MySQL PHP Web Database 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 to do is create an empty database to hold our table. For this tutorial, I name the database linksdb. In MySQL you use the following command from the command line to create the new database.

The "-p" flag is used so it will prompt you for your password to MySQL. Most setups require a username and password. Read the MySQL doumentation on how to setup the username and password using the mysqladmin tool. It is fairly straight forward, however you may need root access.

Test connecting to the database using the mysql client. On the command line:

You should get a mysql> prompt if it connects to the database. Else, it returns an error saying Unknown database 'linksdb'

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 use later. If something goes wrong, you can recreate the tables. Or, you can use it to refer to the database schema.

The SQL command to create a table is:

The table we want to create is:

Table Name: links
Columns:    id           (integer - primary link key)
            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:

The id column is the primary key for this table. 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. A primary key is a unique number for that specific record or row of data.

The sitename, siteurl, and category column each are specifed 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 has 3 steps:

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

1.The command to connect to the database is:

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.

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

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

Where $db is the database to query, $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. Note: 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)
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: Putting data into the database.

Tutorial Sections

• Getting Started

• Create Database

• Insert Data into Database

• View Data from Database

• Manage Data

Related Links:

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

Introduction to SQL
By James Hoffman. One of the best tutorials on the internet regarding SQL. It says Introduction to SQL but I have not needed anything that is not covered in this tutorial.