how do i implement update i pdo for the following mysql code?

Question

here is my mysql code and equivalent pdo code i need to know what is wrong

$id = $_POST['id'];


    $query1=mysql_query("SELECT Quantity,id FROM `yumyum`.`food` where  `food`.`id` LIKE $id");
    $r = array();

    while($r = mysql_fetch_assoc($query1)) {

        $output = $r['Quantity'];


            echo $output;
            $query2=mysql_query("UPDATE food SET Quantity = Quantity - 1 where  `food`.`id` LIKE ".$r["id"]);

PDO code

$stmt = $db->prepare("SELECT * FROM yuymuym WHERE id=:id AND Quantity=:Quantity");
$stmt->execute(array($id, $Quantity));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC)

Describe your problem. No one will examine your code for free) — u_mulder, Nov 24 '13 at 18:00
how do i implement update i pdo for the following mysql code? — user3027853, Nov 24 '13 at 18:01
[How to squeeze error message out of PDO?](http://stackoverflow.com/q/3726505) — Pekka, Nov 24 '13 at 18:02

Jompper · Accepted Answer · 2013-11-24T19:02:14.460

How about this. I don't know what $_POST['id'] is so you have to figure the rest youself. It updates every item with id in $ids array. So this updates items with id 1,2,3,4 and 5.

$db = new PDO('mysql:host=localhost;dbname=yumyum', 'username_here', 'password_here');
$ids = array(1,2,3,4,5);
foreach($ids as $id){
    $stmt = $db->prepare("SELECT Quantity, id FROM `food` WHERE `food`.`id` = :id");
    $stmt->bindParam(':id', $id);
    $stmt->execute();
    $row = $stmt->fetch();
    if($row){
        //uncomment to see $row content
        //var_dump($row);
        $rowId = (int)$row['id'];
        $rowQuantity = (int)$row['Quantity'];
        echo $rowQuantity;
        $ustmt = $db->prepare("UPDATE `food` SET `Quantity` = `Quantity` - 1 WHERE `food`.`id` = :id");
        $ustmt->bindParam(':id',$rowId);
        $ustmt->execute();
    }else{
        var_dump($stmt->errorInfo());
    }
}

But PDO basics:

Query (Works with select, insert, update, everything else):

$id = (int)$_POST['id'];
$else = $_POST['string'];

// Connect to database
$db = new PDO('mysql:host=HOST_HERE;dbname=DATABASENAME_HERE', 'USERNAME_HERE', 'PASSWORD_HERE');

// First we prepare our query
$stmt = $db->prepare("... WHERE `id` = :id AND `something` = :else");

// We bind values to our prepared query
$stmt->bindParam(':id',$id);
$stmt->bindParam(':else',$else);

// We execute our query
$success = $stmt->execute();

// If we want to fetch only one row:
$row = $stmt->fetch();
echo $row['id'];

// If we want to fetch all rows:
$rows = $stmt->fetchAll();
foreach($rows as $row){
    echo $row['id'];
}

These are very basics, if you don't understand what is really happening here, you should learn some more.

im getting this error Parse error: syntax error, unexpected '' (T_ENCAPSED_AND_WHITESPACE), expecting identifier (T_STRING) or variable (T_VARIABLE) or number (T_NUM_STRING) in C:\wamp\www\yumyum\reserve.php on line 38 — user3027853, Nov 24 '13 at 18:15
And if you still don't get it working, paste the error line and line before it here. — Jompper, Nov 24 '13 at 18:16
The code itself doesn't make much sense. Are you updating only one product at a time. Because now it should be looping all rows this "SELECT Quantity, id FROM `yumyum`.`food` WHERE `food`.`id` = :id" query returns. And because of id = I believe, this return only one line. Maybe you could explain more of what you want to achieve. — Jompper, Nov 24 '13 at 18:29
@JoniSalmi i need to update multiple rows. The quantites of multiple rows — user3027853, Nov 24 '13 at 18:31

how do i implement update i pdo for the following mysql code?

1 Answers1