Background:
I have a database table regarding various products. This database table is called product. The columns are set as id | group_id | model | description | price | .
id = the product id.
group_id = identifies the group that this product belongs too.
model = product model
description = Description
price = Price
From this table I generate a dynamic table based on the group_id of table product.
<table>
<tr>
<th>Model</th>
<th>Description</th>
<th>Price</th>
<th>QTY</th>
</tr>
<?php
$conn = mysqli_connect("localhost", "root", "root", "test");
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql ="SELECT * FROM product WHERE group_id = 2";
$result = $conn->query($sql);
while($row = $result->fetch_assoc()) :
?>
<tr>
<td><?php echo $row['model']; ?></td>
<td><?php echo $row['description']; ?></td>
<td>R <?php echo $row['price']; ?></td>
<td><input type="number" name = "<?php echo $row['id']?>" value = 0></td>
</tr>
<?php endwhile; ?>
</table>
<div class="input-group">
<button type="submit" class="btn" name="save">Save and Return</button>
</div>
</form>
This works well. Once this table is generated it allows the user to edit the quantity of each product that would fall under that group id, upon submit using POST, the information is sent to config_824.php
Please see code below for this file.
<?php
session_start();
include ('customer_details_config.php');
$quote_id = $_SESSION['quote_id'];
$salesman_id = $_SESSION['salesman_id'];
$db = mysqli_connect('localhost', 'root', 'root', 'test');
if (isset($_POST['save_pan_han'])){
unset($_POST['save_pan_han']);
foreach($_POST as $key => $value):
$test_query = "SELECT * FROM quote_items WHERE quote_id = $quote_id AND prod_id = $key AND rep_id = $salesman_id";
$result = mysqli_query($db, $test_query);
$item = mysqli_fetch_assoc($result) or die (mysqli_error($db));
if($item['quote_id'] == $quote_id &&
$item['prod_id'] == $key &&
$item['rep_id'] == $salesman_id){
$update_query = "UPDATE quote_items SET
qty = $value
WHERE
quote_id = $quote_id
AND prod_id = $key
AND rep_id = $salesman_id
";
mysqli_query($db,$update_query) or die (mysqli_error($db));
}else{
$query = "INSERT INTO quote_items (quote_id, prod_id, rep_id, qty) VALUES ($quote_id, $key, $salesman_id, $value)" ;
mysqli_query($db,$query) or die (mysqli_error($db));
}
endforeach;
}
?>
$quote_id is set with $_SESSION['quote_id'] and $salesman_id is set with $_SESSION['salesman_id'] when the user logs in.
quote items table has columns called id, quote_id, prod_id, rep_id, qty
CREATE TABLE `project`.`quote_items` ( `id` INT(10) NOT NULL AUTO_INCREMENT , `quote_id` INT(10) NOT NULL , `prod_id` INT(10) NOT NULL , `rep_id` INT(10) NOT NULL , `qty` INT(10) NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;
As per the code above, I am trying to insert the data into a database table called quote_items, if I insert test data into the database, the UPDATE query runs fine.
The problem:
The issue is that if it checks and the row is not in the database, the INSERT query does not work. Nor do I see any errors despite error showing = TRUE.
I will use the following example:
I have six rows of test data in the database. The dynamic table allows for 8 values to be set and sent to the database. So say the first two values are already in the database and would comply with UPDATE query. Value 3 and 4 has not yet been inserted and would fall under INSERT query. Value 5, 6, 7 and 8 again fall under the Update query.
If I run the code. Only row 1 and 2 would be updated. Row 3 and 4 would not be inserted and row 5, 6, 7, 8 would not be updated.
What I have tried:
Stackoverflow using mysqli_autocommit($db,FALSE);
Copy the query and insert into database via SQL in phpMyAdmin, if I remove the variables from the query it works fine on INSERT.
As you can see in the code above, I have an unset($var) as I noticed when I echo the array that the array included the table name. This is a string. If I try inserting a string this would cause issues obviously and I thougt this would resolve the issue.
This was sadly not the case.
Testing by removing the if and elseif and only using insert as recommended in a comment below:
<?php
include ('customer_details_config.php');
$quote_id = $_SESSION['quote_id'];
$salesman_id = $_SESSION['salesman_id'];
$db = mysqli_connect('localhost', 'root', 'root', 'test');
if (isset($_POST['save_pan_han'])):
foreach($_POST as $key => $value):
$test_query = "INSERT INTO quote_items (quote_id, prod_id, rep_id, qty) VALUES ($quote_id, $key, $salesman_id, $value)";
$result = mysqli_query($db, $test_query) or die (mysqli_error($db));
/*
$item = mysqli_fetch_assoc($result) ;
if ($item['quote_id'] == $quote_id &&
$item['prod_id'] == $key &&
$item['rep_id'] == $salesman_id
){
$update_query = "UPDATE quote_items SET
qty = $value
WHERE
quote_id = $quote_id
AND prod_id = $key
AND rep_id = $salesman_id
";
mysqli_query($db,$update_query) or die (mysqli_error($db));
}else{
$query = "INSERT INTO quote_items (quote_id, prod_id, rep_id, qty) VALUES ($quote_id, $key, $salesman_id, $value)" ;
mysqli_query($db,$query) or die (mysqli_error($db));
}
*/
endforeach;
endif;
?>
