How to create a stored procedure in mysql

Question

I have created the following stored procedure in mysql...

DELIMITER //
CREATE PROCEDURE GetMember(IN in_memberID int)
BEGIN
    SELECT *
    FROM Members
    WHERE MemberID = in_memberID;
END//



$result = mysql_query("CALL GetMember(".$memberID.")") or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
    echo $row['Name'] . "</br>";
}

But when I call it from php it returns all records in the Members table, what am I doing wrong?

EDIT: When I try to call the query within phpmyadmin I get this error

CALL GetMember(1);

#1312 - PROCEDURE myDb.GetMember can't return a result set in the given context

I agree with @ethrbunny. I think its confusing the procedure. — seanbreeden, Feb 09 '12 at 21:26
Can you call it from the mysql cmd line? Its looks pretty straight-forward. — ethrbunny, Feb 09 '12 at 21:44
can you check if $memberID is not null prior to calling the sp? — mdprotacio, Feb 09 '12 at 21:45
@MelvinProtacio I dont think that is the issue, I get the same result even If I pass in a constant — Eamonn McEvoy, Feb 09 '12 at 21:57
can you try changing `in_memberID` to `@in_memberID` and see if it works? — mdprotacio, Feb 09 '12 at 22:03
@MelvinProtacio I get an error if I try to use @ in front of in_memberID; — Eamonn McEvoy, Feb 10 '12 at 19:19
Very similar to http://stackoverflow.com/questions/2360371/mysql-stored-procedures-not-working-with-select-basic-question/2437024#2437024. The `1312` error may be related to http://stackoverflow.com/questions/2454071/how-do-i-write-an-sp-in-phpmyadmin-mysql/2498437#2498437 There is nothing "wrong" with your SP the way it is, but you may want to try changing the delimiter. Also, if you haven't already, verify that the MemberIDs are truly unique. If they aren't, you could get multiple results like you describe. — Farray, Feb 10 '12 at 19:40

score 0 · Answer 1 · answered Aug 07 '13 at 19:18

0

what version of PHP?

PHP 5.2.3 and PHP 5.2.4 have a bug with procedures: https://bugs.php.net/bug.php?id=42548

answered Aug 07 '13 at 19:18

Pedrão

301
1
8

score 0 · Answer 2 · edited Jan 12 '16 at 14:28

0

use Database_Name;

DELIMETER $$

DROP PROCEDURE IF EXISTS Proc$$
CREATE PROCEDURE Proc()
       BEGIN
               DECLARE x INT;
               SET x = 1;
               WHILE x <= 110000 DO
                   INSERT INTO Table_Name (word, mean) VALUES ('a', 'a mean');
                   SET x = x + 1;
               END WHILE;
       END$$

DELIMITER ;

edited Jan 12 '16 at 14:28

fantaghirocco

4,761
6
38
48

answered Jan 12 '16 at 13:04

DGVK

1
1

How to create a stored procedure in mysql

2 Answers2