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

Question

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?

wimvds · Accepted Answer · 2010-05-17T09:15:53.623

94

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.

edited May 17 '10 at 09:15

answered May 17 '10 at 09:01

wimvds

12,790
2
41
42

I can't believe I missed this... It works.. So Well.... Now can you guide me, a place to start learning stored procedures also... – Starx Nov 15 '10 at 10:08
I use PHP 2.8.2.4 - There is no the "Delimiter" field anywhere !! ?? – Vassilis Apr 21 '11 at 21:32
1

@VassilisGr: Then you might consider upgrading phpMyAdmin, it's certainly there in 2.11.8.1 ... – wimvds Apr 28 '11 at 11:17
when I call my stored procedure from phpmyadmin it does not return anything, when I execute it there in routines it works and also works when I call it from java, php and others. – Muhammad Mar 20 '15 at 07:19
Doesn't work on 3.4.10.1deb over "SQL" tab wirting directly to the textbox. It is mandatory put tags "delimiters ;;" and ";;" at begin/end (;; can be // also or anything I think) – Diego Andrés Díaz Espinoza Jul 14 '15 at 16:50
Doesn't work for me on 2.11.11.3 even though the Delimiter field is there on the SQL tab. – BadHorsie Sep 01 '15 at 14:26

score 60 · Answer 2 · edited Jun 20 '20 at 09:12

60

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.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jan 01 '14 at 14:41

TheTechGuy

16,560
16
115
136

3

In order to see that tab you must enable mysqli in the phpMyAdmin's config file. – wmac Apr 10 '14 at 06:55
I'd like to add that [this StackOverflow answer](https://stackoverflow.com/a/25988832/6761698) describes how to use a routine within phpmyadmin – Wouter Vanherck Jan 11 '19 at 08:50

score 25 · Answer 3 · edited Apr 29 '11 at 08:50

25

try this

delimiter ;;

drop procedure if exists test2;;

create procedure test2()

begin

select ‘Hello World’;

end

;;

edited Apr 29 '11 at 08:50

kapa

77,694
21
158
175

answered Apr 29 '11 at 08:32

Sourav

259
3
2

thanks for trying to help, but your answer is similar to that of @wimvds – Starx Apr 29 '11 at 10:40
For me also. In order for this to work I should set the delimiters. – Panos Kalatzantonakis Apr 21 '13 at 09:38
Vote this as it works on 3.4.10.1deb (under the tab "SQL") – Diego Andrés Díaz Espinoza Jul 14 '15 at 16:51
I personally think using `;;` as the delimiter is too similar to `;` used for the statements, so makes more sense to use a delimiter of `//` for clarity. – BadHorsie Sep 01 '15 at 14:20

score 1 · Answer 4 · answered May 11 '12 at 18:43

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.

Paul J · Answer 5 · 2012-06-27T07:29:43.347

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.

score 1 · Answer 6 · answered May 27 '13 at 06:59

1

delimiter ;;
CREATE PROCEDURE sp_number_example_records()
BEGIN
SELECT count(id) from customer; END
;;

answered May 27 '13 at 06:59

Jay Thakkar

699
1
8
13

score 1 · Answer 7 · answered May 17 '10 at 03:51

1

Try Toad for MySQL - its free and its great.

answered May 17 '10 at 03:51

ronaldosantana

5,112
4
22
28

I would like to try Toad.It looks promising. – kta Apr 03 '12 at 11:14

score 0 · Answer 8 · answered Nov 28 '13 at 16:34

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

Create a new PHPMyAdmin on the hosting provider. Attached link gives you idea http://internetbandaid.com/2009/04/05/install-phpmyadmin-on-1and1/
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

score 0 · Answer 9 · edited Apr 16 '20 at 22:52

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;

score 0 · Answer 10 · answered Oct 02 '17 at 16:49

/*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$$

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

But I dont know how to?

10 Answers10

Linked

Related