Re: Calling stored procedure from PHP
Hi,
I get some strange results calling stored procedures from PHP.
With following data:
DROP TABLE IF EXISTS `countries`;
CREATE TABLE `countries` (
`id` int(11) NOT NULL auto_increment,
`country` varchar(50) default NULL,
`capital` varchar(50) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `land` (`country`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;
INSERT INTO `countries` VALUES (1, 'Deutschland', 'Berlin');
INSERT INTO `countries` VALUES (2, 'Schweiz', 'Bern');
INSERT INTO `countries` VALUES (3, 'Österreich', 'Wien');
INSERT INTO `countries` VALUES (4, 'Frankreich', 'Paris');
INSERT INTO `countries` VALUES (5, 'Türkei', 'Ankara');
INSERT INTO `countries` VALUES (6, 'USA', 'Washington');
... and following stored procedures:
CREATE PROCEDURE getCountries()
begin
select country from countries order by country;
end//
CREATE PROCEDURE getCapitals(IN country_in varchar(100))
begin
select concat(country, ", capital: ", capital) from countries where country like country_in;
end//
... and finally this PHP script:
<?php
ini_set("display_errors", 1);
$mysqli = new mysqli("10.2.1.4", "me", "abc123", "mysql5_tests");
if (mysqli_connect_errno())
die ("Error connecting MySQL: ".mysqli_connect_error());
$sql1 = "call getCountries()";
//SQL which does the same thing:
//$sql1 = "select country from countries order by country";
$query1 = $mysqli->query($sql1);
while ($data1 = $query1->fetch_row())
{
echo $data1[0]."<br />\n";
$sql2 = "call getCapitals(\"".$data1[0]."\")";
//SQL which does the same thing:
//$sql2 = "select concat(country, \", capital: \", capital) from countries where country like '".$data1[0]."'";
$query2 = $mysqli->query($sql2);
while ($data2 = $query2->fetch_row())
{
echo $data2[0]."<br />\n";
}
$query2->free(); // unsuccessful attempt to clear the results and get the next SP going
}
?>
... I get following output:
Deutschland
Fatal error: Call to a member function fetch_row() on a non-object in D:\Apache2\htdocs\test\several_mysql50_tests.php on line 34
So it seems that I can't query more than one stored procedure at once, even with $query2->free().
Another strange result with the same data and following PHP script:
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<?php
$mysqli = new mysqli("10.2.1.4", "me", "abc123", "mysql5_tests");
$mysqli2 = new mysqli("10.2.1.4", "me", "abc123", "mysql5_tests");
if (mysqli_connect_errno())
die ("Error connecting MySQL: ".mysqli_connect_error());
$sql1 = "call getCapitals(\"Österreich\")";
$query1 = $mysqli->query($sql1);
while ($data1 = $query1->fetch_row())
{
echo $data1[0]."<br />\n";
}
echo "<br />List all countries:<br /><br />\n";
$sql2 = "select country from countries where country like 'Österreich' order by country";
$query2 = $mysqli2->query($sql2);
while ($data2 = $query2->fetch_row())
{
echo $data2[0]."<br />\n";
}
?>
The stored procedure ($sql1) fails, the SQL string ($sql2) works.
If I change the first line to
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
... it's just the other way around, now the stored procedure works and the SQL string fails.
That must be because of the umlaut ("Ö"), with iso-8859-1 it works in SQL strings, with utf-8 it works in stored procedures.
Greetz,
Markus