?
Solved

php mysqli works for sql but not working for stored procedure

Posted on 2010-11-16
8
Medium Priority
?
589 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
[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
  • 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 1000 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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
The viewer will learn how to count occurrences of each item in an array.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

752 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