MySQL Forums
Forum List  »  Stored Procedures

Re: Calling stored procedure from PHP
Posted by: Markus Popp
Date: April 25, 2005 01:10PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
59404
March 18, 2005 05:58PM
8437
November 26, 2008 05:09AM
Re: Calling stored procedure from PHP
22261
April 25, 2005 01:10PM
3962
October 22, 2006 10:03PM
4056
November 03, 2006 02:30AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.