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
From PHP: retrieve the value saved in MYSQL SPs OUT param
Moderator: General Moderators
-
jackcobain
- Forum Newbie
- Posts: 2
- Joined: Fri Jan 25, 2008 3:07 pm
Re: From PHP: retrieve the value saved in MYSQL SPs OUT param
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...
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
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
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
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
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
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