CODEWITHSUNDEEP
phpmysqlmysqli
JMS
JMS

Reputation: 1091

prepared statement failing for "SELECT" command, using mysqli in PHP

I've been able to execute multiple prepared statements using mysqli (in PHP). However, for some reason the prepared statements always encounter an error when I try running a "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 row was changed)...

$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'm aware that prepared statements aren't that efficient for a single SELECT - this question is mostly academic.

Upvotes: 3

Views: 506

Answers (3)

Joe T
Joe T

Reputation: 2350

This function (affected_rows) only works with queries which update a table. In order to get the number of rows from a SELECT query, use mysqli_stmt_num_rows() instead.

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

Make sure you store the result first!!

$stmt->execute();

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

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

Upvotes: 1

fortune
fortune

Reputation: 3372

mysqli_affected_rows

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

Upvotes: 1

Richie
Richie

Reputation: 1439

What do you get if you do this? 

echo $stmt->num_rows;

You cannot use affected rows method for SELECT statement!

Upvotes: 1

Related Questions