#42619 [Com]: Problem with prepared statement

[uwendel at mysql dot com]

 ID:               42619
 Comment by:       uwendel at mysql dot com
 Reported By:      lmanseau at claurendeau dot qc dot ca
 Status:           Open
 Bug Type:         MySQLi related
 Operating System: Linux
 PHP Version:      5.2.4
 New Comment:

Stupid me, ... no bug, of course - wrong API usage. 

Prepared Statements work with unbuffered result sets by default. PS
can't tell you how many rows there are in the result set before you
either have:

 - stored the result set / turned it into a buffered result set
 - fetched all data

You have neither called mysqli_stmt_store_result() nor fetched all
results before you call mysqli_stmt_num_rows(). Therefore,
mysqli_stmt_num_rows() returns 0, which is correct.

http://www.php.net/manual/en/function.mysqli-stmt-num-rows.php

"Returns the number of rows in the result set. The use of
mysqli_stmt_num_rows()  depends on whether or not you used
mysqli_stmt_store_result() to buffer the entire result set in the
statement handle.

If you use mysqli_stmt_store_result(), mysqli_stmt_num_rows() may be
called immediately. "

See also
http://dev.mysql.com/doc/refman/5.1/en/mysql-stmt-num-rows.html


Previous Comments:
------------------------------------------------------------------------

[2007-09-11 15:10:46] lmanseau at claurendeau dot qc dot ca

Here is the SQL definition:

CREATE TABLE test1 (
  c1 int(4) unsigned zerofill NOT NULL auto_increment,
  c2 varchar(10) default NULL,
  c3 double(4,2) default NULL,
  PRIMARY KEY  (c1)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

Code with problem:

// Opening connection
include ('../mysql/param.inc');
$db = new mysqli($hote, $util, $mpas, $base); 
if (!$db) {
        echo "No connection!";
        exit();
}
echo 'Non Prepared Request<br />';  //WORK WELL
$sql = "select * from test1"; 
$rqp = $db->query($sql); 
echo 'Rows = '.$rqp->num_rows.'<br />';

echo '<br />Prepared Request<br />'; //DON'T WORK
$sql = "select * from ch9books"; 
$rqp = $db->prepare($sql);
$rqp->execute();
echo 'Rows = '.$rqp->num_rows.'<br />';

// Closing connection 
$db->close();


And the results...


Non Prepared Request
Rows = 6

Prepared Request
Rows = 0

------------------------------------------------------------------------

[2007-09-11 08:52:29] uwendel at mysql dot com

Please provide a reproducible test case, including SQL definitions and
"the other statements" you mention, if they are needed for a minimum
test case.

Thanks!

------------------------------------------------------------------------

[2007-09-10 20:50:48] lmanseau at claurendeau dot qc dot ca

Description:
------------
I try to execute a prepared statement with mysqli. I use PHP 5.2.0 with
MySQL 5.0.27

After some tests, prepared statements with MySQL instructions  such as
(INSERT, DELETE and UPDATE) work very well.

The only case problem which I pointed out, it is the instruction SELECT
that does not seem to react of good manner

-       SELECT * FROM table1 – don’t work
-       SELECT C1, C2 FROM table1 - don’t work

I have no error message!!!

Reproduce code:
---------------
$db = new mysqli($hote, $util, $mpas, $base);  
if (!$db) { 
   echo "No connection with BD"; 
   exit(); 
} 

//WORK WELL 
echo 'Non prepared statement<br />'; 
$sql = "select * from ch9books";  
$rqp = $db->query($sql);  
echo 'books = '.$rqp->num_rows.'<br />'; 


//DON’T WORK
echo '<br />Prepared statement<br />'; 
$sql = "select * from ch9books";  
$rqp = $db->prepare($sql); 
$rqp->execute(); 
echo 'books = '.$rqp->num_rows.'<br />'; 

$rqp->close();


Expected result:
----------------
Non prepared statement
Books = 6 



Actual result:
--------------
Prepared statement 
Books = 0



------------------------------------------------------------------------


-- 
Edit this bug report at http://bugs.php.net/?id=42619&edit=1