Solved

php mysqli works for sql but not working for stored procedure

Posted on 2010-11-16
8
581 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
ID: 34146482
{$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
ID: 34146600
{$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
ID: 34146629
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
ID: 34146929
I had even tested with a single stored procedure call it it does not give results.
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 13

Expert Comment

by:dsmile
ID: 34147043
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
ID: 34147381
Hello,

try :

echo $mysqli->error

Open in new window


what's the output?
0
 
LVL 3

Expert Comment

by:Prograministrator
ID: 34147622
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
ID: 34147696
Thanks issue was every Execute command was denied for the user. Now resolved
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
change password links 7 72
mcrypt_create_iv() is deprecated 4 69
Calculating number of days between two dates 7 31
Sql query on a varchar that is numeric. 8 26
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
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…
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 …

911 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

16 Experts available now in Live!

Get 1:1 Help Now