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

User · Answer

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

User · Answer

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

User · Answer

Try Toad for MySQL - its free and its great

User · Answer

I guess no one mentioned this so I will write it here  In phpMyAdmin 4 x  there is  quot Add Routine quot  link under  quot Routines quot  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

User · Answer

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

User · Answer

try this  delimiter     drop procedure if exists test2    create procedure test2    begin  select    Hello World      end

User · Answer

delimiter    CREATE PROCEDURE sp number example records   BEGIN SELECT  count id  from customer  END

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to php

Examples related to stored-procedures

Examples related to mysql

Examples related to phpmyadmin