How to Write a Stored Procedure in PHPmyadmin

How to write a stored procedure in phpMyAdmin?

In phpMyAdmin you can create the stored procedure in the SQL window.

You may have to set the delimieter to something like "$$" instead of the default ";". You can change this from the bottom of the SQL window.

Also you can refer this: http://www.mysqltutorial.org/mysql-stored-procedure-tutorial.aspx

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

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.

phpMyAdmin Stored Procedure Explanation

IS DETERMINISTIC:

A procedure or function is considered “deterministic” if it always produces the same result for the same input parameters, and “not deterministic” otherwise. If neither DETERMINISTIC nor NOT DETERMINISTIC is given in the routine definition, the default is NOT DETERMINISTIC.

SQL DATA ACCESS:

• CONTAINS SQL indicates that the routine does not contain statements that read or write data. This is the default if none of these characteristics is given explicitly. Examples of such statements are SET @x = 1 or DO RELEASE_LOCK('abc'), which execute but neither read nor write data.

• NO SQL indicates that the routine contains no SQL statements.

• READS SQL DATA indicates that the routine contains statements that read data (for example, SELECT), but not statements that write data.

• MODIFIES SQL DATA indicates that the routine contains statements that may write data (for example, INSERT or DELETE).

Stored Procedure in mysql phpmyadmin #1064

MySQL IF-clause uses THEN END IF instead of BEGIN and END.

You also might want to use temporary table for the IDBillInfoTable so it works with concurrent use.

IF (idSecondBill  IS NULL) THEN
  INSERT INTO bill (id_table, bill_maker, status) 
  VALUES (idTable2, 1, 0);

  SELECT MAX(id) into idSecondBill
  FROM bill 
  WHERE id_table = idTable2 AND status = 0;
END IF;

and temporary table creation:

CREATE TEMPORARY TABLE IDBillInfoTable AS 
SELECT id
FROM bill_info 
WHERE id_bill = idSecondBill;

and dropping the temp table

DROP TEMPORARY TABLE IDBillInfoTable;

Error while creating a procedure in phpmyadmin

Try using a DELIMITER statement:

DELIMITER $$

CREATE PROCEDURE insertData (
    in_Name varchar(255),
    in_Description text
)
BEGIN
    INSERT INTO categories(name, description)
        VALUES (in_Name, in_Description);
END;$$

DELIMITER ;

Notice that I also renamed in the input parameters so they are less likely to be confused with column names. This is a good practice when writing stored procedures and functions.

How to make a while in a stored procedure with phpmyadmin, error #1064

You should change that to below. See Documentation for more information.

open cur;

read_loop: LOOP
fetch cur into product_min_age, product_id;

    INSERT INTO _virtuemart_product_customfields (virtuemart_product_id, virtuemart_custom_id, customfield_value, customfield_params) VALUES
    ( product_id, 5, product_min_age, 'addEmpty=0|selectType=0|');

END LOOP;

close cur;

ERROR creating stored procedure phpmyadmin

As suggested by @nick, when creating a stored routines set delimiter other than default ;

delimiter $$

CREATE PROCEDURE login(usern varchar(255),pass varchar (255))
BEGIN                 

    SELECT * from usuario WHERE username = usern AND password = pass;

END $$

delimiter ;

---

Related Topics