Tag Archives: mysql5.0

Steps for converting mysql to mysqli

18 Jan , 2009,
, ,
No Comments

As mysql and mysqli both belongs to same mysql server. mysql and mysqli as just modules provided with php.

when we started working on php and mysql then only mysql module was provided and different hosting company even not updated their mysql for latest version. so specifically we had to use the old technique to access these things. now many things are changing. People at the server company realize that they need to keep updated servers then only they will get more customer for their hostings. and now a days even customer are already known abt pros and cons of databases.

Now things begins here. now you have old traditional web site which was created using old techniques and you want to make things more optimised and go with new cutting edge technologies. so there few things which you really need to keep in mind that now a days different layers are used for web sites. its not as easy as what traditional methods. now on every front peoples are getting more advance. just for the sake of example. now for handling database there are dedicated peoples who knows clustering , memorycache ing for mysql. and in designing now advance flash version details are used with greate features. some templating used to keep site flexible to modify. and here we(Runwalsoft) have made all front details advance. each of our designer, programmer, database handlers are advance. they know how to tacles the hits. and how to mange more hits without losing their customers.

just for the sake of programmer. let say if you have used to open database connection you might have used.

$cn = mysql_connect('localhost','root','password') or die ('Unable to connect' . mysql_error());

which now you have to change like this.

$cn = new MySQLi('localhost','root','password','DatabaseName') or die("Unable to connect");

in the similar fashion you have to change queries as well previously we was using

$rs = mysql_query($sql,$cn) or die("Unable to find sql statement<br>" . mysql_error());

which is now

$rs = $mysqli->query($cn, $sql) or die("Unable to find sql statement <br>" . $mysqli->error);

I know that this is very very simple things but sometime people forgot how to use these. so I am writting this for newbie. mysqli means that mysql improved. and it having my good features along with it. I will update few more lines after some time. lets have start atleast. 🙂

How to write mysql stored procedures with php5

19 Dec , 2008,
, , , ,

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))

select (first + second) as addition;

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.

$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())


*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.



Albumhunt’s Control Panel

18 Dec , 2008,
, , , , ,
one comments

Recently we have completed albumhunt’s work. I am really glad to giving him quality work. and this work in created in smarty. customer is so pleased with efforts. even I have seen lots of his customer feed back on shout box [ one module on site] on albumhunt.com.

As this is Cristmas month. and I also feel that I will offer some gift to him. as physically its not possible for me to send gift to him as it might be costlier for me. so I have given gift to him for animated santa in javascript. seriously this is first time we also made site like 3D look. our designer Raj M Jadhav created his high talented efforts in created designs for not only this site but also for many other sites as well.

Even we have created email templates for that site. i wish you visit the sites and enjoy the looks.

This site having almost 30,000 visitors every day. so we have created different layers for each sections. at the same time we have delivered high query optimization. previously page was aroun 360k. now whole page is around 60k. its all because of Runwalsoft always works on cutting edge technologies. site is created with keeping in mind of table less structures.