469,647 Members | 1,789 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,647 developers. It's quick & easy.

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

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 |
1 row in set (0.00 sec)

Now, on the PHP side, I'm using the code samples in the documentation

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

$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

print "procedure returned $return_value\n";

$dbh = null;
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
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) {

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

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:

$user = "username";
$pass = "pAsSwOrD";
$db = "databasename";

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

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

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);



} 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.



Apr 10 '06 #1
0 18786

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by James | last post: by
3 posts views Thread by Chris | last post: by
4 posts views Thread by Pakna | last post: by
1 post views Thread by Frank Moyles | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.