PHP MySQL Stored Procedure Getting Number of Rows Returned

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jonatec
Forum Newbie
Posts: 2
Joined: Wed Feb 01, 2012 3:32 am

PHP MySQL Stored Procedure Getting Number of Rows Returned

Post 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 ;
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: PHP MySQL Stored Procedure Getting Number of Rows Return

Post 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
Post Reply