Hello guys, my name is Gabriel and I need some help with a function.
So this function I used to show all products in my shopping cart, and I I want all products to be inserted in the order_products table when submit button is pressed. Everything works fine in the first part but If I press the submit, 2 rows inserted with the same information. Maybe because it’s in the select query. How can I insert these data into the database without insert every product twice?
Woh bad code! Especially using hidden fields for pricing data. All of that should be stored in the table that you query. You are not using prepared statements, which is bad.
The next issue is a straight insert rather than an upsert. An upsert works like an insert statement, except if the row exists, it updates it rather than inserting a new row.
<?php
function cart() {
global $connection;
$total=0;
$item_name = 1;
$item_number = 1;
$amount = 1;
$q = 1;
foreach ($_SESSION as $name => $value) {
if($value > 0) {
if(substr($name, 0, 8) == "product_") {
$length = strlen($name - 8);
$id = substr($name, 8, $length);
$id_safe = escape_string($id);
$query = "SELECT * FROM products WHERE product_id = '$id_safe'";
$select_query = mysqli_query($connection,$query);
confirm($select_query);
while ($row=mysqli_fetch_assoc($select_query)) {
$sub = $row['product_price'] * $value;
$title = $row['product_title'];
$price = $row['product_price'];
$quantity += $value;
$product = <<<DELIMITER
<tr>
<td>{$row['product_title']}</td>
<td>{$row['product_price']}€</td>
<td>{$value}</td>
<td>{$sub}€</td>
<td>
<a class="btn btn-warning" href="cart.php?remove={$row['product_id']}">
<span class='glyphicon glyphicon-minus'></span></a>
<a class="btn btn-success" href="cart.php?add={$row['product_id']}">
<span class='glyphicon glyphicon-plus'></span></a>
<a class="btn btn-danger" href="cart.php?delete={$row['product_id']}">
<span class='glyphicon glyphicon-remove'</span></a>
</td>
</tr>
<input type="hidden" name="item_name_{$item_name}" value="{$row['product_title']}">
<input type="hidden" name="item_number_{$item_number}" value="{$row['product_id']}">
<input type="hidden" name="amount_{$amount}" value="{$row['product_price']}">
<input type="hidden" name="quantity_{$q}" value="{$value}">
DELIMITER;
if (isset($_POST['submit-product'])) {
global $inv_number;
$queryInsertProducts = "INSERT INTO
order_products
(
order_delivery_id
,product_title
,product_price
,product_quantity
)
VALUES
('{$inv_number}'
,'{$title}'
,'{$price}'
,'{$value}')";
$queryConfirmInsert = mysqli_query($connection,$queryInsertProducts);
confirm($queryConfirmInsert);
echo "Row Inserted!";
}
echo $product;
$item_name++;
$item_number++;
$amount++;
$q++;
}
}
$_SESSION['item_total'] = $total += $sub;
$_SESSION['item_quantity'] = $quantity;
}
}
}
1 Like
thank you for your advice. Should I use PDO with prepared statements? I saw some examples and I think I can implement this.
If this is code you are writing, rather than existing code you must deal with, you need to Keep It Simple (KISS.)
Starting with the session variable. You should use an array variable, such as $_SESSION[‘cart’] so that the session can hold other information. You would operate on elements in the $_SESSION[‘cart’] array. The current code would loop over other things in the session, such as a logged in user id, language choice, …
The only thing that should be stored in the session cart is the product id (as an array index) and the quantity (as the array value.) The product id should be the auto-increment id column form the products table. You should not have any values that end in _number that you must strip off to use, i.e. don’t make up things that require extra processing each time you use it. The submitted product id should be cast as an integer when an item is added to the cart.
To display the cart, don’t run queries inside of loops. Get a list of the product ids (you can use array_keys() on $_SESSION[‘cart’]) then implode those ids to make a comma separated list to use in an IN() comparison in a single sql query. You would loop over the result set from the query to display the contents of the cart, getting the quantity for each item using $_SESSION[‘cart’][$row[‘product_id’]]. When you display the cart, you can provide form(s) field(s) to modify the contents of the cart (you shouldn’t use links to modify data), but they will modify the contents of the session cart.
When the visitor is happy with the cart and moves onto the next step of finalizing the order, the session cart contains all information, so there’s no need for any of the hidden form fields at this point. You should also always pass a minimum of information through the form since you must validate all the submitted data before using it. When you insert the cart data into the database table holding the order details, you would use code similar to that displaying the cart. You should actually be inserting a new row into an ‘orders’ table. This will establish an order_id value. You would get the last inserted id from this query and use it in the insert query for the order details. The order details table would have columns for the order_id, product_id, and quantity. You can include the price at the time the order was placed, but you should actually handle pricing by having a product pricing table with from/to dates for any price, then use an appropriate JOIN query to retrieve the prices at the date of the order.
Any form processing code needs to be above the form (should be above the start of the html document) so that you can display any form validation errors when your re-display the form.
As already mentioned, you should use prepared queries when supplying external/unknown values to an sql query statement, and the php PDO extension is much simpler and more consistent then the php mysqli extension. Using a prepared query for the data being inserted into the order details table will also provide a slight performance increase, since you will prepare the query once, but execute it for each row that gets inserted.
1 Like
Thank you for your message. I changed my code and used PDO with prepared statements.
Now I try to fix cart shop.
Just one more question about PDO:
Is this safe? I’m not sure about GET method in cases like this.
if(isset($_GET['id'])) {
$id = $_GET['id'];
$query = "SELECT * FROM products WHERE product_id=:id";
$stmt = $connection->prepare($query);
$stmt->bindParam(':id',$id,PDO::PARAM_INT);
$stmt->execute();The query is fine, though the $id variable is not needed and just a waste of memory.
1 Like
Ok. I solved all. I made a new function for add the products from sessions. Now it’s working.
I cleaned up some useless things in my code after I implemented the PDO prepared statements.
Thank u so much!