Week 5: MySQL and Relational Databases

What is a database?

Think of a database like a catalog in a library. There are many ways of storing this data: card catalogs, finding guide books, or computer-based systems. Within the final option there are lots of options too. XML is one way of storing this data, but if you store every record in one file, the computer will have to skip over a LOT of records before it can pull up the one you want. You can split these XML files into separate files and folders, but then the computer must be able to determine from the filename and directory path what the file contains, or it will have to open the file and process it anyway. Much work in computer science has gone into finding to best methods for allowing a computer to store information as efficiently as possible while also allowing it to find the data as quickly as possible. Programs that attempt to do this are called databases.

There are a lot of kinds of databases. About 10 years ago, one of the most popular methods was to build hierarchies of the information. That is, much like in XML, there was a root category under which everything was classified. Under this would be subcategories which would themselves have subcategories. This meant a computer could ignore completely branches that were not included in a search. For instance, if a user searched for a work of fiction, the computer could ignore the non-fiction branch altogether.

This worked very well for searching, but it could be difficult to classify certain relationships. A title, for instance, should probably be a subcategory of book. But what if the book exists in paper, electronic, audio CD, and audio cassette form? Perhaps media should be first, but then a search for all records relating to the same work, regardless of format, would have to transverse many different branches of the tree, dampening some of the efficiency.

Cultural shifts which led to a distrust of hierarchies mirrored a shift from hierarchical to "relational" database model. A relational database is, as one might expect, based relationships that can be relatively more complicated that a simple parent-child relationship. A common relationship may be something like "has many," but can be as complicated as "has 10 books of genre Mystery."

Database creation and PHPMyAdmin

We will create our first database using a PHP program built into WAMP/MAMP called phpMyAdmin. On Windows, go to http://localhost/phpmyadmin. On the Mac, go to http://localhost:8888/phpMyAdmin. We will turn our flat file of comments from last week into a database. The database name should describe an entire project rather than any small piece of it, so lets call this database "Poetry_Project." Type "Poetry_Project" in the blank labeled "Create a new database." Click "Create".

Now we need to think about what we want in our database. The database will store a series of objects called Tables. Think of these tables like tables in an Excel file. We will need to create labels for the columns in our first table. We need to think about what we want to store in each comment. For now, let's assume we want to store an author username, content text, and a datestamp. We will also want to include a ID column to uniquely identify each comment. So, in phpMyAdmin type comments in the "Name" blank under "Create new table on database Poetry_Project." Type 4 in the blank labeled "Number of fields:". You will be taken to a new screen that allows you to define the column names and give them other information.

You will now need to fill in the blanks labeled "Field" with the column names. Let's call them, in order: commentID, userID, commentText, date. You will then need to define the "Type" of each field. MySQL is built to allow the user the maximum amount of control over how much memory is assigned to each record. A fairly complete list of definitions of the types allowed in MySQL can be found at http://www.htmlite.com/mysql003.php.

For the ID field, let's use an INT. This is an integer (a number) from 0 to 2147483647 (probably more comments than are likely to posted on our site). You can (and probably should) increase the number to 4294967295 (twice as much), by setting the "Attributes" value to "Unsigned," meaning the value cannot be negative. We do not have to specify a length with the INT type, collation refers to the type of text encoding and we can just leave it blank for now, we should keep the Null drop down at not null (which means that each record requires an ID field), and we do not want to set a default value. We will want this value to auto-increment, so set the "Extra" value to reflect this. This value, as the ID, is the Primary Key, which means that it cannot be duplicated within the table. So click the little circle under the icon with a key on it.

The username value should be a VARCHAR with a length of 25. This means that the username can be up to 25 characters long, but usernames shorter than that will not take up more memory than necessary. Everything else can remain in its default state.

The comment field should be a medium text (that is, its should allow up to 16777215 characters). No other specifications are needed.

The date field should take type DATETIME and the attributes drop-down should be changed to ON UPDATE CURRENT_TIMESTAMP (this means the comment will always, automatically be stamped with the time and date of its last change).

You can now save the table. Create a new table called USER. Test your skills by creating a 4 field/column table with a primary key called "username", and fields for First Name, Last Name, and Password. The field for PASSWORD should be at least 256 characters long.

INSERTing records

Alright, we are now ready to put some data in the database. This is done with the INSERT command in MySQL. MySQL is actually a sort of programming language in its own right, and all we have done with phpMyAdmin has actually generated MySQL commands that were executed by the database. Unfortunately, we have now reached the point where we can no longer rely on phpMyAdmin. However, we can use it to teach us.

Click on the USER table. You will see a tab about midway through the tabs at the top of the screen that says INSERT. You will be able to manually create a new user by putting information in the "Value" fields. You can leave the functions blank except for the password blank which you should set to "Password" (this encrypts the password so if someone hacks the database they can't pretend to be one of the users. When you are done, click "Go."

At the top of the page, there will be a greyed box labeled "SQL query" which should contain something like the following:

INSERT INTO `Comments`.`users` (
`username` ,
`password` ,
`FirstName` ,
`LastName`
)
VALUES (
'larue24601', PASSWORD( 'mypassword' ) , 'Doug', 'Reside'
);
This is the command (or query) sent to the database to put the first record into the database.

PHP can issue these commands as well. Open a text editor and type the following:

<?php
$username="root";
$password="";
$database="users";
$first = $_GET['firstname'];
$last = $_GET['lastname'];
$user = $_GET['username'];
$pass = $_GET['password'];
//If you are on a windows machine, leave off :8888 on the next line
mysql_connect("localhost:8888",$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$thequery = "INSERT INTO 'users' (
'username' ,
'password' ,
'FirstName' ,
'LastName'
)
VALUES (
'$user', PASSWORD( '$pass' ) , '$first', '$last'
);";
$result = mysql_query("$thequery");
mysql_close();
?>
Note that we use the function PASSWORD() around username. This encrypts the plain version sent by through GET. Usually we will want to send passwords through the "POST" method, but even then we will always want to encrypt them using the password function.

Now, because we used the GET method, we can test this PHP by pointing our browser to the address of the PHP with the values of the variables at the end. Like this:

http://localhost:8888/tei/createUser.php?firstname=Doug&lastname=Reside&username=larue24601&password=doug

Now, test yourself by creating a form that will allow new users to generate accounts.

Requesting data from the database

Now, let's request data from the database rather than putting information into it. Click the Browse tab tab at the top of the phpMyAdmin list for your users table. Notice that the SQL code that is generated at the top says:

SELECT *
FROM `users`
LIMIT 0 , 30
This means "Select everything from the table "users" but only list the first 30 elements.

We may want to select something more specific. For instance, we may want to get the password for a particular username. The w3schools page has a good tutorial on SQL syntax, but it is usually pretty intuitive. To find the password for a username equal to "dreside" you would submit the query:

SELECT password FROM users WHERE username='dreside';

This list is returned to PHP in something called a result array. To get a specific value from the list, you specify the row and column of the result array you want. For example, assume that the variable "user" was sent through a POST method. The following code will echo the stored password for that username to the screen (assuming, as we can, that usernames are unique in our database).

<?php
$username="root";
$password="";
$database="users";
$user = $_POST['user'];
//If you are on a windows machine, leave off :8888 on the next line
mysql_connect("localhost:8888",$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$thequery = "SELECT password FROM users WHERE username='$user';";
$result = mysql_query("$thequery");
$storedPassword = mysql_result($result,0,"password");
echo $storedPassword;
mysql_close();
?>

Consider the third from the last line in the above example:

$storedPassword = mysql_result($result,0,"password");
This line gets row 0, column "password" from the result set stored in the variable $result, and then stores this value in $storedPassword.

Now, of course, we would never want to echo a password to the screen. Let's assume, instead that a user has submitted a variable "user" and a variable "pass." We want to make sure this "pass" variable is equal to the value stored as the password for the username $user. Here's one way to do it:

<?php
$username="root";
$password="";
$database="poetry_project";
$user = $_POST['user'];

$pass = $_POST['pass'];

//If you are on a windows machine, leave off :8888 on the next line
mysql_connect("localhost:8888",$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$thequery = "SELECT username FROM users WHERE username='$user' AND password=PASSWORD('$pass');";
$result = mysql_query("$thequery");
$storedUser = mysql_result($result,0,"username");
if ($storedUser == $user){
echo "Welcome!";
}
else {
echo "No hackers allowed!";
}
mysql_close();
?>

Now, create a form that allows users to create accounts, log in, and post comments. All the comments should be visible in a div block that can be hidden by the user.