MySQL Stored Procedures

Question

Hi,

I have a few questions regarding stored procedures. After hours of googling, I really can't find any really simple query examples.

CREATE PROCEDURE test1(IN uid INT)
BEGIN
    SELECT USER_NAME FROM USERS WHERE USER_ID = uid;
END //

this query takes 'uid' as input and selects the username for that uid, Can someone please explain how I call that procedure in php and also, how do I output the actual rows to the php var?

Answer 1

AFAIK you just need to put call before it in the query:

$result = mysql_query("CALL test1(".$args.")");
$username = mysql_result($result);

Answer 2

You should consider doing this with a stored function that returns a single VARCHAR:

CREATE FUNCTION test1(uid INT) RETURNS VARCHAR(100)
BEGIN
    DECLARE V_RETURN_VAL VARCHAR(100);

    SELECT USER_NAME 
    INTO V_RETURN_VAL
    FROM USERS 
    WHERE USER_ID = uid;

    RETURN V_RETURN_VAL;
END //