CODEWITHSUNDEEP
phpmysqlmysqli
Jordan Ramstad
Jordan Ramstad

Reputation: 179

PHP MySQLi possible issue with escaped parameter in escaped statement?

This is something I have been trying to figure out for a bit, it is the most simplest of queries that does not seem to want to work for me (only in php mysqli, works in console sql)

First I am using a prepared statement, merely looking for a match on a specialized id (from another service) to update the relation to use my primary key for easier searching on my end.

The query is as follows:

$query = "SELECT id 
          FROM {$this->config->dbprefix}{$table} 
          WHERE sf_id = ? 
          LIMIT 1";

I use this as one line, I split it up for better readability here

I then check that the prepare statement is valid (I do this in multiple places and it works everywhere else.

if(!($ret = $this->dbo->prepare($query))){
  //handle error, this part is never called
}else{
  //everything is fine, code in here is below
}

Up to here everything seems fine. Checking table and prefix manually shows they are working and referencing the proper table.

$ret->bind_param('s',$id);
$ret->execute();
$ret->bind_result($retId);
$ret->fetch();
$count = $ret->num_rows;

The problem is here, the query always returns 0 for the num_rows. Checking the query manually and trying it in console returns 1 result as it should. So far with it being such a simple query I just cannot wrap my head around why it would work elsewhere, but not here. I am sure this is the proper way to build it (I have many other queries structured similar).

Is there any kind of confusion I may be experiencing? Something easy to miss that could cause a query like this to not return results?

EDIT: I have attempted further error handling, and trying an if test on execute does not trigger an error, though I will try more.

To expand I have a raw output of the $query variable and the id variable. By combining them and manually attempting the query in console I get the proper result. My thoughts are on somehow the prepare statement is escaping, causing the string variable $id to not properly match. Though that is just speculation.

Upvotes: 0

Views: 60

Answers (1)

Mike Brant
Mike Brant

Reputation: 71384

You need to call store_result() before trying to access num_rows. Without it, the statement handle does not know how many rows are in the result set.

$ret->bind_param('s',$id);
$ret->execute();
$ret->bind_result($retId);
$ret->store_result();
$count = $ret->num_rows;
// perhaps add error handling based on number of rows here
$ret->fetch();

Upvotes: 1

Related Questions