Page 1 of 1

PHP MySQL Stored Procedure Getting Number of Rows Returned

Posted: Wed Feb 01, 2012 3:36 am
by jonatec
Hi

Please take a look at my code snippet. Works fine, however I don't know how to get the number of rows returned from a MySQL stored procedure so that I can handle it accordingly. Any ideas please?

Thanks.

Code: Select all

<?php
require ('mysqli_connect.php'); // Connect to the Db.

$sql = "CALL customers_for_rep(?, ?)";

$stmt = $dbc->prepare($sql);

if ($dbc->errno) {die($dbc->errno.":: ".$dbc->error);}

$stmt->bind_param("is", $i_user_id, $i_firstName);

$i_user_id = 2;
$i_firstName = "David";

$stmt->execute( );

$rows = $stmt->num_rows;

if ($dbc->errno) {die($dbc->errno.": ".$dbc->error);}

$stmt->bind_result($first_name, $last_name);

echo "Rows returned: " . $rows . "</p>";

$buf = "";
$buf .= '<table align="center" cellspacing="3" cellpadding="3" width="70%" border="1">';
$buf .= '  <tr>';
$buf .= '    <td class="td01">First Name</td>';
$buf .= '    <td class="td01">Last Name</td>';
$buf .= '  </tr>';
while ($stmt->fetch( )) {
	$buf .= '<tr>';
	$buf .= '  <td>' . $first_name . '</td>';
	$buf .= '  <td>' . $last_name . '</td>';
	$buf .= '</tr>';	
}
$buf .= '</table>';
echo $buf;
echo "<p/>made it.";
mysqli_close($dbc);
?>
=========================================================================
DELIMITER $$
DROP PROCEDURE IF EXISTS customers_for_rep$$
CREATE PROCEDURE customers_for_rep
(
    IN i_user_id INT,
    IN i_firstName VARCHAR(100)
)
BEGIN
    SELECT first_name, last_name
    FROM users
    WHERE users_id > i_user_id;
END $$
DELIMITER ;

Re: PHP MySQL Stored Procedure Getting Number of Rows Return

Posted: Wed Feb 01, 2012 9:23 am
by mikosiko
you must use mysqli::store_result (Ref.: http://php.net/manual/en/mysqli.store-result.php) immediately after execute your statement as in:

Code: Select all

$stmt->execute( );

// Store Result
$stmt->store_result()

$rows = $stmt->num_rows;
however, be sure to close your $stmt BEFORE try to execute any other SQL sentence (using $stmt->close()), otherwise you can get 2 possible errors:
"Command Out of Sync" or "MYSQL Server has gone away"

the reason behind that, in case that you don't know it, is that an SP has the potential to return MORE THAN ONE Record set and you MUST exhaust them all before to execute other SQL sentence; In you current code per example, if the SP execute correctly it is going to return 2 Record Set, been one just an SP's status indicator (to control if the SP's execution was successful or not), and the other the record set produced by your SELECT in that SP.

Now, just for curiosity... if that is the real code for your SP, why are you using an SP in first place?... you are over complicating the solution