Execute in prepared statement not returning anything

Question

I've verified all variables below and am now attempting to insert them into my mysql database using prepared statements, but the data is not inserting even though I am not receiving any system errors. $insert_stmt->affected_rows>0 returns false, and thus displays my custom warning message:

$insert_stmt = $db->prepare("INSERT INTO users (id,timestamp,externalid,password,authentication,email) VALUES(NULL,NOW(),?,?,?,?)");
$insert_stmt->bind_param("ssss",$userid,$hash,$authenticate,$email);
$insert_stmt->execute();
if ($insert_stmt->affected_rows>0)

I'm somewhat new to PHP (and the community) and can't figure out why nothing would be inserting and my custom warning message always displays? Again, no system errors are generated. Thanks!

I reccomend you to tell PHP to show all warnings and errors. call error_reporting at the begining of your script passing E_ALL | E_STRICT. You can aslo try that SQL statement directly in the database to see if is there any problem. — Theraot, Oct 14 '12 at 05:16
would there be issues in choosing mysqli vs. PDO? I wrote this with respect to mysqli, so the error catching techniques described below for PDO would take me some time to incorporate. I'm not getting the errors I was hoping to resolve. — rocket_boomerang_19, Oct 14 '12 at 05:51

score 3 · Answer 1 · answered Oct 14 '12 at 05:17

3

Chances are there is an error when execute() is called. execute() will return TRUE if the statement succeeded and FALSE on failure. So check the return value. If it's FALSE, call mysqli_stmt_error() for information about the problem.

answered Oct 14 '12 at 05:17

Trott

66,479
23
173
212

score 1 · Accepted Answer · answered Oct 14 '12 at 19:05

I figured out the issue. I was testing with an externalid that was already in the database, and my check for preventing duplicate external ids was not working correctly, so my code was reaching the insertion line and trying to put the externalid again into MySQL when it already existed in the database. I had set the externalid as a unique field, so it wouldn't allow it to be entered. MySQL though didn't return any noticeable errors when I had set E_ALL | E_STRICT. Good to know in the future.

I didn't realize I was testing with a duplicate entry, so sorry to the community for adding that nugget of info.

score 0 · Answer 3 · edited May 23 '17 at 11:56

0

Please do the following:

1) Look at this link:

PHP PDO code to insert in MySQL db fails

2) Add this after you connect to the database:

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

3) Add this try/catch block around your prepare, bind and execute statements:

try { .. } and catch (Exception $e) { echo $e->getMessage(); }

4) Report back what - if any - error messages you get

And be sure to check the error status of your calls. Particularly $insert_stmt->execute().

edited May 23 '17 at 11:56

Community

1
1

answered Oct 14 '12 at 05:17

paulsm4

114,292
17
138
190

Thanks, I'll review all these steps and report back. For #4, the error statements when errors were turned all the way on are just notices for some undefined indexes with issues in other places of my code. – rocket_boomerang_19 Oct 14 '12 at 05:25

Execute in prepared statement not returning anything

3 Answers3