PDO Prepared stament while in a loop

Question

This is something simple but I am having a really difficult time with it for some reason. Basically, I do a select statement to find results. I loop through that result and update the items I found in result.

This is my code:

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); $stmt = $pdo->prepare("select * from licenses where email='empty' limit $quantity"); $stmt->execute(); $row = $stmt->fetchAll(PDO::FETCH_ASSOC); if ($stmt->rowCount() > 0) { $stmt = $pdo->prepare("update licenses set email=:em, exp_date=:exp where 'id'=:id"); foreach ($row as $row) { $expires = date("Y-m-d", strtotime('+365 days')); $license = $row['license_key']; $lid = $row['id']; echo $license . '<br> ID:' . $lid; $stmt->bindValue(':em', $email1, PDO::PARAM_STR); $stmt->bindValue(':exp', $expires, PDO::PARAM_STR); $stmt->bindValue(':id', $lid, PDO::PARAM_INT); $stmt->execute(); }

This code WORKS, it updates my database in a working condition. However, the website returns a 504 error and the output of license does not get displayed. Does anyone know what is wrong with this code or how can I complete the task I am aiming for?

** Edit: When I use fiddler, I get this error: [Fiddler] ReadResponse() failed: The server did not return a response for this request.

From php manual Note: Some drivers require to close cursor before executing next statement. php.net/manual/en/pdostatement.execute.php . What driver do you use? — Ivan Hušnjak
– Ivan Hušnjak, Commented Aug 24, 2013 at 13:13
I have even tried closing cursor before doing a second prepare. No luck either. — JonJon2244
– JonJon2244, Commented Aug 24, 2013 at 19:29

Ivan Hušnjak · Accepted Answer · 2013-08-24 22:05:16Z

Well, I cant believe we all missed this one. In your update query you use expression 'id'=:id in where clause. This effectively means that if your $id is say 15 -> 'id' != 15 and query will never update any values in table. Remove the quotes around id:

$stmt = $pdo->prepare("update licenses set email=:em, exp_date=:exp where id=:id");

Great observation!!! That was actually one of the reason it was causing an error. I figured that one out already while testing. Another reason the error was happening was because of: array(PDO::ATTR_PERSISTENT => true)); I had to remove that and it was working again.

som · Accepted Answer · 2013-08-24 12:46:34Z

change variable name $row to $rows

$rows = $stmt->fetchAll(PDO::FETCH_ASSOC); // change variable $row to $rows foreach ($rows as $row) { // Your Code }

Collectives™ on Stack Overflow

PDO Prepared stament while in a loop

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related