function contains mysql_query inside scope of stored procedure does not work!!

[Moderns]

Hello,

I am facing something strange. I will discuss it by very clear and simple example below.

1)I have function called Test() in page1.php (gives correct output) as follows:

function Test(){
$sql = "SELECT * FROM accounts WHERE account_id = 4";
$rs = mysql_query($sql);
while ($row = mysql_fetch_array($rs))
$mobile_number = $row;

return $mobile_number;
}

2)I have stored procedure called GetAccounts() in page2.php that gets all accounts without IN and OUT parameters, I call it as follows:

$sql = "CALL GetAccounts()"; //LINE 1
///Here calling the function Test()works fine!!!
$result = mysql_query($sql); //LINE 2
////Here if I call the function Test(), it gives mysql error as below.
while($row = mysql_fetch_array($result)) //LINE 3
print_r($row);

Now the problem, when I call the function Test() between LINE 1 and LINE 2, it works fine, please see the comment I indicated above. Also it works fine before LINE 1.

But, when calling the function Test() between LINE 2 and LINE 3, it gives the mysql error: Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in.... Also the error appear when I call it after LINE 3.

I am really wondering!! It happens only when using stored procedure, but when using normal query(without stored procedure) no such problem happens!!

Is it bug in PHP? How can I solve it?

Please advise and many thanks.

Regards,

[bakir]

can u please post all ur code to make better view

[Moderns]

can u please post all ur code to make better view

It's already posted above.

[pritaeas]

I am wondering, if you use mysql_close() in your Test() function, do you still get the same error ?

[bakir]

It's already posted above.

some of it we need to check that function getAccounts()

i just think the problem that at line 1

$sql = "CALL GetAccounts()";

and this wat is supposed to do? i think it is invalid call to mysql
so execution this string will give an error in the $result and not a result set
and he fetch the $result as an invalid arguments to the function fetch;
when he execute test between the line 1 and line 3 the sql string change to that one
found in the function since and hence this can lead to valid sql statement and hence no
errors found and also as u mentioned that maybe he didnt close the sql then maybe he get the same result generated via test function

best regards

[pritaeas]

GetAccounts is a MySql stored procedure. If that function exists, it is a valid query.

[Moderns]

some of it we need to check that function getAccounts()

i just think the problem that at line 1

$sql = "CALL GetAccounts()";

and this wat is supposed to do? i think it is invalid call to mysql
so execution this string will give an error in the $result and not a result set
and he fetch the $result as an invalid arguments to the function fetch;
when he execute test between the line 1 and line 3 the sql string change to that one
found in the function since and hence this can lead to valid sql statement and hence no
errors found and also as u mentioned that maybe he didnt close the sql then maybe he get the same result generated via test function

best regards

GetAccounts is a stored procedure as below created in PHPMYADMIN:

DELIMITER ;;
DROP PROCEDURE IF EXISTS GetAccounts ;;
CREATE PROCEDURE GetAccounts()
BEGIN
SELECT accounts.username FROM accounts WHERE (accounts.account_id = 4 OR accounts.account_id = 5);
END ;;

[bakir]

first i would say i m here not only to help just also to learn so any mistake i did or i do
im sorry for that
second i just look at sql as string joint 2 strings sorry that was ma mistake as

$sql = "CALL".getAccounts();

since he said "stored procedure called GetAccounts() in page2.php" so thought it is php function
sorry again.
i will check it ma self and i will inform ya with the result
sorry again for miss understanding

[bakir]

by the way wat do u mean that getAccounts() in page2.php

[bakir]

hi again this results wat i get in these cases
CASE 1

$createProc = "$.. " //added here u mysql procedure 
mysql_query($createProc);

$sql = "CALL getAccounts()";
$result = mysql_query($sql);

// after executing it like  how u did 
while($row = mysql_fetch_array($result))
print_r($row);

got result with those who have accid = 4 or 5;

now in
CASE 2 as u did exactly but set sql = "invalid mysql";

$sql = "invalid mysql"; //LINE 1

$result = mysql_query($sql); //LINE 2

while($row = mysql_fetch_array($result)) //LINE 3
print_r($row);

the result was if test() after line 1 then i get result with those
who have accid = 4 only

if i move test() after line 2 or 3 i get error like u said
so if i m right u first $sql is calling an invalid sql statement

maybe that is not the real problrm but that wat happened after the test i made

base regards

[Moderns]

hi again this results wat i get in these cases
CASE 1

$createProc = "$.. " //added here u mysql procedure 
mysql_query($createProc);

$sql = "CALL getAccounts()";
$result = mysql_query($sql);

// after executing it like  how u did 
while($row = mysql_fetch_array($result))
print_r($row);

got result with those who have accid = 4 or 5;

now in
CASE 2 as u did exactly but set sql = "invalid mysql";

$sql = "invalid mysql"; //LINE 1

$result = mysql_query($sql); //LINE 2

while($row = mysql_fetch_array($result)) //LINE 3
print_r($row);

the result was if test() after line 1 then i get result with those
who have accid = 4 only

if i move test() after line 2 or 3 i get error like u said
so if i m right u first $sql is calling an invalid sql statement

maybe that is not the real problrm but that wat happened after the test i made

base regards

Thanks for your inputs, actually yes as I told you, any PHP function that uses the mysql_fetch() function can't be used inside stored procedure scope in PHP, I think the solution is to make the database inquiry inside Test() function stored procedure as well. I did not try it yet.

[bakir]

Hi Moderns
let me say i cant see any difference between using that in any function or not ..
actually the way that interpreter or compiler does not care about function or not
it is just some instruction that kept to frequent use .. as u know the name of the function just refer to the location of these instruction .. and in compilin time just
it will be replace in main method or idk in php wat is it but let me say in the reference page that contains it .. so no error will rise as the interpreter or compiler will look at as one code (linkin) sono diffrence when u use subroutine or
just used it directly .

as in your case also i would say the failure in ur code is not in the test() function. and the evidence that it worked once (when u put it between L1 and L2)
hence ur assumption is not right.

as u move ur test() to anther Lx then failure happened ... then this not failure in
test() and hence as u said in mysql_fetch maybe the failure happened coz of test()
on the other line ( let just say that at the moment) but not in it.

so better suggestion let's try this :

$sql = "SELECT accounts.username FROM accounts WHERE (accounts.account_id = 4 OR accounts.account_id = 5)"; //LINE 1
///Here calling the function Test()works fine!!!
$result = mysql_query($sql); //LINE 2
////Here if I call the function Test(), it gives mysql error as below.
while($row = mysql_fetch_array($result)) //LINE 3
print_r($row);

i doubt u will get an error
i have tried it .. u should to tell me wat u get then
and also wat php version u use

sorry for this long post

[Moderns]

Hi Moderns
let me say i cant see any difference between using that in any function or not ..
actually the way that interpreter or compiler does not care about function or not
it is just some instruction that kept to frequent use .. as u know the name of the function just refer to the location of these instruction .. and in compilin time just
it will be replace in main method or idk in php wat is it but let me say in the reference page that contains it .. so no error will rise as the interpreter or compiler will look at as one code (linkin) sono diffrence when u use subroutine or
just used it directly .

as in your case also i would say the failure in ur code is not in the test() function. and the evidence that it worked once (when u put it between L1 and L2)
hence ur assumption is not right.

as u move ur test() to anther Lx then failure happened ... then this not failure in
test() and hence as u said in mysql_fetch maybe the failure happened coz of test()
on the other line ( let just say that at the moment) but not in it.

so better suggestion let's try this :

$sql = "SELECT accounts.username FROM accounts WHERE (accounts.account_id = 4 OR accounts.account_id = 5)"; //LINE 1
///Here calling the function Test()works fine!!!
$result = mysql_query($sql); //LINE 2
////Here if I call the function Test(), it gives mysql error as below.
while($row = mysql_fetch_array($result)) //LINE 3
print_r($row);

i doubt u will get an error
i have tried it .. u should to tell me wat u get then
and also wat php version u use

sorry for this long post

Bakir, you did not provide anything new :) I already mentioned in my first post that using normal query (not stored procedure) works fine, I need Test() to work inside the stored procedure PHP scope. Thanks.

[pritaeas]

I tried this:

<?php
  mysql_connect('localhost', '', '');
  mysql_select_db('test');

  function Test() {
    $sql = "SELECT * FROM tickets WHERE TicketId = 1";
    $rs = mysql_query($sql);
    if ($row = mysql_fetch_array($rs))
      $mobile_number = $row['TicketNumber'];

    //mysql_close($rs); // Enabling this gives me a warning, though it shouldn't
    return $mobile_number;
  }

  $sql = "CALL GetAccounts()"; //LINE 1
  echo Test(); // echo number works
  $result = mysql_query($sql); //LINE 2
  echo Test(); // echo number works
  while($row = mysql_fetch_array($result)) //LINE 3
    print_r($row);
?>

The warning I get is on the fetch (LINE 3), because GetAccounts is a procedure and thus returns nothing. You can see it here.

[bakir]

Bakir, you did not provide anything new :) I already mentioned in my first post that using normal query (not stored procedure) works fine, I need Test() to work inside the stored procedure PHP scope. Thanks.

ok just tried to move the doubt from php that it .. and then if u got it ur self that
better

just hope u get solution for it
good luck

[Moderns]

I tried this:

<?php
  mysql_connect('localhost', '', '');
  mysql_select_db('test');

  function Test() {
    $sql = "SELECT * FROM tickets WHERE TicketId = 1";
    $rs = mysql_query($sql);
    if ($row = mysql_fetch_array($rs))
      $mobile_number = $row['TicketNumber'];

    //mysql_close($rs); // Enabling this gives me a warning, though it shouldn't
    return $mobile_number;
  }

  $sql = "CALL GetAccounts()"; //LINE 1
  echo Test(); // echo number works
  $result = mysql_query($sql); //LINE 2
  echo Test(); // echo number works
  while($row = mysql_fetch_array($result)) //LINE 3
    print_r($row);
?>

The warning I get is on the fetch (LINE 3), because GetAccounts is a procedure and thus returns nothing. You can see it here.

You used different table in the Test() function, but if you use same table (accounts table) which is used in the stored procedure, then you will get error mentioned in first post. I think it's Mysql bug.

[Moderns]

ok just tried to move the doubt from php that it .. and then if u got it ur self that
better

just hope u get solution for it
good luck

Thanks and it might be a bug in Mysql as it has some bugs.

[pritaeas]

Of course, I used the same table as in my stored procedure. I did not have your accounts table, so I used my tickets table in the php file and in the stored procedure.

[Moderns]

Of course, I used the same table as in my stored procedure. I did not have your accounts table, so I used my tickets table in the php file and in the stored procedure.

In your stored procedure you used GetAccounts() procedure, not GetTickets() :-)

[pritaeas]

Yes, I did not change the name, only the query... I doubt that would impact it.

[Moderns]

So what's the wrong of my code boss? What it does not work?

[pritaeas]

Really no clue... Using PHP 5.2.11 and MySql 5.0.91

[bakir]

HI Moderns
can u try the sql statement with

$sql = "CALL GetAccounts();;";

or

$sql = "CALL GetAccounts();";

try it and tell me wat u get

[pritaeas]

The semi-colon only makes a difference when you use a multi_query.

[bakir]

The semi-colon only makes a difference when you use a multi_query.

first i would say thanx for ur activity in the forums i really learned too much from u as i will call teacher

for ur reply i think he need that since he change the DILIMITER in the mysql to :: i always ignore that specially with phpmyadmin since i think it would apply itself but when u try that :: u wont get response from the mysql until u pass ;;
to it .. that was from my test
i did enter his procedure and when i make CALL GetAccount() got no response so
that why the $result will hold invalid argument to fetch method but when i did
CALL GetAccounts();; i got the response and the whole things worked correctly

Best Regards ma teacher :D