Lesson 18: Create databases and tables

In the previous lesson we looked at how to create a connection to a database server. Next step is to create databases and tables.

We'll look at two ways to create databases and tables. First, how it is done in PHP, and then how it's made with the more user-friendly tool PhpMyAdmin, which is standard on most web hosts and in XAMPP.

If you have a hosted website with PHP and MySQL, a database has probably been created for you already and you can just skip this part of the lesson and start creating tables. Again, you should consult your host's support pages for more information.

Create a database and tables with PHP

The function mysql_query are used to send a query to a MySQL database. The queries are written in the language Structured QueryLanguage (SQL). SQL is the most widely used language for database queries - not only for MySQL databases - and is very logical and easy to learn. In this lesson and the next, you will learn the most important SQL queries.

When creating a database, the SQL query CREATE DATABASE is used with the following syntax:

	CREATE DATABASE database name

Logical and easy, right!? Let's try to put it into a PHP script:

	mysql_connect("mysql.myhost.com", "user", "sesame") or die(mysql_error());

	mysql_query("CREATE DATABASE mydatabase") or die(mysql_error());

	mysql_close();

First, we connect to the MySQL server. Next, we create a database named "mydatabase". And finally, we close the connection to the MySQL server again.

So far so good... but things become a little bit more complicated when we want create tables in PHP. When creating tables, we use the SQL query CREATE TABLE with the following syntax:

	
	CREATE TABLE table name
	(
	column1_name DATA_TYPE,
	column2_name DATA_TYPE,
	column3_name DATA_TYPE,
	...
	)

table_name and column_name are of course the name of the table and the columns, respectively. DATA_TYPE are used to specify the data type to be inserted into the column. The most commonly used data types are:

INT: For numbers without decimals
DECIMAL: For numbers with decimals
CHAR: Short text up to 255 characters
TEXT: For plain text up to 65,535 characters
LONGTEXT: For long passages of text up to 4,294,967,295 characters
Date: For dates in the format YYYY-MM-DD
Time: For time in the format HH:MM:SS
DATETIME: For date and time in the format YYYY-MM-DD HH:MM:SS

All in all, logical and relatively easy. Let's try to put it into an example:

	
	mysql_connect("mysql.myhost.com", "user", "sesame") or die(mysql_error());
	mysql_select_db("people") or die(mysql_error());

	mysql_query("CREATE TABLE MyTable (
	  id INT AUTO_INCREMENT,
	  FirstName CHAR,
	  LastName CHAR,
	  Phone INT,
	  BirthDate DATE
	  PRIMARY KEY(id)
	)") Or die(mysql_error());
	mysql_close ();

In the example, we start by connecting to the MySQL server. Next we use the function mysql_select_db to select the database "people". Then we create the table "persons" with 5 columns.

Note that at the "id" column, we first use INT to specify that the column contains numbers and then add AUTO_INCREMENT to automatically increase the number and ensure a unique ID for each row.

At the end, we use PRIMARY KEY to set the "id" column as the primary key. The primary key uniquely identifies each record (/row) in the table, which becomes very useful later when we update the database.

Create database and tables with phpMyAdmin

It can be useful to be able to create databases and tables directly in PHP. But often, it will be easier to use phpMyAdmin (or any other MySQL administration tool), which is standard on most web hosts and XAMPP. The screendumps below shows how to create a database and tables in phpMyAdmin.

Start by logging onto phpMyAdmin. Often, the address will be the same as your MySQL server (eg. "http://mysql.myhost.com") and with the same username and password. In XAMPP, the address is http://localhost/phpmyadmin/.

When you are logged on, simply type a name for the database and press the button "Create":

phpMyAdmin

At some hosts, it's possible the have already created a database, and you may not have the rights to create more. If that is the case, you obviously just use the assigned database.

To create a table, click on the tab "Databases" and choose a database by clicking on it:

phpMyAdmin

Then there will be a box titled "Create new table in database", where you type the name of the table and the number of columns and press the button "Go":

phpMyAdmin

Then you can name the columns and set the data type, etc., as in the SQL example above.

phpMyAdmin

Notice, that here we also set "id" as PRIMARY KEY and uses AUTO_INCREMENT (A_I).

Now you have created your own database and table. In the next lessons, we look at how to insert, retrieve and delete data in a database

Username:
Password: