Solved

php mysqli works for sql but not working for stored procedure

Posted on 2010-11-16
8
582 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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
 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

770 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