CodeSamplez.com

Programming, Web development, Cloud Technologies

  • Facebook
  • Google+
  • RSS
  • Twitter
  • 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
Home Database Beginning With MySQL Stored Procedure Programming

Beginning With MySQL Stored Procedure Programming

Rana Ahsan December 13, 2010 4 Comments


 Beginning With MySQL Stored Procedure 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 🙂 .

Related

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

About Rana Ahsan

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

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.

Email Subscription

Never miss any programming tutorial again.

Popular Tutorials

  • How To Work With JSON In Node.js / JavaScript
  • PHP HTML5 Video Streaming Tutorial
  • Utilizing Config File In C#.NET Application
  • LinQ Query With Like Operator
  • How To Work With C# Serial Port Communication
  • Facebook C# API Tutorials
  • Few Important Basic Git Commands Examples
  • How To Use Hotkeys/Keyboard Events In WPF Application Using C#
  • Getting Started With Smarty Template Engine
  • Using Supervisord Web Interface And Plugin

Recent Tutorials

  • Building Auth With JWT – Part 1
  • Document Your REST API Like A Pro
  • Understanding Golang Error Handling
  • Web Application Case Studies You Must Read
  • Getting Started With Golang Unit Testing
  • Getting Started With Big Data Analytics Pipeline
  • NodeJS Tips And Tricks For Beginners
  • Apple Push Notification Backend In NodeJS
  • Web Based Universal Language Translator, Voice/Text Messaging App
  • How To Dockerize A Multi-Container App From Scratch

Recent Comments

  • S. Chalisque on PHP HTML5 Video Streaming Tutorial
  • Armorik on Generate HTTP Requests using c#
  • iswaps on PHP HTML5 Video Streaming Tutorial
  • TAKONDWA on PHP HTML5 Video Streaming Tutorial
  • rorenzo on PHP HTML5 Video Streaming Tutorial

Archives

Resources

  • CodeSamplez.com Demo

Tags

.net apache api audio aws c# cache cloud server codeigniter deployment doctrine facebook git github golang htaccess html5 http image java javascript linq mysql nodejs oop performance php phpmyadmin plugin process python regular expression scalability server smarty ssh tfs thread tips ubuntu unit-test utility web application wordpress wpf

Copyright © 2010 - 2022 · CodeSamplez.com ·

Copyright © 2022 · Streamline Pro Theme on Genesis Framework · WordPress · Log in