Building a MySql Table
Almost all modules use forms and a DB in some way, so now I'm going to show how to create a simple form that has it's data inserted into the database (which I will refer to now as DB).
First open xoops_version.php and add the following:
<?php $modversion['name'] = "Tutorial"; // Admin // Menu $modversion['sqlfile']['mysql'] = "sql/mysql.sql"; |
The first line of the new code tells xoops where the sql file is located. The second tells it the table name.
It's good practice to start table count with 0. You should also preappend your module name to the tables, so that latter on, when using phpmyadmin you can easily locate and identify witch tables belong to witch module. This line tells xoops that this module willl create 1 table. This is however only part of the process, we still need to tell xoops what's inside the table, namely what fields and their properties. To do this lets create another folder called sql. Inside folder sql create a file called mysql.sql Open this file and place this code:
| CREATE TABLE tutorial_myform ( tt_id int(5) unsigned NOT NULL auto_increment, name varchar(30) NOT NULL default '', address varchar(30) NOT NULL default '', telephone varchar(30) NOT NULL default '', email varchar(30) NOT NULL default '', PRIMARY KEY (tt_id), KEY name (name) ) TYPE=MyISAM; |
The first line tells xoops to create a table called tutorial_myform. The first field called tt_id is a number field that allows a maximum of 5 digits ( int(5) ), unsigned means it doesn't acept negative values, NOT NULL means it can't be empty and auto_increment will add a number to each row, meaning if the previous row had tt_id=5 then the new row will have tt_id=6. Notice: This field is called tt_id and not id because id is a protected name of mysql, meaning you can't use it for your own purposes. The following 4 fields are similar. They define a field name (name, address, telephone and email), data type ( varchar(30) ) which in this case is a 30 character variable. To read more about this type and more check mysql.com . The next line defines a PRIMARY KEY. All tables should have a primary key defined. Normally I always set my primary keys to each tables id, in this case tt_id. When you have a lot of rows in a table, to increase speed you should index additional fields. Normally I index the ones which are used more often as reference, in this case "name" that's why I have: "KEY name (name)". Notice: the line before )TYPE=MyISAM; should NOT have a comma (,) . You should be carefull not to index too many fields as each new index increases the table size. Finally we define what type of table it is, I personally always use MyISAM but you can experiment with others. You can read more about this here and here.
Ok. We are ready for the next stage. In order for Xoops to create this table we need to do a full uninstall of the module. Then reinstall it. So let's do it.
1- Go into module administration and remove the check from active on the tutorial module. Click Submit. Confirm by clicking submit again.
2- Click back to module administration
3- Click on module uninstall icon which now shows in front of the tutorial module. Confirm by clicking yes. You will now notice a red error message saying that it was unable to drop table xxx_tutorial_myform. This is correct since we told xoops there was a table in xoops_version.
4- Click back to module administration.
5- Now install the module Tutorial.
We now have a xoops module with a table at our disposal! Let's continue.