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 documentationmysql_query are used to send a query to a MySQL database. The queries are written in the language Structured Query Language (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 documentationCREATE 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 documentationCREATE 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:

documentationINT
For numbers without decimals
documentationDECIMAL
For numbers with decimals
documentationCHAR
Short text up to 255 characters
documentationTEXT
For plain text up to 65,535 characters
documentationLONGTEXT
For long passages of text up to 4,294,967,295 characters
documentationDate
For dates in the format YYYY-MM-DD
documentationTime
For time in the format HH:MM:SS
documentationDATETIME
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 documentationmysql_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 documentationINT to specify that the column contains numbers and then add documentationAUTO_INCREMENT to automatically increase the number and ensure a unique ID for each row.

At the end, we use documentationPRIMARY 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 documentationPRIMARY KEY and uses documentationAUTO_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


Related topics in the PHP ForumRepliesViews
Retrieve data from the database -error1438519
PHP Code for retrieve and write data from a table1116525
Link Problem - Retrieve data from a database211529
Validate form and insert data in Database216299
Mysql Connection Not Allowed17628

+ Post a new topic


<< Lesson 17: Databases

Lesson 19: Insert data into a database >>