Tag Archives: mysql

Working with mysql foreign keys.

5 Feb , 2010,
Administrator
, ,
No Comments

We have seen that most of the time people write many lines of code. Might be the reason behind is that they either don’t know the capabilities of the each and every modules. Out of that different module one module is mysql.

As most of the websites having some kind of relational database. where their is one master table. and their keys are used in other tables. But if the programmer don’t know about the foreign keys then they write the code at the time of deleting the record e.g

Lets imagine that We have 2 tables

  1. Books
  2. Purchasers

people write following syntax.

create table books ( id smallint primary key auto_increment , name varchar(100));
create table purchasers (id smallint primary key auto_increment , bookid smallint , pur_name varchar(100));

While creating this, many programmer forgot major things. as they are creating these tables with storage engine as myISAM. but they forgot all transactional details need to be saved in InnoDB. and current released version’s of mysql support foreign key for only innodb. and Now php is being concern they write code for 2 sql statement for deleting books and their purchasers. this is really bullship.

Now We have seen that when we got the work for existing websites. we seen similar things in many cases( almost all the cases). this really increases lots of efforts in programming as well as maintaining the codes. For owner of websites , they don’t bother(rather they don’t know) what other/older programmers written on it. this increases our efforts even more. And now our database designer taking meetings with programmer and explaining the things was older database. so programmer have to find excessive work written on the code. and replace with just simple modification made by our DB designer.

First Important thing needed is to first convert required tables into innodb

alter table books engine = 'innodb'; 
alter table purchasers engine = 'innodb' ;

Then Adding of foreign keys.

alter table purchasers add foreign key (bookid) references books(id) on delete cascade on update cascade ;

Doing This will add foreign key so that when we delete any book it will delete all their respective references given on purchasers table. which can be achieved with only single sql execute in php see the following line.

delete from books where id = _NUMBER_

some time we have seen that we have to drop foreign keys. and mysql manual says that use the following syntax.

alter table tableName drop foreign key keyName ;

But now the problem is that how to get that key name. Then you need to use following way.

show create table tableName;

Doing this you will get name of foreign key. then delete the foreign key and index key. Please note that when you want to add foreign key to any table try to make foreign key symbol name by your own rather than automated by mysql. This will easier for you manage and maintaining the all the keys. every foreign key comes with 2 kays one foreign key and one index key. if you just delete only foreign key then it will not be good way. as you are keeping garbage of index kay.

How to write mysql stored procedures with php5

19 Dec , 2008,
Administrator
, , , ,
2 comments

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