asked on 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
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
PHPMySQL Server
Last Comment
Andrew Angell
ASKER
{$table}_zip() is not the problem.
I have tried even hard typing the full stored procedure name it still does not work
I have tried even hard typing the full stored procedure name it still does not work
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
I had even tested with a single stored procedure call it it does not give results.
Try this function instead of calling your store directly
(from http://www.dev-explorer.com/articles/mysql-stored-procedures-and-mysqli)
(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;
}
}
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Oops, missing semicolon ";" :)
OK, need to see the output of this code :
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();
?>
ASKER
Thanks issue was every Execute command was denied for the user. Now resolved
$sql = "CALL ".$table."_zip();";
and so on.