[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

PHP + mysqli + dislaying stored orocedure results

Posted on 2014-04-09
21
Medium Priority
?
647 Views
Last Modified: 2014-04-11
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?
0
Comment
Question by:_agx_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
  • 3
  • +1
21 Comments
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39990158
What is the result of the stored procedure when you run it directly from MySQL?
0
 
LVL 52

Author Comment

by:_agx_
ID: 39990174
In MySQLWorkbench, call p(); returns

id + 1
----------
2
3
4
0
 
LVL 35

Assisted Solution

by:Dan Craciun
Dan Craciun earned 400 total points
ID: 39990185
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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 52

Author Comment

by:_agx_
ID: 39990356
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
 
LVL 52

Author Comment

by:_agx_
ID: 39990367
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
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 39990451
It worked for me with PHP 5.3.8 and MySQL 5.0.45.
0
 
LVL 52

Author Comment

by:_agx_
ID: 39990538
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
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 39990596
Add "error_reporting(E_ALL);" as the second line of your script and see if you get any messages.
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39990676
Use root to connect to your database. If the result displays, it's a permission problem.
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 39990691
If it's not 'localhost', "root" usually isn't allowed to connect.
0
 
LVL 52

Author Comment

by:_agx_
ID: 39991390
> 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
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 39992077
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
 
LVL 59

Assisted Solution

by:Julian Hansen
Julian Hansen earned 200 total points
ID: 39992497
Just for giggles try
$recs = $res->fetch_all();
if (!is_array($recs)) {
  die("Not an array");
}
print_r($recs);

Open in new window

0
 
LVL 84

Accepted Solution

by:
Dave Baldwin earned 1400 total points
ID: 39992636
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
 
LVL 52

Author Comment

by:_agx_
ID: 39992770
> 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
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 39992814
It would look a lot like this (in phpinfo()):
mysqlnd in phpinfo()
0
 
LVL 52

Author Comment

by:_agx_
ID: 39992829
Thanks. Mine goes from "mysqli" to "odbc", nothing in between. So I think that confirms it..
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 39992842
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
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 39992857
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
 
LVL 52

Author Comment

by:_agx_
ID: 39992882
> 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
 
LVL 52

Author Closing Comment

by:_agx_
ID: 39995196
@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

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question