Welcome to S-Design
The home of your bespoke, professional, fast-loading, interactive, database-driven web presence.
Menu
MySQL TUTORIALS


MySQL Tables

Interacting with a MySQL database is a little weird as you donít have the tried and true WYSIWYG interface that something as easy as Microsoft Access affords. When creating tables, youíll either have to create them by using SQL Statements, or by using another open-source tool available online called PHPMyAdmin. PHPMyAdmin gives you an easy-to-use interface that allows you to create tables and run queries by filling in a little bit of information and then having the tables created for you. This is good if youíre either lazy, or donít feel like bothering with big and complicated SQL Statements.

Here we're going to show you how to create tables using SQL (using PHP to create the tables)

Create a Table: <?PHP
include_once('DBConnect.php');

mysql_query("CREATE TABLE customers(id int NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), name varchar(50), email varchar(100), address varchar(300), phone int)");

?>
This query tells MySQL to create a table called customers, and sets it's fields/columns to id, name, email, address, phone.
We also need to tell MySQL what type of data will be stored in each field. Because the name field will be variable in width (i.e. the name might be 10 characters or 40 characters) we'll tell MySQL to use the varchar data type, and limit it's size to 50 character. the same applies for the email and address feilds. Because the id and phone fields will only ever have numbers in them, we use the int (integer) data type.
We also want an id for each customer to be unique, and auto-incrementing. This mean that each time we add a new record to the customers table, the id field will be automatically created for us with the next number. So if, for example, we already have 20 customers in the customers table, adding another one will automatically be given the id of 21. We also make the id column the Primary Key for quick reference and sorting purposes. This speeds up the process of finding records.

You should only need to run this kind of script once per table, and should then delete this script from your hosting, so it can't be accidentally run again.

Title