Ad:

PHP Discussion Thread
Unsolved
Views: 11605

Page 1
2
Next

You are currently viewing page 1 of this multi-page discussion thread

Nov 7th, 2007

PHP and MySQL Stored Procedure Exec Problem

Expand Post »

here is my stored procedure

sql Syntax (Toggle Plain Text)
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
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 Syntax (Toggle Plain Text)
<?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];
}
?>
<?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 Syntax (Toggle Plain Text)
<?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]; 
}
?>
<?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.

Last edited by fatihpiristine; Nov 7th, 2007 at 7:11 pm.

Similar Threads

PHP MySQL 5.0 stored procedure in PHP
how to call Stored Procedure in mysql by php in PHP
php mysql problem in PHP
how do I run a "disconnected" stored procedure in MS SQL
Stored procedure call with ADO in C

fatihpiristine

Reputation Points: 6

Solved Threads: 19

Posting Whiz in Training

Offline

283 posts
since Sep 2007

Permalink

Nov 8th, 2007

Re: PHP and MySQL Stored Procedure Exec Problem

mysqli version.. doesnt work also...

php Syntax (Toggle Plain Text)
<?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>";
}
?>
<?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>";
}
?>

fatihpiristine

Reputation Points: 6

Solved Threads: 19

Posting Whiz in Training

Offline

283 posts
since Sep 2007

Permalink

Nov 8th, 2007

Re: PHP and MySQL Stored Procedure Exec Problem

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

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

ch.-

chmazur

Reputation Points: 10

Solved Threads: 3

Light Poster

Offline

30 posts
since Sep 2007

Permalink

Nov 9th, 2007

Re: PHP and MySQL Stored Procedure Exec Problem

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

Last edited by fatihpiristine; Nov 9th, 2007 at 2:18 am.

fatihpiristine

Reputation Points: 6

Solved Threads: 19

Posting Whiz in Training

Offline

283 posts
since Sep 2007

Permalink

Nov 9th, 2007

Re: PHP and MySQL Stored Procedure Exec Problem

did you set an out parameter on the procedure?

kkeith29

Reputation Points: 235

Solved Threads: 193

Nearly a Posting Virtuoso

Offline

1,315 posts
since Jun 2007

Permalink

Nov 9th, 2007

Re: PHP and MySQL Stored Procedure Exec Problem

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

fatihpiristine

Reputation Points: 6

Solved Threads: 19

Posting Whiz in Training

Offline

283 posts
since Sep 2007

Permalink

Nov 10th, 2007

Re: PHP and MySQL Stored Procedure Exec Problem

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

here is the procedure:

PHP Syntax (Toggle Plain Text)
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
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 Syntax (Toggle Plain Text)
<?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
 
}
<?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

}

kkeith29

Reputation Points: 235

Solved Threads: 193

Nearly a Posting Virtuoso

Offline

1,315 posts
since Jun 2007

Permalink

Nov 10th, 2007

Re: PHP and MySQL Stored Procedure Exec Problem

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

still error.. no output

Last edited by fatihpiristine; Nov 10th, 2007 at 4:23 pm.

fatihpiristine

Reputation Points: 6

Solved Threads: 19

Posting Whiz in Training

Offline

283 posts
since Sep 2007

Permalink

Nov 10th, 2007

Re: PHP and MySQL Stored Procedure Exec Problem

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.

kkeith29

Reputation Points: 235

Solved Threads: 193

Nearly a Posting Virtuoso

Offline

1,315 posts
since Jun 2007

Permalink

Nov 10th, 2007

Re: PHP and MySQL Stored Procedure Exec Problem

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

fatihpiristine

Reputation Points: 6

Solved Threads: 19

Posting Whiz in Training

Offline

283 posts
since Sep 2007

Page 1
2
Next

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in PHP Forum Timeline: Sending email fails

Next Thread in PHP Forum Timeline: Insert a new record in phpmyadmin db then display the record on new php page

DaniWeb Message

Cancel Changes

User Name
Password