Store procedures in phpMyAdmin

Question

I must add stored procedures to MySQL database.

The problem is that the hosting offers phpMyAdmin to manage the database.

I searched on the Internet and the idea is to run the MySQL native statement that creates the procedures, but as the code of procedures often may have ;, we have to change the delimiter in MySQL.

phpMyAdmin does not have this option. Has anyone tried to create stored procedures manually setting the anchor, or something that worked?

Answer 1

There is a way, see this link: http://blog.nth-design.com/2009/02/25/creating-sp-in-phpmyadmin/

Quote from that link
1.Open phpMyadmin.
2.Select a database to work with.
3.Open the SQL tab.
4.Select all of the SQL statements between the DELIMITER statements in your stored procedure script. Do not include the DELIMITER statements! Here’s what my example script should look like:

DROP PROCEDURE IF EXISTS spFoo $$
CREATE PROCEDURE spFoo ()
BEGIN
    SELECT 'Foo' FROM DUAL;
END $$

5.In the delimiter field, just below the SQL editor’s text area, enter $$ as your delimiter.

Answer 2

Why everybody tries to use a visual tool as console?!? There is an easyer way:

Go to your database, and look for "More"

Then this screen will show. If you have created any stored procedures, they will be listed

To add new one click "Add routine"

Answer 3

I had trouble using the 'Routines' feature in PHPMyadmin cos it kept giving me false negatives so i did it via the 'SQL' tab instead.

CREATE PROCEDURE GetUserPwd(email VARCHAR(320), pass VARCHAR(128))
BEGIN
DECLARE userid INT(3) DEFAULT 0;
DECLARE password_equal INT(3) DEFAULT 0;
DECLARE output VARCHAR(30);
SELECT id INTO userid FROM members WHERE user_email = email;
IF userid != 0 THEN
SELECT user_pass = pass INTO password_equal FROM members WHERE id = userid;
IF password_equal = 0 THEN
SET output = 'not exist';
ELSE
SET output = 'exist';
END IF;
END IF;
SELECT output;
END

In the 'Delimiter' text box, type in '$$'. save.

after that , go to your 'Routines' tab and click on 'execute' and enter your inputs when prompted.