PHP + mysqli + dislaying stored orocedure results

I'm brand new to PHP. I need to retrieve the results of a mysql stored procedure. So I looked at the docs and tried example #3. The only change was host and db credentials.

The docs say it should output something like:

---
array(3) {
  [0]=>
  array(1) {
    [0]=>
    string(1) "1"
  }
  [1]=>
  .....

Open in new window


But my test page simply outputs the header and nothing else:
    ---

Open in new window


If I log into the db, the procedure p() exists, and returns data when executed. Yet my php script shows nothing but "---".

Any ideas on what might be going wrong or how to debug it?
LVL 53
_agx_Asked:
Who is Participating?
 
Dave BaldwinFixer of ProblemsCommented:
For what it's worth, I got it running on PHP 5.2.17 by replacing var_dump($res->fetch_all()); with a loop using $res->fetch_row() in a loop and then dumping that.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
 "http://www.w3.org/TR/html4/loose.dtd">

<html>
<head>
<title>mysqli stored proc demo</title>
</head>
<body>
<h1>mysqli stored proc demo</h1>
<pre>

<?php
$mysqli = new mysqli("localhost", "myuser", "mypassword", "mydatabase");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
    !$mysqli->query("CREATE TABLE test(id INT)") ||
    !$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)")) {
    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

if (!$mysqli->query("DROP PROCEDURE IF EXISTS p") ||
    !$mysqli->query('CREATE PROCEDURE p() READS SQL DATA BEGIN SELECT id FROM test; SELECT id + 1 FROM test; END;')) {
    echo "Stored procedure creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

if (!$mysqli->multi_query("CALL p()")) {
    echo "CALL failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

$rezlt = array();
do {
    if ($res = $mysqli->store_result()) {
        printf("---\n");
				while($rowz=$res->fetch_row()) {
					$rezlt[] = $rowz;
					}
        //var_dump($res->fetch_all());
        var_dump($rezlt);
        $res->free();
    } else {
        if ($mysqli->errno) {
            echo "Store failed: (" . $mysqli->errno . ") " . $mysqli->error;
        }
    }
} while ($mysqli->more_results() && $mysqli->next_result());
?>

</pre>
</body>
</html>

Open in new window

0
 
Dan CraciunIT ConsultantCommented:
What is the result of the stored procedure when you run it directly from MySQL?
0
 
_agx_Author Commented:
In MySQLWorkbench, call p(); returns

id + 1
----------
2
3
4
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Dan CraciunIT ConsultantCommented:
It works here. MySQL 5.6, php 5.5

The output is:
--- array(3) { [0]=> array(1) { [0]=> string(1) "1" } [1]=> array(1) { [0]=> string(1) "2" } [2]=> array(1) { [0]=> string(1) "3" } } --- array(3) { [0]=> array(1) { [0]=> string(1) "2" } [1]=> array(1) { [0]=> string(1) "3" } [2]=> array(1) { [0]=> string(1) "4" } }

Can you post the code you're using?
0
 
_agx_Author Commented:
It's literally a copy and paste of example #3. I just deleted and recreated the file to be sure.  Only thing I changed was the 2nd line:

$mysqli = new mysqli("localhost", "myuser", "mypassword", "mydatabase");

(Edit)  Maybe some sort of permissions problem ... or error occurring that isn't shown?
0
 
_agx_Author Commented:
It works here. MySQL 5.6, php 5.5
(Edit)  Thanks for the sanity check.  I'm running:

PHP 5.3.3
MySQL 5.1.69

Here it my test file fwiw:

<?php

$mysqli = new mysqli("localhost", "myuser", "mypassword", "mydatabase");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
    !$mysqli->query("CREATE TABLE test(id INT)") ||
    !$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)")) {
    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

if (!$mysqli->query("DROP PROCEDURE IF EXISTS p") ||
    !$mysqli->query('CREATE PROCEDURE p() READS SQL DATA BEGIN SELECT id FROM test; SELECT id + 1 FROM test; END;')) {
    echo "Stored procedure creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

if (!$mysqli->multi_query("CALL p()")) {
    echo "CALL failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

do {
    if ($res = $mysqli->store_result()) {
        printf("---\n");
        var_dump($res->fetch_all());
        $res->free();
    } else {
        if ($mysqli->errno) {
            echo "Store failed: (" . $mysqli->errno . ") " . $mysqli->error;
        }
    }
} while ($mysqli->more_results() && $mysqli->next_result());
?>

Open in new window

0
 
Dave BaldwinFixer of ProblemsCommented:
It worked for me with PHP 5.3.8 and MySQL 5.0.45.
0
 
_agx_Author Commented:
Yeah, obviously something's different in my environ .. but I'm not sure what :/ Permissions maybe?  But I'd expect to see an error, but there's nothing in the output.

I'll check back tomorrow if anyone has any ideas on how to troubleshoot this.
0
 
Dave BaldwinFixer of ProblemsCommented:
Add "error_reporting(E_ALL);" as the second line of your script and see if you get any messages.
0
 
Dan CraciunIT ConsultantCommented:
Use root to connect to your database. If the result displays, it's a permission problem.
0
 
Dave BaldwinFixer of ProblemsCommented:
If it's not 'localhost', "root" usually isn't allowed to connect.
0
 
_agx_Author Commented:
> Add "error_reporting(E_ALL);" as the second line of your script

No change.

   <?php
      error_reporting(E_ALL);
      ...

> Use root to connect to your database.
> If it's not 'localhost', "root" usually isn't allowed to connect.

Tried it for the heck of it, same result: "---"

I also added some debug statements, before and after the dump.

        printf("---\n");
        echo "before dump";
        var_dump($res->fetch_all());
        echo "after dump";
        $res->free();

Only the "before" statement prints:

       --- before dump
0
 
Dave BaldwinFixer of ProblemsCommented:
Run phpinfo() and see the the 'mysqlnd' driver is loading.  This page http://us1.php.net/manual/en/mysqli-result.fetch-all.php says it only works with the 'mysqlnd' driver.  And the 'mysqlnd' driver is not the default driver until PHP 5.4.
0
 
Julian HansenCommented:
Just for giggles try
$recs = $res->fetch_all();
if (!is_array($recs)) {
  die("Not an array");
}
print_r($recs);

Open in new window

0
 
_agx_Author Commented:
> Just for giggles try

No change.

> Run phpinfo() and see the the 'mysqlnd' driver is loading.

Silly question - what would it look like if it were loaded?  Can't post a screen shot right now, but I see categories for "mysql" (Client API 5.1.69 ....) and "mysqli", but a find in page turns up nothing for "mysqln".
0
 
Dave BaldwinFixer of ProblemsCommented:
It would look a lot like this (in phpinfo()):
mysqlnd in phpinfo()
0
 
_agx_Author Commented:
Thanks. Mine goes from "mysqli" to "odbc", nothing in between. So I think that confirms it..
0
 
Dave BaldwinFixer of ProblemsCommented:
Try my code above in http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28409081.html#a39992636 .  I hacked your code up to work on PHP 5.2.17 which does not have 'mysqlnd' available.  It's not exact but the first part of the display is.
0
 
Dave BaldwinFixer of ProblemsCommented:
I just double checked and there is an extension for 'php_mysqli_mysqlnd.dll' on my machine.  While it's not 'enabled' on my machine, you may be able to 'enable' it on yours and get the original code working.  That is, if it is included with your copy and version of PHP.
0
 
_agx_Author Commented:
> Try my code above in

Oh, thanks for that.  I totally missed it in all my multi-tasking.  I have to get something out the door, in the next hour, but will try it (and your other suggestion after that).
0
 
_agx_Author Commented:
@Dave Baldwin - Perfect, that version works for me too.  Now I actually have something to work with besides a blank screen :) It's probably the missing mysqlnd driver like you said. I'll try installing it later.  

Thanks everyone for the responses!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.