Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 596
  • Last Modified:

php mysqli works for sql but not working for stored procedure



$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
Andrew Angell
Asked:
Andrew Angell
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
jfromanskiCommented:
{$table}_zip() is not correct. if your procedure is named MyTable_zip, then use
$sql = "CALL ".$table."_zip();";
and so on.
0
 
Andrew AngellCo-Owner / DeveloperAuthor Commented:
{$table}_zip() is not the problem.
I have tried even hard typing the full stored procedure name it still does not work
0
 
dsmileCommented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Andrew AngellCo-Owner / DeveloperAuthor Commented:
I had even tested with a single stored procedure call it it does not give results.
0
 
dsmileCommented:
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
 
PrograministratorCommented:
Hello,

try :

echo $mysqli->error

Open in new window


what's the output?
0
 
PrograministratorCommented:
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
 
Andrew AngellCo-Owner / DeveloperAuthor Commented:
Thanks issue was every Execute command was denied for the user. Now resolved
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.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now