0

I decided to use the stored procedures in MySQL. I am able to retrieve only one row value in OUT paramater, but when the returned result contains more than one row, I get nothing!

Please see the simple code below for stored procedure which I add it to phpmyadmin direct:

DELIMITER ;;
DROP PROCEDURE IF EXISTS SelectContacts ;;

CREATE PROCEDURE SelectContacts(OUT mobileNumber VARCHAR(255))

BEGIN     
     SELECT accounts.mobile_number FROM accounts WHERE accounts.account_id = 1 OR accounts.account_id=2 INTO mobileNumber;
END ;;

/////////

Now to call the procedure from PHP:
/////////////////////////////////////////

$result = mysql_query("CALL SelectContacts(@mobileNumber)");
while($row = mysql_fetch_assoc($result))
     print_r($row); /// no output because the result has more than one value

Now: when the query returns more than one row like below, I get nothing

SELECT accounts.mobile_number FROM accounts WHERE accounts.account_id = 1 OR accounts.account_id=2 INTO mobileNumber;  #no output here#

But: when the query returns only one row like below, I get correct result

SELECT accounts.mobile_number FROM accounts WHERE accounts.account_id = 1 INTO mobileNumber;  #here I got output#

Can you please advise how to retrieve more than one value in OUT parameter using stored procedure? using normal query is working okey with me but unable to do that with stored procedure.

Mysql version is 5++, PHP 5

Please advise.

Thanks.

Edited by diafol: formatted code snips

5
Contributors
6
Replies
18
Views
6 Years
Discussion Span
Last Post by diafol
0

While fetching multiple records you should use array

try this

$result = mysql_query("CALL SelectContacts(@mobileNumber)");
while([B]$row[][/B] = mysql_fetch_assoc($result))
print_r($row);
0

While fetching multiple records you should use array

try this

$result = mysql_query("CALL SelectContacts(@mobileNumber)");
while([B]$row[][/B] = mysql_fetch_assoc($result))
print_r($row);

Thanks, I will try it when I go to home and will post the result here.

0
$rs = mysqli_query($conn,"Call GetProducts") or die(mysqli_error($conn)." Query=".$sql);
    $result = array();
    while($row = mysqli_fetch_array($rs)){
    array_push($result, $row);

    }
        echo json_encode($result);
0

I'd stick with mysql_* until it works. Worry about moving to mysqli_* or PDO later.

while($row = mysql_fetch_assoc($result))
{
    print_r($row);
}

That should be better than using $row[]
If you need to create an array of arrays, you can do soemthing like this with mysql:

$rows = array();
while($row = mysql_fetch_assoc($result))
{
    $rows[] = $row;
}
print_r($rows);
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.