By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,179 Members | 2,175 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,179 IT Pros & Developers. It's quick & easy.

PHP and MySQL: How to Properly call a Stored Procedure with Output Parameters from PHP - mysql, mysqli, or PDO

P: n/a
Hi All,

I am currently using PHP 5 and MySQL 5, both on Fedora Core 5. I am
unable to call a MySQL stored procedure that returns output parameters
using mysql, mysqli, or PDO. I'm having a hell of a time with it...

The following comes from phpinfo():
PHP Version: 5.1.2
mysql Client API version: 5.0.18
mysqli Client API version: 5.0.18
PDO Driver for MySQL, client library version: 5.0.18

MySQL version, from running SELECT version(); = 5.0.18

Here's the procedure I've created in MySQL:
CREATE PROCEDURE testproc(OUT p_testvar varchar(50)) BEGIN SET
p_testvar = 'HELLO WORLD!'; END
From the MySQL Command Line, I can do the following:

mysql> call testproc(@a);
Query OK, 0 rows affected (0.00 sec)

mysql> select @a;
+--------------+
| @a |
+--------------+
| HELLO WORLD! |
+--------------+
1 row in set (0.00 sec)

Now, on the PHP side, I'm using the code samples in the documentation
at:
http://www.php.net/manual/en/ref.pdo.php

under the heading "Example 9. Calling a stored procedure with an output
parameter"

<?
$user = "username";
$pass = "pAsSwOrD";

try {
$dbh = new PDO('mysql:host=localhost;dbname=myDB', $user, $pass);

$stmt = $dbh->prepare("CALL testproc(?)");
$stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000);

// call the stored procedure
$stmt->execute();

print "procedure returned $return_value\n";

$dbh = null;
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
die();
}
?>
When I run this PHP file, it outputs nothing. No output whatsoever.
No error, no source code, nothing. Database username and password are
the same as those I'm using when I log in at the mysql command line.

I've tried using named parameters as well, with no success.
Just FYI, I can run the following from PHP:
<?
$user = "username";
$pass = "pAsSwOrD";

try {
$dbh = new PDO('mysql:host=localhost;dbname=myDB', $user, $pass);

foreach ($dbh->query('SELECT * from url_redirect') as $row) {
print_r($row);
}

$dbh = null;
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
die();
}
?>

and I get the rows from the table.

I've also tried using the mysqli extension, rather than PDO. I'm using
the examples at
http://mysql.gilfster.com/page.php?p...&page_id=4.0.3 - using
the code sample under the section titled "Using Parameters", right
above the link to "php_proc_helloworld.phx". My code is as follows:

<?php
$user = "username";
$pass = "pAsSwOrD";
$db = "databasename";

$link = mysqli_connect("localhost",$user,$pass);

if (mysqli_connect_errno()) {
echo "connection error";
exit();
}

mysqli_select_db ($link,$db);

if ($result = mysqli_multi_query($link,"call testproc(@out_param)")) {

if ($result = mysqli_multi_query($link,"select @out_param")) {

$row = mysqli_fetch_array($result, MYSQLI_NUM);

printf($row[0]);

mysqli_free_result($result);

} else { echo "problem :( inner "; }

} else { echo "problem :( outer"; }

?>
When I run this, I also get nothing. No errors, no output, nothing.
The browser completes the load of the page, but it has no output on it.
Any thoughts or advice is greatly appreciated. I'm willing to try
almost anything at this point - I'm totally stumped.

Thanks,

Evan

Apr 10 '06 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.