#42619 [Opn->Bgs]: Problem with prepared statement

ID: 42619 Updated by: uw@php.net Reported By: lmanseau at claurendeau dot qc dot ca -Status: Open +Status: Bogus Bug Type: MySQLi related Operating System: Linux PHP Version: 5.2.4 New Comment: Wrong usage of API, ext/mysqli acts as described in the manual Previous Comments: ------------------------------------------------------------------------ [2007-09-12 08:39:42] uwendel at mysql dot com 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 ------------------------------------------------------------------------ [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