MySQL Stored Procedure

What is a Stored Procedure?
A store procedure is a SQL block which performs one or more specific task. A procedure has a header and a body. The header consists of name of the procedure and the parameters passed to the procedure. The body consists of declaration section and execution section.

The syntax for creating a Stored Procedure is:

CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

proc_parameter:
[ IN | OUT | INOUT ] param_name type

type:
Any valid MySQL data type

characteristic:
COMMENT ‘string’
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }

routine_body:
Valid SQL routine statement

Note : The syntax within [] indicate they are optional.

Please refer following URL for the reference.

http://dev.mysql.com/doc/refman/5.6/en/create-procedure.html

Create the `employee` table in your database as follow.

CREATE TABLE IF NOT EXISTS `employee` (
`employee_id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(55) NOT NULL,
`last_name` varchar(55) NOT NULL,
`email_address` varchar(100) NOT NULL,
`location` varchar(55) NOT NULL,
PRIMARY KEY (`employee_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Now we will write the Stored Procedure `EmployeeActions` to insert and update the records from and to the database.

DELIMITER $$

CREATE PROCEDURE `EmployeeActions`(
IN paramAction TEXT,
IN paramEmployeeId INTEGER,
IN paramFirstName TEXT,
IN paramLastName TEXT,
IN paramEmailAddress TEXT,
IN paramLocation TEXT
)
BEGIN

DECLARE varAction TEXT ;

CASE paramAction
WHEN ‘New’ THEN
INSERT INTO
employee
(
`employee_id`,
`first_name`,
`last_name`,
`email_address`,
`location`
)
VALUES
(
NULL,
paramFirstName,
paramLastName,
paramEmailAddress,
paramLocation
);

WHEN ‘Update’ THEN
UPDATE
employee
SET
first_name = paramFirstName,
last_name = paramLastName,
email_address = paramEmailAddress,
location = paramLocation
WHERE
employee_id = paramEmployeeId;
END CASE;
END;

DELIMITER ;

Note: If you are using phpMyAdmin then please change the delimiter to $$.

Why Stored Procedure?

Stored Procedure are resides in the database and precomplied so any block of code can acess the procedure to insert and update the records. Since it is precomplied it will execute faster than the SQL query.

How to execute a Stored Procedure?

Now we are ready to insert and update the record to and from the database.

Insert :

call EmployeeActions(‘New’,”,’Tushar’,'Mahajan’,'tushar.iuselinux@gmail.com’,'Pune’);

Update :

call EmployeeActions(‘Update’,’1′,’Tushar’,'Mahajan’,'spiderphp@gmail.com’,'Pune’);

PHP – JSON – JQuery (Sweet Combination)

This is an article to show you an example of the how the JQuery (AJAX) works with PHP using JSON.

To work with this code you basically need a JQuery library which you will get from http://jquery.com/

First include the jquery.js file in your .htm file as follows

<script language=”javascript” type=”text/javascript” src=”jquery.js”>

We are using the JQuery(AJAX)  to fetch the records from server side using PHP in the form of JSON.

JSON stands for Javascript Object Notation. It is nothing but the way to write the data in some specific format so that you can access easily as follows.

var personObject = { “firstName” : “Tushar” , “lastName”  : “Mahajan” , “address” : { “city” : “Pune” , “country” : “India” }  } ;

Access firstName from personObject: personObject.firstName

Access city from personObject: personObject.address.city

Now Suppose you need to fetch the records from server side by using asynchronously (AJAX)  then it is necessary that they should come in some specific format like JSON.

PHP Code (get_person_details.php)  to create JSON object

<?php

$personId = $_POST['id'] ;

/* Here write the code for mysql to fetch the record form  database and create an array as follows */

$person = array( “firstName” => “Tushar” , “lastName” => “Mahajan” , “address” => array( “city” => “Pune” , “country” => “India”) );

echo json_encode($person);

?>

Now the following JQuery Code create an AJAX request.

$(document).ready(function(){
var url = “/person/get_person_details.php”;

$.post( url , { id : 12 } , function(data){

// the {data} is now a JSON object that is created by PHP Code.
alert(data.firstName + ” ” + data.lastName);
alert(data.address.city + ” ” + data.address.country );

}, JSON});
});

So by this way you can eaisly acess the data comming from PHP using JSON.

JQuery Event

This is a basic article which will help you, how to attach an events to the elements at runtime so that you can make a clean html code.

To work with this code you basically need a JQuery library which you will get from http://jquery.com/

First include the jquery.js file in your .htm file as follows

<script language=”javascript” type=”text/javascript” src=”jquery.js”></script>

I am goining to use following HTML code to explain how can we attach an event to the element.

<div>
<h2 class=”row”>
<a href=”#”>Tab 1</a>
</h2>
<h2 class=”row”>
<a href=”#”>Tab 2</a>
</h2>
<h2 class=”row”>
<a href=”#”>Tab 3</a>
</h2>
</div>

Now following JQuery code will attach an events to <a> tag

$(document).ready(function(){
$(“h2.row > a “).click(function () {
var innerHTML = $(this).html();
alert(innerHTML) ;
// or write your own code
});
});

In the above code $(document).ready(function(){ }); is similar to window.onload = function ( ) { } with a small difference that the $(document).ready(function(){ }); waits for document to load.

Once it is loaded then it will find the all
<h2><a href=”#”>bla bla bla</a></h2> combination using $(“h2.row > a “); the meaning of this statement is find all <a> tag which are under <h2> tag having class “row”

After finding such a element it will attach click event to it using $(“h2.row >  a “).click(function( ) { // place your code here });

So by this way you can attach events to the elements without writing
<a href=”#” onClick=”showText(this)”>bla bla bla</a>