Solved

php mysqli works for sql but not working for stored procedure

Posted on 2010-11-16
8
580 Views
Last Modified: 2012-05-10


$table = "alabama";

$sql = "SELECT distinct zip FROM {$table} where zip is not null order by zip;";
$sql .= "SELECT distinct city FROM {$table} where city is not null order by city;";
$sql .= "SELECT distinct county FROM {$table} where county is not null order by county;";

$mysqli = new MySQLI('host','user','pass','db');
if ($mysqli->multi_query($sql)) {
       do {
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_row()) {
                 echo($row[0]);
            }
            $result->close();
        }
        if ($mysqli->more_results()) {
            echo ("next result");
        }
    } while ($mysqli->next_result());
}
$mysqli->close();

The above works great now the issue is using the same sql in a stored procedure (tested stored procedure works good in command line). If I replace the sql string as

$sql = "CALL {$table}_zip();";
$sql .= "CALL {$table}_city();";
$sql .= "CALL {$table}_county();";

then the above script does not give results.

I even tested with a single stored procedure call it it does not give results.

Any help is greatly appreciated
0
Comment
Question by:Andrew Angell
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 10

Expert Comment

by:jfromanski
Comment Utility
{$table}_zip() is not correct. if your procedure is named MyTable_zip, then use
$sql = "CALL ".$table."_zip();";
and so on.
0
 
LVL 11

Author Comment

by:Andrew Angell
Comment Utility
{$table}_zip() is not the problem.
I have tried even hard typing the full stored procedure name it still does not work
0
 
LVL 13

Assisted Solution

by:dsmile
dsmile earned 250 total points
Comment Utility
You might want to read this.

http://php.net/manual/en/mysqli.store-result.php

The problem is that you cannot handle multiple stored procedures at once without free all previous' recordsets
0
 
LVL 11

Author Comment

by:Andrew Angell
Comment Utility
I had even tested with a single stored procedure call it it does not give results.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 13

Expert Comment

by:dsmile
Comment Utility
Try this function instead of calling your store directly

(from http://www.dev-explorer.com/articles/mysql-stored-procedures-and-mysqli)
class MySQLDB extends mysqli {

   public function storedProcedure($proc_name, $params){
      $ds = array();
      if ($result = $this->query("CALL $proc_name('" . implode("', '", $params) . "');")) {
         if ($result->num_rows > 0){
            while ($row = $result->fetch_array(MYSQLI_ASSOC)){
               $ds[] = $row;
            }
            $result->close();
         }
         $this->next_result();
      }
      $this->commit();
      return $ds;
   }
}

Open in new window

0
 
LVL 3

Accepted Solution

by:
Prograministrator earned 250 total points
Comment Utility
Hello,

try :

echo $mysqli->error

Open in new window


what's the output?
0
 
LVL 3

Expert Comment

by:Prograministrator
Comment Utility
Oops, missing semicolon ";" :)

OK, need to see the output of this code :

<?php
$table = "alabama";
$sql = "CALL {$table}_zip();";
$sql .= "CALL {$table}_city();";
$sql .= "CALL {$table}_county();";
$mysqli = new MySQLI('host','user','pass','db');
if ($mysqli->multi_query($sql)) {
       do {
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_row()) {
                 echo($row[0]);
            }
            $result->close();
        }
        if ($mysqli->more_results()) {
            echo ("next result");
        }
    } while ($mysqli->next_result());
}
else {
echo $mysqli->error;
}
$mysqli->close();
?>

Open in new window

0
 
LVL 11

Author Closing Comment

by:Andrew Angell
Comment Utility
Thanks issue was every Execute command was denied for the user. Now resolved
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
This article discusses four methods for overlaying images in a container on a web page
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now