|
*dhtml
|
Iight guys. This is supposed to be an article, but questions can be asked though. Minimal requirement for this thread is that, you MUST be able to create mysql databases and integrate them with php. It is actually a thread meant for advanced users but i will entertain any questions asked relating to it no matter how foolish the question is. For starters you may want to read this foreign article - http://www.mikebernat.com/blog/Setting-Up_a_Relational_Database_in_MySQLSo to continue with the intro. I am just doing a repeat broad-cast in 9ja language. Many of you developers just create database and just carry go like that, and dont even care whether the database type is MyIsam or InnoDb. If you are one of this, please read on rather than get annoyed with me. Yes, you can say database is database, but if you care about efficiency like this. If i create the database of nairaland, i can wipe off all the topics and posts i have ever created on nairaland using a one line common command like this: delete from users where username=dhtml - familiar command huh? So, i am not here to teach you basic SQL codes but rather to give you some insight about creating clean and efficient databases that will reduce your php codes greatly - think about it as a smarter way to lay out your databases. |
|
|
|
|
|
*dhtml
|
There are what we call storage engines in mysql. They are rather like plug-ins that do the actual storage of your tables. The common types of storage engines are as follow: -> MyISAM -> InnoDB -> Merge -> Heap -> Memory -> BDB -> ISAM Please dont be bored 'cos i know some of you may be wondering WTF i am talking about, while some are just impatient to find codes to copy - codes will come eventually but explanation first so that you will know how to use the codes. Codes are not the aim of this thread - but rather table creation and optimization. So MyISAM and InnoDb are the most commonly used storage engines - read all about it - http://www.mikebernat.com/blog/MySQL_-_InnoDB_vs_MyISAM |
|
|
|
|
|
*dhtml
|
Now our relational database at last using InnoDb. So here is how i go about it. Create a dbase - classDemo Create a table called users. fields: id - autoincrement primary key username - varchar(255) users.sql CREATE TABLE `users` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR( 255 ) NOT NULL
) TYPE = MYISAM ;
Then you will now go to operations on phpmyadmin, that place we usually rename database and all that. You will now find the option to change your table to innodb. The sql code directly will be CREATE TABLE `users` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=InnoDB AUTO_INCREMENT=1 ;
So next table will now be the profile table. Notice that while creating tables in phpmyadmin it will usually give you the option to change the dbase storage engine. Default is usually MyISAM. profile.sql CREATE TABLE `profile` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`user_id` INT NOT NULL ,
`comment` TEXT NOT NULL
) TYPE = INNODB COMMENT = 'profile';
So here is what i have done so far. |
|
|
|
|
|
*dhtml
|
Now the tables have been created, let us now make them relational. Explanation: id on users is autoincremented, so let us now register the following users: omnipotens, yawatide users.sql CREATE TABLE `users` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=InnoDB AUTO_INCREMENT=3 ;
INSERT INTO `users` VALUES (1, 'omnipotens');
INSERT INTO `users` VALUES (2, 'yawatide');
So the id of omni is 1 while yawa is 2. This ids are unique too. So i now want to tie this ids to the profile table in a relationship model. So, i now go back to the profile table and click on user_id and select the option to make it an indexed field. Image attached below. Then you will now click on relation view (this is never available in MyISAM) - so dont be bothered that you have never seen it before. Let us see some screenshots before we continue. |
|
|
|
|
|
*dhtml
|
Now go to the relation view and link the user_id of profiles to the id of users table.
Dont be confused, follow the screenshot below. Then we continue from there.
|
|
|
|
|
|
*dhtml
|
Now lemme explain what we have done in essence: 1. You cannot add an entry to the profile table without specifyin the user_id and the user_id must exist in the users table too as pid. 2. When you delete a particular user from the users table, all the comments that carry his user_id will be deleted at once. If you are confused, please forget about that and continue reading. We are talking practicals as alwayz. profile.sql CREATE TABLE `profile` (
`id` int(11) NOT NULL auto_increment,
`user_id` int(11) NOT NULL default '0',
`comment` text NOT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`)
) TYPE=InnoDB COMMENT='profile' AUTO_INCREMENT=1 ;
--
-- Dumping data for table `profile`
--
--
-- Constraints for dumped tables
--
--
-- Constraints for table `profile`
--
ALTER TABLE `profile`
ADD CONSTRAINT `0_78` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
So let us now insert records into the profile table. Notice i entered the user_id of omnipotens in the screenshot. If you specify the user_id that does not match any users.id that query will be rejected, you can try it with a value of 0 instead of 1 or 2, valid.php $sql = 'INSERT INTO `profile` (`id`, `user_id`, `comment`) VALUES (NULL, \'1\', \'Hi Y\'\'all, i am omnipotens, i got here first.\');';
mysql_query("$sql");
invalid.php $sql = 'INSERT INTO `profile` (`id`, `user_id`, `comment`) VALUES (NULL, \'0\', \'Hi Y\'\'all, i am omnipotens, i got here first.\');';
mysql_query("$sql");
|
|
|
|
|
|
*dhtml
|
INSERT INTO `profile` (`id`, `user_id`, `comment`) VALUES (NULL, \'1\', \'Hi Y\'\'all, i am omnipotens, i got here first.\') full profile table will be CREATE TABLE `profile` (
`id` int(11) NOT NULL auto_increment,
`user_id` int(11) NOT NULL default '0',
`comment` text NOT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`)
) TYPE=InnoDB COMMENT='profile' AUTO_INCREMENT=7 ;
--
-- Dumping data for table `profile`
--
INSERT INTO `profile` VALUES (1, 1, 'Hi Y''all, i am omnipotens, i got here first.');
INSERT INTO `profile` VALUES (2, 1, 'Hi Y''all, i wish you happy sql coding.');
INSERT INTO `profile` VALUES (3, 1, 'Time to go. Yawa will address y''all later');
INSERT INTO `profile` VALUES (4, 2, 'Hi I am yawatide. DHTML na my friend o.');
INSERT INTO `profile` VALUES (5, 2, 'You guyz had beta start answering my quiz else, ');
INSERT INTO `profile` VALUES (6, 2, 'Gotta go now. Hve a bizzy day ahead of me.');
-- |
|
|
|
|
|
*dhtml
|
Notice that the user_id contains only 1 and 2. Specifyin the required users,
So to delete all the entries of omnipotens. All you need do is:
delete from users where id='1';
or whatever. Once that row is deleted, all the user_id that have same
value as users.id will be deleted automatically.
That method of linking the profiles with the users table can be employed to
tie more tables together. This is one of the powers of innodb aside
the row locking stuff.
And this is the end of my article, good night folks. Gotta get back to work.
|
|
|
|
|
|
Seun (m)
|
Thanks for the tutorial.
|
|
|
|
|
|
*dhtml
|
Hi chief, you are welcome as alwayz. . .
|
|
|
|
|
|
segsalerty (m)
|
nice thread , didnt know this exists before,
bt , cool
thanks as always DHTML
|
|
|
|
|
|
smartsoft (m)
|
OMO i don 4get all dis thing ooooooooooooooooooooo colours don take ova me finish
|
|
|
|
|
|
*dhtml
|
OMO i don 4get all dis thing ooooooooooooooooooooo colours don take ova me finish
Just lyke codes don scatter my head finish! @segsalert, i have lost track of my useful threads on webmasters sef, i will try rearrange then again someday. Thanks to omnipotens for helping me bookmark some of them here, and i tried to link some of them together too. . . |
|
|
|
|
|
![Nairaland [Nigerian Forum] Home](http://www.nairaland.com/Themes/default/images/english/home.gif)
Author
db1.gif (43.78 KB, 760x728 )
