How to write a stored procedure using phpmyadmin and how to use it through php?

I want to be able create stored procedures using phpMyAdmin and later on use it through php.

But I dont know how to?

From what I know, I found out that we cannot manage stored procedures through phpMyAdmin.

What other tool can manage stored procedure?

I am not even sure if it is better option to use stored procedure through PHP. Any suggestion?

Solution 1:

Since a stored procedure is created, altered and dropped using queries you actually CAN manage them using phpMyAdmin.

To create a stored procedure, you can use the following (change as necessary) :

CREATE PROCEDURE sp_test()
BEGIN
  SELECT 'Number of records: ', count(*) from test;
END//

And make sure you set the "Delimiter" field on the SQL tab to //.

Once you created the stored procedure it will appear in the Routines fieldset below your tables (in the Structure tab), and you can easily change/drop it.

To use the stored procedure from PHP you have to execute a CALL query, just like you would do in plain SQL.

Solution 2:

I guess no one mentioned this so I will write it here. In phpMyAdmin 4.x, there is "Add Routine" link under "Routines" tab at the top row. This link opens a popup dialog where you can write your Stored procedure without worrying about delimiter or template.

enter image description here

Add Routine

enter image description here

Note that for simple test stored procedure, you may want to drop the default parameter which is already given or you can simply set it with a value.

Solution 3:

try this

delimiter ;;

drop procedure if exists test2;;

create procedure test2()

begin

select ‘Hello World’;

end

;;

Solution 4:

Try Toad for MySQL - its free and its great.