• Skip to main content
  • Skip to primary sidebar
  • Skip to footer
  • Home
  • Featured
    • C# Tutorials
      • LinQ Tutorials
      • Facebook C# API Tutorials
    • PHP Tutorials
      • CodeIgniter Tutorials
    • Amazon AWS Tutorials
  • Categories
    • Programming
    • Development
    • Database
    • Web Server
    • Source Control
    • Management
    • Project
  • About
  • Write
  • Contact

CodeSamplez.com

Programming, Web development, Cloud Technologies

You are here: Home / Database / Beginning With MySQL Stored Procedure Programming

Beginning With MySQL Stored Procedure Programming

December 13, 2010 by Rana Ahsan 4 Comments

MySQL Stored Procedures Programming

While doing database programming, we mainly focus on SQL statements that do ‘select’,’insert’,’update’,’delete’ operations with some conditions ‘where’,’like’ and some orders ‘group by’, ‘order by’. For a little complex goal, use join operations. MYSQL database is a very popular name in web applications development, specially for small and medium scale applications. Moreover, PHP has a good support in connecting/executing queries, fetch results. But, until MYSQL 5 releases, it didn’t support stored procedures , which is one of the major important features in RDBMS. But, as traditionally as developers already used raw query writing and execute them from PHP, still today, most of them are using same ways, although their server is upgraded to >= mysql 5. However ,we should use it for our own benefits if our server supports. In this tutorial, I will try to give a very basic idea to start using MySQL stored procedure and simple code samples also.

What is STORED PROCEDURE:

As its name imply, it is one kind of procedure/method/function that is stored within the database systems and contains a set of sql statements, which can be executed consequently one after another in a single call.

Creating Stored Procedure In PHPMyAdmin

Most of the developers use MySQL database on web applications and in that sense, PHPMyAdmin is their best database management tools. To create a stored procedure in PHPMyAdmin is quite easy. Simply go to the ‘query’ tab. Write the following simple statements:

DELIMITER //
CREATE PROCEDURE my_procedure                /* Procedure Name*/
(IN id INTEGER)                    /* parameters passed to the procedure */
BEGIN                                      /* start body of the procedure*/
  DECLARE name CHAR(10);                /* A simple variable those can be used in  stored procedure */
  IF id%2=0 THEN                    /* Simple conditional block */
    SET name = 'even';                   /* assign statement */
  ELSE
    SET name = 'odd';
  END IF;
  INSERT INTO tbl_numbers VALUES (id,name);    /*  sql insert statement */
END                                       /* end of procedure body */
// DELIMITER ;

Now, if you simply run these by clicking ‘go’, it will execute and create an stored procedure on your database. Remember to use the delimiter part. This is important, helps converting ‘;’ to ‘//’ so that multiple SQL statements can be executed. This will be shown as ‘Routines’ under the structure tab on database root page(where no table is selected). To edit/view the query of the procedure , click ‘structure’ column link next to the routine name.

To execute these procedures are also quite easy. Here are simple PHP code samples to execute the above mentioned stored procedure:

$id = 1;
$result = mysql_query('call my_procedure($id)');

So, you can easily assuming, how it can reduce complexity of writing raw SQL inside your server script and gives more flexibility in re-usability. also, if you are working on an existing PHP project that utilizes stored procedure, you won’t have to find which server script functions is doing that kind of works. You can simply find them by checking the routines on PHPMyAdmin.

Also just be careful about the following terms in using stored procedures:

  • Use a meaningful name of the procedure which indicates the internal functionality transparently so others can understand just by checking the procedure name.
  • Use proper parameter types and careful they are called.
  • It is not possible to debug  MySQL stored procedures(as far i know, applies to most of other DBMS also, special case: there is debugger for SQL server on the recent 2008 version), so you have to write procedures in a clean way to be easily adaptable so that others can catch an potential error/enhancement points easily.

I have also found some good tutorials on various parts of MySQL stored procedures on MySQL tutorials website. You can go through them also for more detailed and deep knowledge. Besides, don’t forget to follow the database best practices as well. You can also ask if you have any, here by commenting. happy coding 🙂 .

Share If Liked

  • Click to share on Facebook (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
  • Click to share on Reddit (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • Click to share on Pocket (Opens in new window)

You may also like

Filed Under: Database, Programming Tagged With: mysql, phpmyadmin

About Rana Ahsan

Rana is a passionate software engineer/Technology Enthusiast.
Github: ranacseruet

Reader Interactions

Comments

  1. programarivm says

    January 21, 2012 at 8:36 am

    Hi, excellent article. I attach a post (the post is in Spanish, and the MySQL script’s in Catalan) that explains how to implement a virtual store with MySQL stored procedures. There’s a MySQL API there for typical CRUD operations. Greetings to all!

    http://programarivm.com/2012/01/comercio-electronico-de-la-uoc-base-de-datos-de-una-tienda-virtual-con-stored-procedures-y-stored-functions-de-mysql/

    Reply
  2. ankursharp says

    February 20, 2013 at 7:22 pm

    HI, wery helpful post. Also for extended clarification I got some reference from http://www.techflirt.com/mysql-stored-procedure-tutorial/ . It was great pleasure to see sp in mysql as well.

    Reply
  3. Abir Sur says

    April 6, 2015 at 3:40 am

    Hi excellent post, I have a question, how do I get “out parameter” ‘s data of a Stored Procedure in PHP codeigniter. I use PDO and get success. but as I am basically a asp.net programmer So I cant understand ” Using PDO is good for Codeigniter Framework or Not ?? “. Please help.. Thanks in advance.

    Reply
  4. Luis says

    November 4, 2016 at 11:05 am

    I’m Sorry, i cant use SP in php codeigniter, i’m using driver mysql and php 5.
    i can see error database then i call my SP, please help me!

    Reply

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 3,774 other subscribers

Follow Us

  • Twitter
  • Facebook

Top Posts & Pages

  • How To Work With JSON In Node.js / JavaScript
    How To Work With JSON In Node.js / JavaScript
  • PHP HTML5 Video Streaming Tutorial
    PHP HTML5 Video Streaming Tutorial
  • How To Work With C# Serial Port Communication
    How To Work With C# Serial Port Communication
  • Facebook C# API Tutorials
    Facebook C# API Tutorials
  • LinQ Query With Like Operator
    LinQ Query With Like Operator
  • Using Supervisord Web Interface And Plugin
    Using Supervisord Web Interface And Plugin
  • Get Facebook C# Api Access Token
    Get Facebook C# Api Access Token
  • Getting Started With UDP Programming in Java
    Getting Started With UDP Programming in Java
  • Utilizing Config File In C#.NET Application
    Utilizing Config File In C#.NET Application
  • Generate HTTP Requests using c#
    Generate HTTP Requests using c#

Recent Posts

  • Building Auth With JWT – Part 2
  • Building Auth With JWT – Part 1
  • Document Your REST API Like A Pro
  • Understanding Golang Error Handling
  • Web Application Case Studies You Must Read

Tags

.net angularjs apache api audio auth authenticatin aws c# cloud server codeigniter deployment docker doctrine facebook git github golang htaccess html5 http javascript jwt linq mysql nodejs oop performance php phpmyadmin plugin process python regular expression scalability server smarty socket.io tfs tips unit-test utility web application wordpress wpf

Footer

Archives

Follow Us

  • Twitter
  • Facebook

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 3,774 other subscribers

Copyright © 2023