MySQL database tables - Definition

We have seen that a database allows you to store the information which has been collected during the execution of your PHP script. But just how exactly is this information stored ?

Actually, a database can be seen as a collection of tables, each table being constituted of fields which are destined to store specific pieces of information. In this way, a table containing n fields is simply an array whose elements are vectors of dimentions n. Additionally, each database table must contain a primary key field whose value must be non-null and unique for each field (the purpose of this primary key is to make possible the identification of the different fields).

In order to illustrate this definition, imagine the following schema: you are a company selling computers, and you have just set up a database called Computers_Inc. This database contains two tables: one of them is called Clients, while the other is called Computers Sold.

The database table Clients will contain the fields ID, Address, Name, First Name, Tel. Every time you add a new item to the database table Clients, a new vector (ID, Address, Name, First Name, Tel.) is created containing the relevant information for each field.
The field ID can be set as the primary key (and can contain for instance the social security number of the client; all that matters is that each client must hold a uniquen non-null ID).

The purpose of the next paragraph will be to introduce how these tables and table entries can be defined in SQL and more particularly from within your PHP script (using the function mysql_query, as was done for the creation and deletion of MySQL databases).

MySQL database tables - Creating or deleting a MySQL database table

Prior to the manipulation of MySQL database tables, a MySQL database must have been selected once your MySQL connection has been established.

In order to select a MySQL database to work with, you must use the PHP function mysql_select_db whose syntax is:

mysql_select_db(database_name,$connection)

where database_name is the string name of your database
where $connection is the variable storing your connection, as defined in our tutorial about how to connect to a MySQL database server.

Only once a database has been selected can the creation of a database table be done via the SQL command CREATE TABLE as follows:

CREATE TABLE my_table
(field_name_1 type_1,
PRIMARY KEY (field_name_1),
field_name_2 type_2,
field_name_3 type_3,
...)

where field_name_i is the name of the i-th field of the table.
where type_i is the type of data contained within the field i.
where PRIMARY KEY specifies that field_1 holds the primary key of the table, and as such you will need to ascertain that all the values taken by this field are non-null and unique.

Remark:

Among the possible types that can be accepted by a MySQL database field are (to name only the most common ones): CHAR(size) (designates a fixed length string of size characters), VARCHAR(size) (designates a variable length string of up to size characters), TEXT (designates a string with a maximum length of 65535 characters), LONGTEXT (designates a string with a maximum length of 4294967295 characters), INT(size) (designates a number from 0 to 4294967295 and who can be no more than size), BIGINT (size) (designates a number from 0 to 18446744073709551615 and who can be no more than size), etc ...
The list of SQL types given above is in no way exhaustive; for a complete list of SQL types, please refer to the SQL user manual.

As usual within a PHP script, the previous SQL command will be transported through the mysql_query PHP function as follows:

Learn the PHP & MySQL code:

<?php
$connection = mysql_connect('localhost', 'john', 'secret');
if (!$connection) die('An error has occured during the connection');
mysql_query('CREATE DATABASE your_database', $connection);
mysql_select_db('your_database',$connection);
mysql_query('CREATE TABLE my_table (ClientID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(ClientID), Client_Address TEXT)');
?>

Remarks:

The NOT NULL setting guarantees that the field will not accept any NULL entry.
The AUTO_INCREMENT setting allows an automatic incrementation (+1) of the specified field entry every time a new entry is inserted into the table. Adding the AUTO_INCREMENT setting to the PRIMARY KEY fields is customary since it automatically guarantees the unicity of the primary key without you having to worry about defining this field every time a new entry is added to the table.
In order to delete a database table, you can use the SQL command DROP TABLE (its use is exactly the same as that of DROP DATABASE).

We have seen how to create and delete MySQL database tables, each table being assigned a primary key which must be unique for each entry.
In the following tutorial, we shall see how to populate these database tables with entries (which constitute the essence of the information contained within the database).

Next tutorial: MySQL database entries
Previous tutorial: Creating or deleting a MySQL database

Back to computer forums