Create a MySQL Table with PHP

One thing I often asked myself, when I first started programming in PHP, was How do I create a MySQL database table with PHP? Of course, I have come a long way since then, and creating a MySQL table from within a PHP script is no longer a mystery to me.

However, browsing through a few popular PHP forums these past few days, I quickly realised that there are still a lot of webmasters who need to know how to get this done, and so I got to writing, as I usually do…

Assume you wanted to create an install page for your latest PHP project. One of the main things you would do in an PHP install page or script is to make PHP create the (MySQL) database tables needed for the rest of your script to work.

Create sample table: ZONES

If I had to create a MySQL table named `zones` in my script using PHP, this is what I’d do:

// Connect to our database
// ----------------------------------
$conn = dbconnect();

$sql = 'CREATE TABLE `zones` (
 `zid` TINYINT( 3 ) UNSIGNED NOT NULL AUTO_INCREMENT,
 `zdesc` VARCHAR( 150 ) NOT NULL,
 `zwidth` SMALLINT( 3 ) DEFAULT 0 NOT NULL,
 `zheight` SMALLINT( 3 ) DEFAULT 0 NOT NULL,
 `zmax` MEDIUMINT(7) unsigned NOT NULL default 12288,
 PRIMARY KEY ( `zid` )
 )';
echo 'Creating table: `zones`....';
mysql_query( $sql, $conn );

That’s all you have to do… and you can even do more than one database table creation in this one script / page, which is what I will show you below. Also, it’s good idea to extend this script by checking if a table (name) being created is already existing in the database!

DROP before CREATE example

In this example, I will create 2 tables, `zones` and `bcategory` (dropping or deleting any already existing table in the sample database first).

// Connect to our database
// ----------------------------------
$conn = dbconnect();

$sql = 'DROP TABLE IF EXISTS `zones`';
mysql_query( $sql, $conn );

$sql = 'CREATE TABLE `zones` (
 `zid` TINYINT( 3 ) UNSIGNED NOT NULL AUTO_INCREMENT,
 `zdesc` VARCHAR( 150 ) NOT NULL,
 `zwidth` SMALLINT( 3 ) DEFAULT 0 NOT NULL,
 `zheight` SMALLINT( 3 ) DEFAULT 0 NOT NULL,
 `zmax` MEDIUMINT(7) unsigned NOT NULL default 12288,
 PRIMARY KEY ( `zid` )
 )';
echo 'Creating table: `zones`....';
mysql_query( $sql, $conn );

$sql = 'DROP TABLE IF EXISTS `bcategory`';
mysql_query( $sql, $conn );

$sql = 'CREATE TABLE `bcategory` (
 `bid` int(10) NOT NULL default 0,
 `cid` int(10) NOT NULL default 0,
 PRIMARY KEY ( `bid`, `cid` )
 )';
echo 'Creating table: `bcategory`....';
mysql_query( $sql, $conn );

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>