How to write mysql stored procedures with php5

Hello Everyone,

This is really long waiting features on mysql side as well php sides person. that is stored procedure things. I have almost tried all servers. oracle, mssql, msaccess, post gre, mysql. all of them having similar features. just few things are additional or remaining in some of databases.

One best loved things are Stored Procedures.

Q: Why we really need stored procedures ?

A: If you want create big web sites or net applications. all database need to be properly placed. There need to proper seperation of designing layer, application layer. and need to have database layer as seperate entity. I know you might thinking why we need that layer. as I have seen from many years that customer changes their servers as time progresses. and each hosting company giving different price packages. so customer choses their different server. so rather changing the whole application if we have things properly managed on our end then it will be easiest for customer to be independent on server selection.

And Runwalsoft.com is master in doing all code seperation. Stored procedure are pre-compiled sql statements. and basically business logic resides here. so most of the time it is required to change. so let say if some one wants to alter the logic. he will just open stored procedures and alter the changes according to customers need. this makes less disturbances in other code. and where ever you have used the similar function is automatically changed.

In Market generally use normal sql statements in php files. let say project is almost completed and customer have added features to enable or disable depending upon region, Then for this requirement let say we have added such statements in 40 files then we have to change that 40 php files. This takes time on programmer point of view. and generally programmer charge customer for his timing. Though he made wrong design. and he updated only 38 files and forgot to change 2 files. this creates website functioning extremly wrong. visitor of the web sites thinks that creator of the site is fool. or site is worst. and visitor never visit site again. this is lose for site owner.

Now Let say if programmer created database layer seperate. and in that layer if he used stored procedures then for every stored procedure they have used their call on 40 pages. so for changing 1 stored procedure is very small amount of time. inshort this way it will charge less to the customer. and Runwalsoft.com doing same things. we create the system in such a fashion that things are so seperated that it will be easily managable.

Q: As software purchased what they need ?

A : when they are purchasing any hosting company make sure they have mysql5.0 or above and php5.0 or above installed on their server. if their are lesser version then it will be problem in creating web site. what happen let say if you have lesser version. we have develope that layer (database layer) in traditional old way. so then further changes might cost you more. so sometime its quite better to purchase updated server.

Q: I am programmer how to incorporate stored procedures in php ?

A: I have seen that many people uses phpmyadmin as admin panel for it. so you can use that admin panel to create procedure on that only little difference is that let say if you click on “sql tab” at the bottom of it you will see the box called delimiter to changed “;” to // ( or something other than ; ) and write the following syntax for it.

create procedure showAddition(first decimal(10,2),second decimal(10,2))

begin
select (first + second) as addition;
end

This will create stord procedures. now its time to create page for php. in previous days generally programmer having habit to use mysql normal functions

mysql_connect , mysql_query , mysql_num_rows

But as mysql module doesn’t support stored procedures. you need to installed or need to tell your hosting company to activate mysqli [MySQL Improved Extension] module. this having features to access stored procedures , triggers and many advance fetures which mysql is provided. mysqli technically supports transactional and non transaction databases.

so coming to point.

PHP Code. I am giving you just short code how to access above procedure.

<?php
$mysqli = new MySQLi('localhost','root','root','test') or die("Unable to connect");

$a= 100 ;
$b = 20 ;
// This will fetch results but doesn't show results
$sql = "call showAddition('" . $a ." ','". $b . "');";
if ($mysqli->multi_query($sql)) {
    // This is for first Result ;
    $mysqli->next_result(); // # why this ? *A
    $rs = $mysqli->store_result();
    while($row = $rs->fetch_assoc())
    {
        print_r($row);
    }
}

?>

*A : for accession stored procedures generally mysql sends first response error code. and then it sends output which we have shown. so we have to bypass first resultset.

Another important point is that you need to access mysqli_multi_query function to get multiple results from the database. though there are many different methods. but for the start let have one function working. then I am sure you will become master on it.

Regards

Runwalsoft.com