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.

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);

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.

Using $row[] is not working. Any advice?

use mysqli->query("call the storedprocedure")

$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);
Member Avatar for diafol

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);
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.