mkaz.com home photography web dev personal about

Web DB Tutorial : Create Database

We know what data we want and what we want to with that data. Now we need to create the database to store the data. The first thing we need to do is to create an empty database which will hold our table. For this tutorial I will name the database linksdb. For MySQL you use the following command from the command-line to create the new database.

$ mysqladmin -u root -p create linksdb

The "-u root" says to use the MySQL root user and "-p" flag will prompt you for your password. Read the MySQL documentation on how to setup the username and password using the mysqladmin tool. You don't necessarily need root access depending on how the database is setup.

SHORTCUT: I put an alias in my ~/.bashrc file so every time I call "mysql" or "mysqladmin" it automatically adds the "-u root -p". Those entries are:

alias mysql='mysql -u root -p'
alias mysqladmin='mysqladmin -u root -p'

You can test connecting to the database using the mysql client. On the command line:

$ mysql linksdb

You should get a mysql > prompt if it connects to the database, else it will return an error saying Unknown database 'linksdb'. You can exit the mysql client by typing: exit;


Creating the Database Table
Next we need to create the table in the database. Tables are created using SQL statements, and can be created using the mysql client tool or PHP. The nice thing about using a PHP script to create the table is you can save the script to for later use, so if something goes wrong you can recreate the tables, or just use it to refer to the database schema.

The SQL command to create a table is:

CREATE TABLE tablename (
   column1 column1type,
   column2 column2type,
   etc....
);

The table we want to create is:

Table Name: links
Columns:    id           (integer - unique Link ID)
            sitename     (50 characters)
            siteurl      (75 characters)
            description  (text field - lots of text)
            category     (50 characters)

So the SQL code to create the categories table is:

CREATE TABLE links (
id INT NOT NULL AUTO_INCREMENT,
sitename VARCHAR(50),
siteurl VARCHAR(75),
description TEXT,
category VARCHAR(50),
PRIMARY KEY(id) );

The id column is the primary key for this table. This is used to uniquely refer to this record entry in the database. In order to be a primary key the column can not be null (NOT NULL). I also set it to automatically increment the number (AUTO_INCREMENT) so when each record is added the id will increase by one. The last line specifies that the id column will be the primary key for this table.

The sitename, siteurl, and category column each are specified as a VARCHAR or variable character field each with their respective maximum length. A VARCHAR field holds characters from 0 up to its maximum specified length. Most databases VARCHAR limit is 255 characters. If you have a field which may require more characters you should use a TEXT field, which is what is used for the description field. A text field can hold lots of data, usually dependent on the database.

The most common data types are INT, VARCHAR, TEXT and DATETIME. Look in the MySQL documentation about creating databases for information about other available datatypes in MySQL, and more specifics the data types it supports.


Executing SQL in PHP
The code to execute a SQL statement in PHP takes 3 steps.

  1. Connect to database
  2. Create Statement
  3. Execute Statement

1.The command to connect to the database is:

$cid = mysql_connect($host,$usr,$pwd); mysql_select_db($db);

Where $host, $usr, and $pwd are previously specified. Host refers to the machine running MySQL and the username and password to connect to that MySQL machine. This command returns a connection id, which is used for to identify this connection in later queries.

The second line simply tells MySQL which database to use.

2.Creating the SQL statement simply consists of assigning the SQL statement such as the one above to a string variable ($str). I usually have it run over multiple lines concatenating the string together as I go, this makes it easier to read.

3.The command to actual send the database the SQL command is:

$result = mysql_query($sql, $cid);

$sql is the SQL statement, and $cid is the connection id created above. This returns a 1 if executed correctly, and undefined or false if an error occurred.

Putting all of this together, and adding a little error detection and displaying of the errors gives us the following script, which you can download and load on to your web server running PHP.

IMPORTANT: Opening the file through the web server will execute the script and create the database tables.

Download: PHP Script to create tables (create_table.phps)
Rename the file from .phps to .php

Note: You must edit the username and password at the top of this script to reflect your system.

Problems: The most common problem when running this script is connecting to the database. Make sure you replace the username and password variables with your username and password setup to connect to your MySQL server. You can trouble shoot connection problems using the mysql client from the shell, use mysql -? for help.


Next Page: Insert Data

Sections: Getting Started Create Database Insert Data View Data Manage Data Resources

Related Links:

MySQL Cookbook (BOOK) MySQL Cookbook
MySQL Cookbook provides a unique problem-and-solution format that offers practical examples for everyday programming dilemmas. For every problem addressed in the book, there's a worked-out solution or "recipe".
Buy Now from Amazon.com


MySQL Manual
Information on how to use MySQL, and examples on how to create databases and tables using the MySQL client tools.