PDO updating value

[Self_Taught_Still_Learning]

$sqld = "SELECT * FROM orders WHERE `id`='$delete' AND `name`='$inf2[name]' AND `email`='$inf2[email]' LIMIT 1";
$csql   = $db->query($sqld);
$ccheck = $csql->fetch(PDO::FETCH_NUM);
$cinf = $csql->fetch(PDO::FETCH_ASSOC);

$quantity = $cinf[quantity];
$code = $cinf[code];

$stmt11 = $db->prepare('UPDATE feeds SET quantity=quantity-:quantity WHERE code=:code');
$stmt11->bindValue(':quantity', $quantity, PDO::PARAM_STR);
$stmt11->bindValue(':code', $code, PDO::PARAM_STR); $stmt11->execute();

$id = $delete; 
$name = $inf2[name]; 

$stmt2 = $db->prepare("DELETE FROM orders WHERE id=:id AND name=:name"); 
$stmt2->bindValue(':id', $id, PDO::PARAM_STR); 
$stmt2->bindValue(':name', $name, PDO::PARAM_STR); 
$stmt2->execute();

OK, i have just been told i should start using PDO instead of mysql to update my tables. This code works to delete the order but doesn't update the feeds section.

 

All this is new to me so far and i think i am getting the hang of it. Should i still be using the quantity=quantity-:quantity or is there another way to do it with PDO?

 

 

Sorry, i posted this in the wrong forum. Hope someone here can help until it's moved

Edited May 30, 2014 by Self_Taught_Still_Learning

[ginerjm]

not bad if it's your first effort.

 

Judging from what we can see here:

 

1 - you should use quotes around array indices such as $cinf[quantity].

2 - you do the bindvalue of quantity as a string - why not an int?

3 - you grab the first row of your query result then you grab a second row but you limited the results to only 1. Wassup with that?

 

You should be checking the results of your query executions before doing anything with the results to ensure they ran. You should also have php error checking turned on, but it may just not be shown here.

[Self_Taught_Still_Learning]

It works perfectly if i repeat the num code again and run the fetch from a seperate file instead of both from the same and also i had to change the way it was adding things to the database. I had to change the code to:

$sql = "UPDATE feeds SET quantity=quantity-:quantity WHERE code=:code";
$q = $db->prepare($sql);
$q->execute(array(':quantity'=>$quantity, ':code'=>$code));

What's the difference between binding and what i am currently using?

[ginerjm]

no difference, therefore I don't think you HAD to change that code.

 

I don't know what you are defending in your code. The fact that you do two fetches in a row to different vars is puzzling. No reason, especially since you only have one record to be fetched.

[kicken]

What seems likely is that the update query runs, but simply has no effect as the values you bind are NULL.

 

As mentioned you limited your SELECT query to a single row (LIMIT 1) but then you try and fetch two rows ($ccheck and $cinf). Since there is at most one row, the second fetch is going to fail and result in $cinf being set to false.

 

Since $cinf is set to false, that then means that trying to access $cinf

 and $cinf[quantity] is going to result in a NULL value since $cinf is not an array and that key doesn't exist. Since your $code and $quantity values are going to be NULL, your UPDATE query will effectively do nothing.

 

What you need to do is only fetch one row from your SELECT query results, and make sure you fetch it into the correct variable. You should also being using prepare/bind for the SELECT query as well since it user parameters. You should be using prepare/bind for any query with parameters, not just an UPDATE or DELETE.

 

$sqld = "SELECT * FROM orders WHERE `id`='$delete' AND `name`=:name' AND `email`=:email LIMIT 1";
$csql   = $db->prepare($sqld);
$csql->bindValue(':name', $inf2['name']);
$csql->bindValue(':email', $inf2['email']);
$csql->execute();

$cinf = $csql->fetch(PDO::FETCH_ASSOC);
$csql->closeCursor(); //Generally optional, but I like to do it.

$quantity = $cinf['quantity']; //Quote array keys
$code = $cinf['code']; // "

If you don't have PDO running in exception mode for error reporting, then you should also be checking the result of execute() to make sure the query was successful and if not report the error.