[php] 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?

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.

Add Routine

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.

The new version of phpMyAdmin (3.5.1) has much better support for stored procedures; including: editing, execution, exporting, PHP code creation, and some debugging.

Make sure you are using the mysqli extension in config.inc.php

$cfg['Servers'][$i]['extension'] = 'mysqli';

Open any database, you'll see a new tab at the top called Routines, then select Add Routine.

The first test I did produced the following error message:

MySQL said: #1558 - Column count of mysql.proc is wrong. Expected 20, found 16. Created with MySQL 50141, now running 50163. Please use mysql_upgrade to fix this error.

Ciuly's Blog provides a good solution, assuming you have command line access. Not sure how you would fix it if you don't.

try this

delimiter ;;

drop procedure if exists test2;;

create procedure test2()

begin

select ‘Hello World’;

end

;;

Try Toad for MySQL - its free and its great.

1. delimiter ;;
2. CREATE PROCEDURE sp_number_example_records()
3. BEGIN
   SELECT count(id) from customer; END
4. ;;

/*what is wrong with the following?*/
DELIMITER $$

CREATE PROCEDURE GetStatusDescr(
    in  pStatus char(1), 
    out pStatusDescr  char(10))
BEGIN 
    IF (pStatus == 'Y THEN
 SET pStatusDescr = 'Active';
    ELSEIF (pStatus == 'N') THEN
        SET pStatusDescr = 'In-Active';
    ELSE
        SET pStatusDescr = 'Unknown';
    END IF;

END$$

All the answers above are making certain assumptions. There are basic problems in 1and1 and certain other hosting providers are using phpMyAdmin versions which are very old and have an issue that defined delimiter is ; and there is no way to change it from phpMyAdmin. Following are the ways

1. Create a new PHPMyAdmin on the hosting provider. Attached link gives you idea http://internetbandaid.com/2009/04/05/install-phpmyadmin-on-1and1/
2. Go thru the complicated route of be able to access the your hosting providers mySQL from your dekstop. It is complicated but the best if you are a serious developer. here is a link to do it http://franklinstrube.com/blog/remote-mysql-administration-for-1and1/

Hope this helps

On local server your following query will work

DELIMITER |

CREATE PROCEDURE sample_sp_with_params (IN empId INT UNSIGNED, OUT oldName VARCHAR(20), INOUT newName VARCHAR(20))

BEGIN

SELECT `first name` into oldName FROM emp where id = empId;

UPDATE emp SET `first name`= newName where id = empId;

END

|

DELIMITER ;

but on production server it might not work. depend on mysql version you are using. I had a same problem on powweb server, i removed delimiter and begin keywords, it works fine. have a look at following query

CREATE PROCEDURE adminsections( IN adminId INT UNSIGNED ) SELECT tbl_adminusersection.ads_name, tbl_adminusersection.ads_controller FROM tbl_adminusersectionright LEFT JOIN tbl_adminusersection ON ( tbl_adminusersectionright.adsr_ads_id = tbl_adminusersection.ads_id ) LEFT JOIN tbl_adminusers ON ( tbl_adminusersectionright.adsr_adusr_id = tbl_adminusers.admusr_id ) WHERE tbl_adminusers.admusr_id = adminId;

I got it to work in phpAdmin , but only when I removed the "Number of records " phrase.

In my version of phpAdmin I could see the box for changing the delimiters.

Also to see the procedure in the database i went to the phpAdmin home, then information_schema database and then the routines table.