PHP

Create a Table

We create a table users. This is done only once. You have the choice of using either PHP or phpmyAdmin to create the table.

A unique index is placed on the email field, and another index is placed on the combination of the email and pass fields. These two fields will be used togetehr during the login query, so indexing them as one , which is called login.

field	type
user_id	INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
first_name	VARCHAR(20) NOT NULL
last_name	VARCHAR(40) NOT NULL
email	VARCHAR(80) NOT NULL UNIQUE KEY
pass	CHAR(40) NOT NULL
active	CHAR(32)
registration_date	DATETIME NOT NULL

<?php
// This is the page to create a table called "users"
// to create a different table, change the name in the db_connect.php

		// connect to the database server
		include ('db_connect.php');

		// define the query
		$query = "CREATE TABLE " . TABLE_NAME . " (
		user_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
		first_name VARCHAR(20) NOT NULL,
		last_name VARCHAR(40) NOT NULL,
		email VARCHAR(80) NOT NULL UNIQUE KEY,
		pass CHAR(40) NOT NULL,
		user_level TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
		active CHAR(32),
		registration_date DATETIME NOT NULL
		)";
		// run the query
		if (@mysql_query($query)) {
			echo "The table has been created.";
		}
		else {
			die ("Could not create the table because: ". mysql_error(). "");
		}

		// define the query to create an index
		// on the email and pass.
		// they are used together during the login query
		$query = "CREATE INDEX login ON " . TABLE_NAME . " (
		email, pass
		)";
		// run the query
		if (@mysql_query($query)) {
			echo "The index to login has been created.";
		}
		else {
			die ("Could not create the index because: ". mysql_error(). "");
		}

		// close the connection
		mysql_close($link);

?>

View the effect