PHP and MySQL Stored Procedure Exec Problem

[fatihpiristine]

here is my stored procedure

begin
select
indexes.IndexID,
languages.LanguageID,
languages.LanguageName,
indexes.OrderID,
indexes.IndexName
FROM
indexes, languages
where indexes.LanguageID = languages.LanguageID and languages.LanguageID = Language
order by indexes.OrderID asc;
end

here is my php code

<?php
$Server = "127.0.0.1";
$Port = ":"."3306";
$DataBase = "abc";
$UserName = "def";
$PassWord = "blabla";
$Flags = "MYSQL_CLIENT_SSL";
$Connection = mysql_connect($Server . $Port, $UserName, $PassWord, $Flags, 65536);
if(!$Connection)
{
    die("Could not connect to server : " . mysql_error());
}
else
{
    mysql_select_db($DataBase,$Connection) or die("Could not connect to database");
}

$TM = mysql_query("Call SP_Index($Language)",$Connection);
while($TM_W = mysql_fetch_array($TM))
{
	$i1++;
        print $TM_W[4];
}
?>

so far everything works fine after calling second procedure.
.... doesnt show up anything... i got empty array

<?php
$Cat = mysql_query("Call SP_Category($Language)",$Connection);
$Cat_N = mysql_num_rows($Cat);

while($Cat_W = mysql_fetch_array($Cat))
{
	$i3++;
        print $Cat_W[4]; 
}
?>

i closed and opened the connection and freed the previous array etc etc..
still nothing.. anyone has idea??

thanx in advance.

[fatihpiristine]

mysqli version.. doesnt work also... :(

<?php
$Server = "127.0.0.1";
$DataBase = "teknober";
$UserName = "root";
$PassWord = "19741968";
$Connection = mysqli_connect($Server, $UserName, $PassWord);
if(!$Connection)
{
    die("Could not connect to server : " . mysqli_error());
}
else
{
    mysqli_select_db($Connection,$DataBase) or die("Could not connect to database");
}
?>
<?php
print "Procedure #1<br>";

$TM = mysqli_query($Connection, "CALL mypro;");
while($TM_W = mysqli_fetch_array($TM))
{
	print $TM_W[2] . "<br>";
}

?>
<?php
print "<hr>";
print "Procedure #2<br>";
$TM = mysqli_autocommit($Connection,1);
$TM = mysqli_query($Connection, "select * from news;");
while($TM_W = mysqli_fetch_array($TM))
{
	print $TM_W[2] . "<br>";
}
?>

[chmazur]

On your store procedure.
...
...
where indexes.LanguageID = languages.LanguageID and languages.LanguageID = Language
...
...

what is Language? Did you try with $Languaje (?)

ch.-

[fatihpiristine]

language is the input parameter. i deleted it and still same. got error...

[somedude3488]

did you set an out parameter on the procedure?

[fatihpiristine]

out parameter is not so important coz i need all values out. if i do something without using tables like:

declare in_x, in_y, out_z int;
select in_x * in_y = out_z;

select out_z;
end

[somedude3488]

i got a smiliar procedure to work on my server. i hope this helps.

here is the procedure:

CREATE PROCEDURE SP_Index (IN lang VARCHAR(255), OUT info BLOB) 
BEGIN
SELECT 
indexes.IndexID,
languages.LanguageID,
languages.LanguageName,
indexes.OrderID,
indexes.IndexName INTO info 
FROM indexes,languages 
WHERE indexes.LanguageID = languages.LanguageID AND languages.Language = lang 
ORDER BY indexes.OrderID ASC;
END

Here is the php:

<?php
$Server = "127.0.0.1";
$Port = ":"."3306";
$DataBase = "abc";
$UserName = "def";
$PassWord = "blabla";
$Flags = "MYSQL_CLIENT_SSL";
$Connection = mysql_connect($Server . $Port, $UserName, $PassWord, $Flags, 65536);
if(!$Connection)
{
    die("Could not connect to server : " . mysql_error());
}
else
{
    mysql_select_db($DataBase,$Connection) or die("Could not connect to database");
}

$TM = mysql_query("Call SP_Index($Language, @data)", $Connection);
$TM2 = mysql_query('SELECT @data');
while ($TM_W = mysql_fetch_array($TM2)) {

//DO WHAT YOU WANT

}

[fatihpiristine]

Did you test this? It doesn't work, i got it work somehow but.. when i call the second procedure..

still error.. no output

[somedude3488]

yes i did test it and it worked for me. did you set up your other procedure the same way? i have looked into procedures before but i have never really used them. i am going off basic knowledge and the mysql manual. i am just as lost as you are to why this is not working.

[fatihpiristine]

i m get more used to mssql procedures... i think i need to write kinda php module to execute multi stored procedures. thanks anyways

[klusik]

It is bug (or feature?) in PHP, calling procedures is not possible ;/

Look at: http://www.php.net/manual/en/function.mysql-query.php#82429