Jump to content

PDO updating value

Recommended Posts

$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); 

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 by Self_Taught_Still_Learning
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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']);

$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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.