Page 1 of 1

From PHP: retrieve the value saved in MYSQL SPs OUT param

Posted: Wed Jan 23, 2008 9:50 am
by dpati
Hi All,

I have created a simple stored proc. in MySQL that returns the number of rows fetched by a query.

CREATE PROCEDURE ROW_COUNT(tablenames VARCHAR(1000), querycmd VARCHAR(8000), OUT cnt INT)

The body contains a "SELECT count(*) INTO ........" kind of statement that saves the row count into the OUT variable cnt.
From Mysql command line, I can access this value using the stmt > select @cnt. Everything works fine.

From my php code, I call the proc as follows:

$call = mysqliObj->query("CALL ROW_COUNT(value for param1, value for param2, @out)");

After this, how do I retrieve the value contained in the @out.
I can use other mysqli routines like fetch_associative followed by some processing to get it. But is
there any simpler way. Also, I don't wan't to use stored function.

Please help. I have searched the web and forums quite a lot and haven't found the answer.

Thanks,
Deb

Re: From PHP: retrieve the value saved in MYSQL SPs OUT param

Posted: Fri Jan 25, 2008 3:29 pm
by jackcobain
hi,

you want to count the number of rows fetched by the query right....!

why are you using stored procedure for that huh..

suppose your query is something like:
<?
$sql = "select * from tablename";
$res = mysql_query($sql);
?>
now if you want to retrieve the total number of records fetched by the query use the below syntex:
<?
$count = mysql_num_rows($res);
?>

it simple... :)

Re: From PHP: retrieve the value saved in MYSQL SPs OUT param

Posted: Fri Jan 25, 2008 4:14 pm
by dpati
Hi jackcobain,

Thanks for answering my problem.
However, my question is not just for getting row count.
That was a simple example that I gave so that it will be
easier to figure out my problem.

In general, if I wan't to read the value of the OUT param
returned by a Stored Proc. from php code, how should I do
it? There could be more than one OUT param.

Thanks,
dpati

Re: From PHP: retrieve the value saved in MYSQL SPs OUT param

Posted: Fri Jan 25, 2008 5:25 pm
by Weirdan
You need to bind out parameter to php variable:

Code: Select all

 
$stmt = $mysqli->prepare("call yourproc(?,?,?)");
$tables = "sdf,asd";
$query = "select something from something";
$stmt->bind_param('ssi', $tables, $query, $count);
$stmt->execute();
echo $count;
 

Re: From PHP: retrieve the value saved in MYSQL SPs OUT param

Posted: Mon Jan 28, 2008 10:02 am
by dpati
Hi Weirdan,

Your reply is exactly what I was looking for. After making
myself a little more knowledgable about Prepared Stmts,
I think that in this case we will use bind_result instead
of bind_param. Thank you very much!

- dpati