[back]

Creating a Table

Testing For PHP and MySQL

To see if MySQL is loaded on your system, simply run the PHP info script again. As you scroll down through this information, you should see a section about MySQL.

<?
phpinfo();
?>

PHP/MySQL TEST Link

Creating A Table

The process of setting up a MySQL database varies from host to host, you will however need a database name, a user name and a password. This information will be required to log in to the database. I will set up a database for each of you to use.

MySQL databases are pretty standard in that they are made up of single or multiple tables. Each of these tables is quite separate and can have many different fields. Once created, these tables can be "joined" together to share information.

We are going to create a table with student-related information. In this table, we will want to have the student's first and last name along with his/her graduation year and student number. We will also set up a field called "id". Each new student that we enter into the table will get a new unique "id" number - (not his/her student number).

Creating A Table With PHP:

Once we have established that PHP and MySQL are enabled on the server, we can connect in the following manner:

<?php
$user="mills";
$password="123";
$database="class";

mysql_connect(localhost,$user,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query="CREATE TABLE students (
id INT( 6 ) NOT NULL AUTO_INCREMENT ,
first VARCHAR( 20 ) NOT NULL ,
last VARCHAR( 20 ) NOT NULL ,
grad_year INT( 4 ) NOT NULL ,
student_no INT( 6 ) NOT NULL ,
PRIMARY KEY ( id )
)";

$result = mysql_query($query);

if( mysql_field_table($result,"id") ){
echo "Database table created";
} else {
echo mysql_error();
}

mysql_close();
?>

How it all works:

Connect to the Database Server

Enter your database, MySQL username and MySQL password in the appropriate positions on the first three lines above. Again, since all of the PHP will be processed by the server before being sent to the browser, this information will not be seen by the end user. (replace mills, 123 and class with your own which I will give you)

$username="mills";
$password="123";
$database="class";

Next, you will need to issue the command to start a database connection:

mysql_connect(localhost,$username,$password);

This line tells PHP to connect to the MySQL database server at 'localhost' (localhost refers to the server that the site is running on). It uses the username and password from the variable above.

Selecting The Database

After you have connected to the database server you must then select the database you wish to use. This must be a database to which your username has access. Use the following command:

@mysql_select_db($database) or die( "Unable to select database");

This tells PHP to select the database stored in the variable $database (which you set earlier). If it cannot connect it will stop executing the script and output the text: "Unable to select database".

This extra 'or die' part is good to leave in as it provides a little error control but it is not essential.

Building The Table

Once you have connected to the server and selected the database you want to work with, you can begin executing commands to the server. The following lines will create a new table called 'students' with these fields.

MySQL Fields: see link for more info on field types.

The mysql_query($query); will be used many times in the following exercises. In this case we are using the $query to "CREATE". In later exercises we will change it to "INSERT", "SELECT", "UPDATE", and "DELETE". For the most part, the basic structure will stay the same.

$query="CREATE TABLE students (
id INT( 6 ) NOT NULL AUTO_INCREMENT ,
last VARCHAR( 20 ) NOT NULL ,
first VARCHAR( 20 ) NOT NULL ,
grad_year INT( 4 ) NOT NULL ,
student_no INT( 6 ) NOT NULL ,
PRIMARY KEY ( id )
)";
mysql_query($query);

mysql_close();
echo "Database table created";

The last two lines close the connection and returns a line confirming that the table was successfully created. Your script will still run if you do not include these commands, but too many open MySQL connections can cause problems for a web host and it is nice to see that the operation was successful.

Create a new folder called "mysql" and save your new php script as table_students.php. FTP your new folder across the network and run the script. You will have to wait until exercise 3 to see your table, but if you received a "database table created" message, it should be there.