MySQL PHP Database Tutorial: Create Database

Now that we know what data we want and what we want to with the data, 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 for security reasons. Set up the username and password using the mysqladmin tool. You can read how in the MySQL doumentation that is part of your installation or read it online at the MySQL site. It is fairly straightforward, 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

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)

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 about the data types it supports.

Executing SQL in PHP

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 assigned. Host ($host) refers to the machine running MySQL. Username ($usr) and password ($pwd) connect to that MySQL machine. This command returns a connection id, which is used 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 actually 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.

Troubleshooting 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 troubleshoot connection problems using the mysql client from the shell, use mysql -? for help.

Next: Putting data into the database

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.

Tutorial Sections

• Getting Started

• Create Database (this page)

• Insert Data into Database

• View Data from Database

• Manage Data