Update cart - mysql table update - while loop

Question

new guy here! I am building a custom shopping cart driven by mysql and i am trying to update my cart item by item in terms of quantity. It seems that i am doing something wrong because when i try to update the quantity it only update the last item. I am posting the code below. Any help would be appreciated. Thanks in advance.

1.cart.php:

    $sql = "select * from orders";
    $result = mysql_query($sql);
    $num = mysql_num_rows($result);
    echo "Στοιχεία: ".$num;
    ?>
    <form name="cart" method="post" action="updatecart.php">
      <table border="2">
        <tr>
          <td>Α/Α</td>
          <td>img_name</td>
          <td>Reprints</td>
          <td>Color</td>
        </tr>
        <?php
        if($num){
            while ($row = mysql_fetch_array($result)){
             ?>
                <tr>
                  <td><?php  echo $row['item_id']; ?></td>
                  <td><?php echo $row['img_name']; ?></td>
                  <td><input type="number" name="quantity" value="<?php echo $row['quantity']; ?>"></td>
                  <input type="hidden" name="item_id" value="<? echo $row['item_id']; ?>">
                  <td><?php echo $row['color']; ?></td>
                </tr>
            <?php
            }
        }

            ?>
      </table>
      <input type="submit" name="update" value="Update Cart" />
      <input type="button" name="2checkout" value="Proceed to Checkout" />
</form>

2.updatecart.php

<meta http-equiv="content-type" content="text/html; charset=UTF-8" />
<?php
    $database_name = "vog";
    $conn = mysql_connect("localhost","root","toor");
    mysql_select_db($database_name);

    $num = 2; //na to ferw me session meta edw!
    if(isset($_POST['update'])){
        $item_id = $_POST['item_id'];
        $i=1;

        while($i<=$num){
            $item_id = $_POST['item_id'][$i];
            $quantity = $_POST['quantity'];
            $sql2 = "update orders SET quantity = '$quantity' where item_id = '$item_id' ";
            $result2 = mysql_query($sql2) or die ("Error in query: $result2");
            $i++;
        }
    }

    if(isset($result2)){
        header("Location:cart.php");
    }
?>

So far it updates just the last record.

score 2 · Answer 1 · answered Dec 01 '11 at 09:14

2

Your problem is with the names of the fields in your HTML form:

<input type="number" name="quantity" value="<?php echo $row['quantity']; ?>">
<input type="hidden" name="item_id" value="<? echo $row['item_id']; ?>">

I think you meant to call them quantity[] and item_id[] instead, so they will and up as arrays in your $_POST variable later on, now they overwrite eachother, making $_POST['item_id'] only contain the last id in the database.

answered Dec 01 '11 at 09:14

Marijn van Vliet

5,239
2
33
45

And the union of PHP programmers everywhere called me to add a comment: before you write another line of PHP code, first read up about SQL injection. This code leaves your database wide open to hackers. Check http://php.net/manual/en/security.database.sql-injection.php – Marijn van Vliet Dec 01 '11 at 09:17
What do you suggest, in terms of anti-sql-injection measurements? – vsapountzis Dec 01 '11 at 10:20
1

@EvanSap : use mysql_real_escape_string function .. also refer http://stackoverflow.com/questions/60174/best-way-to-stop-sql-injection-in-php – Mithun Satheesh Dec 01 '11 at 10:33
@EvanSap Whenever you embed a string from one language (for instance PHP) into another language (for instance SQL or HTML), you need to convert the string into the proper language or you will experience problems. To go from a PHP string to SQL, you use `mysql_real_escape_string`. To go from a PHP string to HTML, you use `htmlspecialchars`. Not only does this prevent hackers from abusing your system, it also solves problems when someone wants to write `Mac Donald's` to the database, or `I <3 burgers` into the HTML. – Marijn van Vliet Dec 01 '11 at 11:14

score 1 · Accepted Answer · answered Dec 01 '11 at 09:15

in #1.cart.php use the inputs as array:

<input type="number" name="quantity[<?php  echo $row['item_id']; ?>]" value="<?php echo $row['quantity']; ?>">
<input type="hidden" name="item_id[<?php  echo $row['item_id']; ?>]" value="<? echo $row['item_id']; ?>">

and in #2.updatecart.php: process it like

 foreach($_POST['item_id'] as $key => $id){

 $item_id = $id;
 $quantity = $_POST['quantity'][$key];
 $sql2 = "update orders SET quantity = '$quantity' where item_id = '$item_id' ";
 $result2 = mysql_query($sql2) or die ("Error in query: $result2");
 $i++;

 }

Thank you very much for your help.It works as it is supposed to! Now i would propably need to add protection measurements against sql injections. — vsapountzis, Dec 01 '11 at 10:10

score 0 · Answer 3 · answered Dec 01 '11 at 09:21

You need to tell PHP that you're using an array for your submitted form items. The way to do this is to make the name of each input quantity[]. You can also place the item ID directly in the array as a key. In cart.php you can do this in your loop:

<input type="number" name="quantity[<?php echo $row['item_id']; ?>]" 
                     value="<?php echo $row['quantity']; ?>"/>

In effect, this will putput something like:

<input type="number" name="quantity[2]" value="1" />
<input type="number" name="quantity[4]" value="1" />
<input type="number" name="quantity[8]" value="2" />

i.e. 1 of item 2, 1 of item 4 and 2 of item 8.

Then, in updatecart.php you can read in the array and process it in a loop.

if(isset($_POST['update'])){ 
   foreach ($_POST['quantity'] as $item_id => $item_qty) {
       $item_id  = (int)$item_id;
       $item_qty = (int)$item_qty;

       $sql2 = "update orders SET quantity = '$item_qty' where item_id = '$item_id' ";
       mysql_query($sql2);
   }
}

You can thank people by voting up their answers, and accepting one. =) — Daren Chandisingh, Dec 01 '11 at 11:25

Update cart - mysql table update - while loop

3 Answers3

Linked