Clever Geek Handbook

Prepared statement does not work for "SELECT" command using mysqli in PHP

I was able to execute multiple prepared statements using mysqli (in PHP). However, for some reason, prepared statements always encounter an error when I try to run the "SELECT" command. For example, the following line will succeed:

$stmt=$mysqli->prepare("UPDATE events SET category='m' WHERE id=(?)");

However, the following line will fail:

$stmt=$mysqli->prepare("SELECT * FROM events WHERE id=(?)");

When I say fail, I mean that the next three lines will return 1 for the UPDATE command (indicating that one line has been modified) ...

$stmt->bind_param('i',$id);
$stmt->execute();
echo $stmt->affected_rows;

The next three lines will return 0 for SELECT:

$stmt->bind_param('i',$id);
$stmt->execute();
echo $stmt->num_rows;

For the record, I know prepared statements are not that efficient for a single SELECT - this question is mostly academic.

+3


source to share


3 answers


This function (affected_rows) only works with queries that update the table. To get the number of rows from a SELECT query use mysqli_stmt_num_rows ().

http://php.net/manual/en/mysqli-stmt.affected-rows.php



Make sure to save the result first!

$stmt->execute();

/* store result */
$stmt->store_result();

printf("Number of rows: %d.\n", $stmt->num_rows);
+1


source


What will you get if you do this?

echo $stmt->num_rows;


You cannot use the affected rows method for a SELECT statement!

+1


source


mysqli_affected_rows

Returns the number of rows affected by the last INSERT, UPDATE, REPLACE, or DELETE query.

+1


source






More articles:


All Articles