PHP - bindValue in a loop calling a stored procedure to insert, only works on first call

Question

I have a stored procedure in MySql to insert some rows. The stored procedure works fine when calling from the command line and with different values.

Now I have a php script that scrapes a page and then tries to insert items into the db using the script. However only the first item in the list gets inserted. code snippet:

$dbStatement = $dbHandler->prepare("CALL insertItem(:in_itemID,:in_itemName)");

foreach($matches as $match) { 
    echo '<div>'.$match[3].' '.$match[5].'</div>';
    $dbStatement->bindValue(':in_itemID', $match[3]);
    $dbStatement->bindValue(':in_itemName', $match[5]);
    $dbStatement->execute();
}

So this will echo all a long list of items/ids to insert, but only the first one ever gets inserted (in the stored procedure there is logic to not insert duplicates)

My question is why is it only getting called with the first set of values? I saw this question A problem while bind params for PDO statement inside loop and tried the suggested solution but it didn't do anything different - still only the values from the first loop getting inserted.

foreach($matches as $match => $value) { 
    echo '<div>'.$value[3].' '.$value[5].'</div>';
    $dbStatement->bindValue(':in_itemID', $value[3]);
    $dbStatement->bindValue(':in_itemName', value[5]);
    $dbStatement->execute();
}

I am new to PHP and MySql (c# and mssql background)

Thanks!

ADDITIONAL: To output errors I added (thank you Charles)

$dbHandler->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );

And my error output is

Warning: PDOStatement::execute(): SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active.  Consider using PDOStatement::fetchAll().  Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.

So I did some searching and added/changed (thanks Phil)

if (!$dbStatement->execute()) {
   $err = $dbHandler->errorInfo();
   throw new Exception($err[2]);
}
$result = $dbStatement->fetchAll();

But still getting the same error, even though that looks to be the right syntax for fetchAll();

SOLUTION:

change

$result = $dbStatement->fetchAll();

to

$result = $dbStatement->closeCursor();

Thank you Phil!!

Have you made sure that [PDO isn't silently discarding errors](http://us2.php.net/manual/en/pdo.error-handling.php)? — Charles, Apr 08 '11 at 01:28

Phil · Accepted Answer · 2011-04-08T03:06:29.027

1

As Charles mentioned, make sure errors are reported. You can also check the return value from PDOStatement::execute().

Another consideration and this is purely subjective, use bindParam() instead of bindValue()

$dbHandler->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbStatement = $dbHandler->prepare("CALL insertItem(:in_itemID,:in_itemName)");
$dbStatement->bindParam('in_itemID', $itemId);
$dbStatement->bindParam('in_itemName', $itemName);

foreach($matches as $match) {
    $itemId = $match[3];
    $itemName = $match[5];
    if (!$dbStatement->execute()) {
        $err = $dbHandler->errorInfo();
        throw new Exception($err[2]);
    }
}

Update

That error is saying it can't execute because you have an open cursor on a previous statement. You either need to complete fetching, close the cursor using PDOStatement::closeCursor() or enable query buffering.

edited Apr 08 '11 at 03:06

answered Apr 08 '11 at 01:40

Phil

157,677
23
242
245

Thanks - I tried both bindParam() and bindValue() with the same results. – Karen Apr 08 '11 at 02:56
Thanks! I changed the fetchAll() to closeCursor() and that worked. You would think they would put that in the error message instead of suggesting fetchAll(). – Karen Apr 08 '11 at 03:44
@Karen The best assumption to make about an open cursor is that you haven't finished fetching. To suggest closing it would not fit most situations. – Phil Apr 08 '11 at 03:49

PHP - bindValue in a loop calling a stored procedure to insert, only works on first call

1 Answers1

Update