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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
dpati
Forum Newbie
Posts: 3
Joined: Wed Jan 23, 2008 8:58 am

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

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

Post 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... :)
dpati
Forum Newbie
Posts: 3
Joined: Wed Jan 23, 2008 8:58 am

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

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

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

Post 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;
 
dpati
Forum Newbie
Posts: 3
Joined: Wed Jan 23, 2008 8:58 am

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

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