Creating a table within a database

This tutorial will explain how to create a users table in your database. This tutorial will assume you know how to connect to the database.

// database connect

This is a comment which php will ignore. The comment is there for your own reference.

The include function will include the database connection details

to include the database details php needs to know where to find the file. We will let php know the address of the file with the global variable 

$_SERVER['DOCUMENT_ROOT']

which will get the address from the start of the server ie the domain name www.example.com

Since we connecting a global variable with a sting we need to join them together using the concatenating string 

'.'

then were telling php to look in a directory called includes then find the file config.php. End the statment with a semicolon ;

// database connect
include $_SERVER['DOCUMENT_ROOT'] . '/includes/config.php';

Once connected to the database, We create the variable to create the table

$sql = 'CREATE TABLE members (

The variable tells MySQL to a create table called members then everything between () will be executed.

memberID INT (4) NOT NULL AUTO_INCREMENT PRIMARY KEY,

The first column in the table will be called memberID which has a data type of INT (integer) which means numbers can only be in this field.

This column cannot be empty so we state NOT NULL with AUTO_INCREMENT which will automatically create the next number in sequence each time data is entered into the table.

We need this field to be unique to we set it as the PRIMARY KEY. include the comma at the end to tell MySQL to carry on the next line.<

The next lines do the same thing so I won't go into each line here's an explanation of the data types used:<

VARCHAR means not fixed length of numbers and characters we define the maximum number of characters with parenthesis (20) would mean a maximum of 20 characters. length can be a maximum of 255

CHAR means a fixed length field from 0 - 255 characters long.

TIMESTAMP creates a time format, given a default of CURRENT_TIMESTAMP inserts the current date and time.

The create table has now been created we want to see if the table has been created so we will check with an if statement:

if (@mysql_query($sql)) {
  echo 'members table successfully created!';
} else {
  exit('Error creating members: ' . mysql_error() . '');
}

Using the php function 'if' and using the error suppressor to silence error messages so we can create our own.

mysql_query ($sql)

checks if successful then prints a message 'members table successfully created!'

If not successful then the else statement is executed and the script stops with the 'exit()' command with a message to tell you there was an error.

exit('Error creating members: ' . mysql_error() . '');

<p>Here's the full script:</p>

// database connect
require $_SERVER['DOCUMENT_ROOT'] . '/includes/config.php';
$sql = 'CREATE TABLE members (
memberID INT (4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(20) NOT NULL,
username VARCHAR(20) NOT NULL,
password CHAR(32) NOT NULL,
email VARCHAR(255) NOT NULL,
joined timestamp NOT NULL default CURRENT_TIMESTAMP,
level INT(1) NOT NULL default 1
)';

if (@mysql_query($sql)) {
  echo 'members table successfully created!';
} else {
  exit('Error creating members: ' . mysql_error());
}

Save the file and upload it to your web server and run the file you will get a message either that's relevant to the table being created or not.

That's all there is to make a basic table using PHP &amp; MySQL

Enjoy.