当等效的普通查询返回3时，Mysqli预处理语句返回0行

Question

I am having some problems with prepared statements in Mysqli and I’m not sure why.

I have a database which currently has 3 rows, which I want to select using a SELECT WHERE query. The query which works in PhpMyAdmin is:

SELECT `totalhits`, `totalmisses`, `date` FROM `performance` WHERE `domain` = 'test' AND `profileid` = 1 ORDER BY `date` DESC

This shows all three rows (all have domain = test and profileid=1.)

If I run this with a normal query in Mysqli and hard-coded variables, I get the same result:

$query = $conn->query(“SELECT `totalhits`, `totalmisses`, `date` FROM `performance` WHERE `domain` = 'test' AND `profileid` = 1 ORDER BY `date` DESC”);
echo $query->num_rows; //outputs 3

If I try and run it as a parameter query (as I will be using user entered data), I get 0 rows returned:

$stmt = $conn->prepare("SELECT `totalhits`, `totalmisses`, `date` FROM `performance` WHERE `domain` = ? AND `profileid` = ? ORDER BY `date` DESC");
$domain = 'test';
$profileid = 1;
$stmt->bind_param('si', $domain,$profileid);
$stmt->execute();
echo $stmt->num_rows; //outputs 0

No Mysqli errors are generated by any of these lines (using print_r on the object at each point to check). I also added a $stmt->store_result() line after the execute line but still had the same result (should I be doing this anyway?).

I know I must be going wrong somewhere but I haven’t read anything in the manual or documentation that might give me a hint as to where. All help would really be appreciated!

If helpful, the SQL database is setup as:

CREATE TABLE `performance` (
  `performanceid` int(11) NOT NULL,
  `domain` varchar(128) NOT NULL,
  `profileid` int(8) NOT NULL,
  `totalhits` int(11) NOT NULL,
  `totalmisses` int(11) NOT NULL,
  `date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `performance` (`performanceid`, `domain`, `profileid`, `totalhits`, `totalmisses`, `date`) VALUES
(1, 'test', 1, 0, 1, '2017-05-15'),
(2, 'test', 1, 1, 1, '2017-05-23'),
(3, 'test', 1, 0, 0, '2017-05-23');

Answer 1

The documentation for mysqli_stmt::num_rows misses some detailed information about using num_rows with prepared statements. The description is rather ambiguous in that it refers only to the need to store the result when using the procedural style, but the object-oriented example makes it clear that you need to call the store_result() method before accessing the num_rows property. This means your code should be something like this:

$stmt = $conn->prepare("SELECT `totalhits`, `totalmisses`, `date` FROM `performance` WHERE `domain` = ? AND `profileid` = ? ORDER BY `date` DESC");
$domain = 'test';
$profileid = 1;
$stmt->bind_param('si', $domain,$profileid);
$stmt->execute();
$stmt->store_result();
echo $stmt->num_rows; //should now output 3