Chapter 14:
Working With Databases

Accessing a Database

First, what exactly is a database? It's made up up one or more "tables" that hold the data. In each table there are a number of "fields." Each field has a type, a size and some other properties such as a default value, and a flag to say it either can or can't be "NULL." NULL is special concept. It means there is no value stored in a field. It's NOT the same as the field holing a zero or an empty string. NULL means there is absolutely nothing in the field.

Each table has zero or more rows. Each row has one each of every field. The fields are defined when the table is created. The rows are usually numbered. Data items that are related in some way are stored in the same table. If there are several tables, they may have a field in common, so you can "join" tables to pull out data items from more than one table with a single request.

Before you can get data from your database or store data in it, you have to "connect" to it. Making the connection requires several specific pieces of information:

1. the name given to the database when it was created
2. the name or IP address host/computer where the database is located
3. a username that the database will recognize as legitimate
4. the password for that user

Here's how PHP would go about connecting to a MySQL database. First, the four items needed for the connection are assigned to local variables. Then the mysql_connect function (which is built into PHP) is used to make the connection. $db_conn is just a local variable that gets some value if the connection is made successfully. It won't make any difference to you exactly what that value is, only that it exists.

<?php
$db_name = "sales_data";
$db_host = "21.41.103.8";
$db_user = "myHandle";
$db_pass = "myPassword";
$db_conn = @mysql_connect("$db_host", "$db_user", "$db_pass")
or die("Couldn't connect to mysql");
$db = @mysql_select_db($db_name, $db_conn)
or die("Couldn't select database $db_name");
?>

The "@" symbol right in front of the function name tells PHP to suppress its errors and warnings. The 'die("Couldn't connect to mysql")' part tells it to let you know if you can't make the connection. If you see this message, it usually means that one or more of the items you used to make the connection is typed in wrong. Check all that stuff very carefully. If all of it is correct, check with your system administrator. Once in a while, the MySQL server locks up and has to be re-started. Just don't bug the tech support folks until you're sure it's not your code that's causing the problem!

The last line of the code snippet above calls the built-in function mysql_select() and selects the desired database. If you've made a typo (usually the database name), you'll get the message "Couldn't select database $db_name." Be sure you've got the name right; you can't select a database that the system can't find. Let's see how we actually do something with the database.

Writing to a Database

Suppose your database has a table called "hits" where you record the information you get when someone visits one of your web pages. You decided to store the time and date, IP address and which browser was used. You also wanted to record which search engine and keywords, if any, were used by the visitor to find your page.

In the hits table, you'd have fields with names like these: ip, time, date, browser, engine, keywords and uri. Now, what data types should they be? The IP address will have 3 dots and up to 12 digits. MySQL provides a data type called VARCHAR, which can hold up to 256 characters, and you can limit the number of characters it will accept. So you'd want the type to be VARCHAR(16). This reserves space for up to 16 characters but only uses as much space as it actually needs.

For the date and time, you can use the DATETIME data type. Be sure that your date and time are formatted like this: 2004-04-25 11:45:59 (Year-Month-Day hr:mn:sec) before you store them in the database. The browser info can be stored in a VARCHAR(100) field and so can the search engine. You can limit the storage space needed by chopping off the "www." and ".com" and just saving "google", "yahoo", etc. For the keywords, you'd probably want to use VARCHAR(255).

Don't get too anxious about all the little details just yet; there's a whole chapter coming up about using databases. Right now, I just want you to be able to visualize what reading and writing to/from a database is like, so you'll see what query strings may have to do with it. So let's see how you'd store this stuff away. First we'll build an SQL "query" that can write the stuff into the database:

$sql = "INSERT INTO customers(ip,date,browser,engine,keywords,uri)
VALUES(\"$ip\",\"$date\",\"$browser\",\"$engine\",\"$keywords\",\"$uri\")";

Notice that I had to "escape" a bunch of double-quotes. Here's why: They were all nested inside a double-quoted string and they can't be allowed to confuse PHP about where the string begins and ends. The variables that contain string data had to be in quotes so that MySQL wouldn't try to treat them as numbers. Numeric data can be passed to MySQL without the quotes. OK, now let's actually write the data into the database:

$result = @mysql_query($sql, $db_conn);

Whew! Now I can finally get to my point. Any item you passed to a script in the query string will become a local variable. Those variables can be put into an INSERT statement for MySQL. Then the INSERT statement is processed and the data gets stored. This is something you'd probably want to do when you provide visitors a form for subscribing to your newsletter.

The database would save sufficient evidence that they really did subscribe - very useful in case someone claims he or she didn't subscribe and that you're spamming! OK, but what if the person who filled in the form maliciously typed in someone else's email address? Having the perpetrator's IP address would help you investigate the incident. Also, you might want to keep a list of "bad" IP addresses and check against them before allowing the procedure to finish.

Reading from a Database

Since you already know how to connect to a database, we can skip that discussion here. Besides, I'm going to show you something else that the query string, with the help of your database, can do for you.

Let's say your site has a catalog of hundreds of different items. Do you really want to create separate pages for individual items or even small groups of items? I doubt it; that sounds like way too much work to me. What you'd want a search form that would let your visitors select just what they wanted. Using their input, you could ask the database to locate what the user wants. Then you could display the search results as a dynamic web page, complete with pictures, descriptions and links to your shopping cart.

Let's look at how that could be done. First you'll need a form. Let's say you're selling rechargeable batteries of various kinds. To do an efficient search, you'd need to know 3 things:

1. The type of device (laptop, camcorder, etc)
2. The manufacturer of the device (Sony, Kodak, etc)
3. The model # of the device (or at least something close to it)

Your search form might look something like this:

Your customer would type in the device type (laptop, for example), the manufacturer (Sony, for example) and the model number. When the button was clicked, the form would call a script (perhaps itself) called search.php or something like that. It would send a query string along with it, like this:

http://www.mysite.com/search.php?device=laptop&maker=Sony&model=VAIO

The script would create these variables:

• $device (with the value "laptop")
• $maker (with the value "Sony")
• $model (with the value "VAIO")

They would be plugged into an SQL query that looked like this:

$sql = "SELECT model FROM products
WHERE device='$device' AND maker='$maker' AND model LIKE '%$model%' ORDER BY model";

"SELECT model FROM products" means look up the model field in one or more rows. "WHERE" means the search for values in the "model" fields must meet certain criteria (the device field must contain "laptop," the maker field must contain "Sony" and the model field must resemble "VAIO").

Using LIKE instead of "=", along with the "%" characters before and after $model allows a very "fuzzy" search. Model numbers that contain VAIO, preceded by or followed by any other characters, will be a match.

This line would then perform the search in the database:

$result = @mysql_query($sql, $db_conn);

After the search, $result will contain every model in the table where a match was found on the device type, manufacturer and model number. These results can then be used to display a page to the visitor - but that's another story altogether.

Copyright © 2004 Steve Humphrey