Using PHP PDO, unusual behavior with rowCount and catching exceptions

Question

I'm getting some unusual behavior when using PHP/PDO and doing an INSERT. Here is some sample code:

foreach ($all_users as $this_user) {
    try {
        $stmt = $db->prepare('INSERT INTO fav_colors (name, color, whattime) VALUES (:name, :color, :whattime)');
        $stmt->bindValue(':name', $this_user[name]);
        $stmt->bindValue(':color', $this_user[color]);
        $stmt->bindValue(':whattime', $this_user[time]);
        $stmt->execute();
        $count = $stmt->rowCount();
    } catch(PDOException $e) { catchMySQLerror($e->getMessage()); }

    if ($count == 1) {
        echo "Successful write to table";
    } elseif ($count == 0) {
        echo "ERROR writing row to table";
    }

}

Lets say I have 10 names/colors to insert and the times are DATETIME format (2016-01-01 12:00:00). I have a loop around this code and after the code I check $count to see if the insert worked OK.

Here is my problem. One of the 10 inserts, for whatever reason the $write_time was blank. mySQL generated an error (can't be null). My custom function catchMySQLerror writes the error to a table and e-mails me. It did, but $count was still 1 for this entry and according to it, everything was fine even though the record did not insert.

So is my problem with the try/catch I am using here to catch the exception? How can I add something where I can better handle the error within the code? I know I can add more lines after the catchMySQLerror line and work with the error that way. That is what I have done. But I would like to handle the error using an if/else and with the approach I just mentioned, I can only handle the error... not if it was success.

UPDATE: I edited the code to show how I have been using $count

http://php.net/manual/en/pdo.error-handling.php if you're not already doing so. — Funk Forty Niner, Feb 29 '16 at 19:08
if the problem is with `$write_time`, don't you think it would make sense to show how `$write_time` is defined.. anyway, sounds like an off by one error, you should show the code for the loop. — I wrestled a bear once., Feb 29 '16 at 19:12
How do you define `$write_time` in your code? Also note that you only need to bind params once. After that you are free to just change your variables and execute. — Dave Chen, Feb 29 '16 at 19:13
[This answer](http://stackoverflow.com/a/883382/4577762) says that PDO _apparently does not work in MySql_. (but some comments say it did, so....) — FirstOne, Feb 29 '16 at 19:14
think he meant to say you only need to prepare once.. you'll have to bind the new params for evey new insert obvs — I wrestled a bear once., Feb 29 '16 at 19:14
@FirstOne That's specifically for SELECT queries. SELECT indeed does not set rowCount. INSERT and UPDATE however should, as far as I know. (As is stated in the [PDOStatement::rowCount docs](http://php.net/manual/de/pdostatement.rowcount.php) at the top) — ccKep, Feb 29 '16 at 19:20
$write_time is not really important, it's just a part of the example I made up. I'm more interested in how to handle any error generated by the insert. — phpmysqlguy, Feb 29 '16 at 19:22
It might help if you posted a more detailed example (with a loop construct as yours). One guess could be that your `$count` is still `1` from the previous iteration since your `$stmt->execute()` jumps into the exception catch handler. This depends on your exact code though (try-catch inside loop, try-catch outside loop etc.) — ccKep, Feb 29 '16 at 19:24
@ccKep I use rowCount with SELECT all the time in PHP/PDO and it works fine. — phpmysqlguy, Feb 29 '16 at 19:51

score 2 · Accepted Answer · edited Feb 29 '16 at 19:36

2

If ->execute() throws an exception, $count = $stmt->rowCount(); isn't executed and therefore $count keeps the value of the previous iteration (if there was one).
If you rely on that value, you have to (re-)initilize it at the beginning of every iteration.

edited Feb 29 '16 at 19:36

VolkerK

95,432
20
163
226

answered Feb 29 '16 at 19:22

Jorgeley

482
1
6
12

The count should be reset each time by the new rowCount output – phpmysqlguy Feb 29 '16 at 19:23
I upvoted it because Jorgeley ist right. When an exception is thrown by ->execute() $count keeps the value from the previous iteration. – VolkerK Feb 29 '16 at 19:33
@VolkerK I don't care, it's still questions to the OP - *"if this is code is inside a loop, so the $count is still 1 in the second loop when the error happens, is it? have to zero the $count first?"* – Funk Forty Niner Feb 29 '16 at 19:34
@VolkerK Thanks for the edit and is much clearer now. Jorgeley should really think twice when posting answers like that http://stackoverflow.com/revisions/35707827/1, as it attracts flags as low-quality. I removed my downvote for it, but there's still one there, so it's no longer mine. – Funk Forty Niner Feb 29 '16 at 19:37
1

@Jorgeley , yes Fred is absolutely right. Be more confident and give answers that make statements, not giving more questions. In case you're not sure, test your code (see it as some kind of coding-kata ;-) and _then_ make a firm statement. Cheers. – VolkerK Feb 29 '16 at 19:39
sorry guys, i'm leaving the stackoverflow, really sorry for inconvenience. – Jorgeley Feb 29 '16 at 19:40
@Jorgeley nobody told you to leave, Stack is about giving quality answers and not about asking questions in the answers area. Once you've reached 50+ rep, then you can ask questions/post comments. – Funk Forty Niner Feb 29 '16 at 19:41
Why leaving? Is there something wrong about what we ...suggest? Btw: stackoverflow isn't exactly a _forum_. Its mission goal was to be more on the wikipedia side of things than _discussion_ forums. We strayed from that ...a lot ...I'm guilty as can be. But that doesn't mean we should endorse that ;-) – VolkerK Feb 29 '16 at 19:41
I think I am starting to understand. I updated my code. I didn't realize if execute isn't successful, rowCount doesn't return 0. So what does it return? It is just completely ignored? – phpmysqlguy Feb 29 '16 at 19:49
@phpmysqlguy if ->execute isn't successful (in your case), it throws an exception. This exception is caught by your catch-block. rowcount is never even executed, it returns nothing. – ccKep Feb 29 '16 at 21:13

score 0 · Answer 2 · answered Mar 01 '16 at 05:23

this is how it have to be done

foreach ($all_users as $this_user) {
    try {
        $stmt = $db->prepare('INSERT INTO fav_colors (name, color, whattime) VALUES (:name, :color, :whattime)');
        $stmt->bindValue(':name', $this_user[name]);
        $stmt->bindValue(':color', $this_user[color]);
        $stmt->bindValue(':whattime', $this_user[time]);
        $stmt->execute();
    } catch(PDOException $e) {
        catchMySQLerror($e); 
        echo "ERROR writing row to table";
    }
}

Using PHP PDO, unusual behavior with rowCount and catching exceptions

2 Answers2