Web designers and developers who have worked their way through HTML, CSS and PHP tend to regard databases as rather esoteric and ethereal things. But databases exist as a very real core component of the modern web, and are the final component of the (W|M|L)AMP stack that we will learn to create a complete web site.

Databases are used to record website data that is either:

1. open to addition, removal or modification, such as customer information (mailing and eMail addresses of users, for example) or a limited amount of text that you might want to have the client/site owner change, without having to worry about ruining anything in your HTML or CSS, as in a CMS.

2. Available for the user to sort or search, such as product information or entries in a blog.

At their root, databases are nothing more than related tables of data. They are, at a simple conceptual level, very similar to the spreadsheets that you may be familiar with in Excel or similar applications: columns defining what kind of data is placed in them (in MySQL, referred to as fields), and rows containing different records, each row being defined by a sequence of data across multiple fields, read in a single horizontal line.

It is a frequent mistake of learners to try to make a single database table that attempts to record every single bit of data for your site. Instead, it is usually much more efficient to make several database tables, each recording different aspects of a site (comments, user details, etc., each in a separate table) that share a common unique key, such as a user ID. Using a language such as MySQL (introduced in the next article) you can then query from multiple tables to get the information you require. Most modern databases are relational: that is, they organize data based on principles (known as a schema) by which data in different tables can relate to each other.

Despite this, it is fairly common to use the terms “database” and “table” interchangeably, much as people confuse “web” and “Internet”.

MySQL is one of a number of popular website database solutions: other options include PostgreSQL and Firebird. The popularity of MySQL stems in large part from the fact that the database system both free and open source, and has strong community support. Most web hosting providers supply MySQL as the default database system for sites.

MySQL (pronounced either as “My SQL” or “My Sequel”) was developed by Ulf Michael (“Monty”) Widenius and David Axmark. “SQL” stands for Structured Query Language, the language in which its databases and tables are created, modified, and manipulated; the “My” is for Michael’s daughter. While the company originally formed to promote MySQL has undergone several mergers and acquisitions, and while there is a commercial version of MySQL available (used in high-demand sites such as Wikipedia, Facebook, Google, Flickr and Twitter), the open-source version is free, and always will be.

There are many tools for interacting with MySQL data, including the MySQL Workbench for Windows, Mac and Linux, but the most common encountered by web developers is phpMyAdmin, which is built into many web hosting packages and servers.

One of the primary advantages of SQL languages is that simple database queries are written in a very human-readable way. For example:

1. SELECT * from camels where humps = 2 ORDER BY species

In this case, the query could be translated as “Select everything from the camels table that has a value of 2 in the humps field and provide the result in alphabetical order using the species field.”  As a result, Bactrian camels would be listed first (not that there are any other surviving two-humped camel species).

Database-as-language

One aspect of MySQL that can be confusing at first but valuable once grasped is that the language is the database; every action taken with the database is written in MySQL, as is the database itself. (Take a look at some of the printouts of the MySQL activity as you press buttons in phpMyAdmin, for example, or read an exported MySQL database as text).

You’ll find a lot of MySQL tutorials go straight to the command line. While there’s a lot that’s right about that approach – the command line is precise, and always works so long as you type commands correctly – its precision and bare-bones interface is also intimidating to new users. Instead, here I’ll do a light, basic introduction to creating a database table via phpMyAdmin.

First, we have to set up a database to contain our table. The name of this database can be almost anything, but should follow standard web naming conventions, with the addition that you should avoid underscores in the name. Click on the Databases tab and enter the name of the database that will contain our tables.

“Collation” is essentially the default character encoding for the database and any tables stored inside it. As a general rule, we should use the same encoding we use on everything else (HTML, CSS, files): utf-8. In phpMyAdmin, there are a bunch of options for this: we’ll stick to utf-8_general_ci.

With our database created, it should appear on the left sidebar of phpMyAdmin. Click on that database entry.

We can now create a new table inside of this database. Remember that tables store values in fields, so the first thing we must determine, after determining our table name, is the number of columns that our table has. (While we can add or modify columns after this point, things are a lot easier if we get this step right at the beginning.)

How Many Columns Do I Need?

For a basic table, this is a fairly simple question to answer: the correct question to ask is “What is the maximum number of discrete pieces of data that I will need for a record, that belong in a single table together?” Another way to think of the question might be “How many ways do I want to sort this table?” For example: making a database table for user records that only records a person’s full name in a single field will make it very difficult to sort by last name, so it would be better to make two fields/columns that separately held the user’s first and last names.

If the database is to be filled from a form, the answer to this question is even simpler: the number of columns in your database table is always the number of inputs in the form plus at least two. That is, a text input will require one field in the table, a drop-down option another, a group of radio buttons another, and so on. Add them up, and then add two to the final total, for reasons we will explain shortly.

Whatever number you come up with, enter this in the “number of columns” entry and press “Go”

Database Table Management

Seeing the phpMyAdmin table creation screen for the first time, you are confronted with what appears to be a bewildering array of options. In reality, it’s not so bad: you just need to keep a few things in mind.

MySQL is always trying to achieve three goals simultaneously: first, arrange the table so that data within it can be organized optimally. Second, limit the amount of data that can be put inside a table field (limiting the growth of a table, as the faster a table grows the slower it will respond to queries). Third, do some basic validation of the data that enters a field (so that text does not go into a field that expects a numeral, for example).

Your first field is key. Literally. The first field should be something that makes each row uniquely identifiable. Words by themselves are not enough for unique identification: many people have the same name. Usually, this key is a unique identification number or code: for a product, it might be a SKU or barcode number, but for most everything else it could be a simple unique integer.  We indicate that this will be our primary key field by choosing just that option. (The name will usually be something like id).

The size of the integer we use for the identifier is important. By default, MySQL uses the INT type, which has a limit of integers between 0 and 4,294,967,295. In other words, you could uniquely identify a little fewer than 4.25 billion rows in your table. That’s likely too large, but neither do you want to use TINYINT, which can only store numbers between 0 and 255. SMALLINT (between 0 and 65535) is plenty for most purposes, so we’ll choose that. (Again, we change this later if needed). We’ll also set this field to AUTO-INCREMENT (the checkbox under A_I), so that we don’t have to enter it manually: the first record we enter will automatically be identified as row 0, the second and row 1, and so on.

Second, we’re going to make a field to store a user’s first name.  The easiest way to do this is to reference the form that we’d make to enter information that would be stored in the database, and keep in mind two simple rules:

• Form inputs that are type text are almost always stored as varchar in the database, and…

• …whatever the maxlength of the input is in our form should also be the limit supplied for varchar (which makes sense: there’s no point in trying to make more room in the database for characters that the user cannot possibly provide. Nor would making the number of characters stored in our database field less than the maxlength of the input make much sense: MySQL will happily clip text information that doesn’t fit inside a varchar field, leaving us with incomplete data).

The same would be done for the user’s last name, etc. I usually find it easiest to match the name I give the database field to the value of the name for the associated input.

If we had a text area in our form, the associated field type in our database would be TEXT. The limits match those for INT: TEXT holds a little over 65,000 characters, TINTTEXT 255 characters, etc.

A few other types that are handy:

• If you are recording the price of anything that includes fractions of a dollar, use DOUBLE as the input type (i.e. double decimal notation, such as 33.93). Adding a dollar sign is not necessary.

• If you know that the field can only have one of a limited number of values (such as provincial information for location of birth) you can use a SET or ENUM field to define the values that are acceptable.

• A TIMESTAMP field is very useful for recording when a record was made or updated. TIMESTAMP will be automatically filled out by the server: it, along with the primary key field, are the two fields I suggested adding to your calculation above.

While it’s not my preferred method for connecting to a MySQL server, writing queries, or displaying results (for reasons I will discuss shortly), using DreamWeaver’s “point-and-click” tools to create a MySQL connection does have the benefit of being fast and relatively easy to understand, making it a useful starting point for exploration of MySQL.

If you want to use DreamWeaver's built-in methods for including MySQL data on a page, you first need to make sure that your DreamWeaver site is set up completely correctly: the smallest error in site configuration, even one that didn’t have any consequences before, will matter now. (I’m assuming for the purpose of this exercise that you have access to a MySQL server, and have already created a database table with some information.)

Next, create a new PHP page and save it in your site folder. This will be the page on which we display the results of our database query. Getting to the point of displaying live database information on a web page will require three steps:

1. Providing DreamWeaver with information about our MySQL server, including a username and password for authorization.
2. Creating a query that interacts with a database table. (Extracting, inserting, or modifying data).
3. Displaying this data on the page.

On your new PHP page in DreamWeaver, switch into Design Mode. (While this step isn’t technically necessary, it will eliminate some possible distraction, and serve as an insightful final reveal at the end.)

We’re going to work backwards in this lesson, telling DreamWeaver that we want to put database information on a web page and then stepping through the procedures necessary to get there.  From the DreamWeaver menu, choose Insert / Data Objects / Dynamic Table.

(“Dynamic” is just DreamWeaver’s way of saying “the information that is displayed comes from (or will be inserted into) a database”.)

A window will pop up checking that you have completed all the steps necessary to get to this stage. If your site is set up correctly, everything in the list should be ticked off except for the last step, Create A Recordset. (“Recordset” is Dreamweaver’s term for a MySQL query). Click on that last link now.

The window that now appears asks you to name the recordset. The name you use can be anything, so long as it follows the naming convention rules. The window then asks for for a connection, which you must create: click on Define, and then, in the window that appears, New.

You should now see the MySQL Connection window. Again, the name for your connection can be anything that follows the rules.

For the MySQL server name:

• If you are on a web host that provides MySQL, the server name should have been provided to you: it is usually the same as the domain name, but does not have to be.

• If you’re trying to connect to MySQL running  on your own machine (i.e. you’re running MAMP. XAMPP or some other local server setup) the server name will be localhost. You may need to add the port number to this: if so, the full information will be localhost:8888

• If you’re in one of my classes, the server is webapp.ict.sait.ca (Note that you cannot reach this server from outside the SAIT campus).

You also need to enter your username and password for connecting to the database:

• If you are on a web host that provides MySQL, the username and password is usually the same as those that you use for FTP, but does not have to be.

• If you’re trying to connect to MySQL running on your own machine (i.e. you’re running MAMP. XAMPP or some other local server setup) the username will probably be root, with no password required.

• If you’re in one of my classes, use the username and password you have been provided with.

You’ll want to test this connection with the Test button, and then choose the database table you want to query information from. Keep with the basic query suggested by DreamWeaver for now.

That should produce a basic dynamic table on your PHP page: the data within braces and highlighted in light blue in the page shown in Design Mode is DreamWeaver’s representation of data from the database, which will not be completed until the page is seen in a browser. (You should go ahead and do that now, using File / Preview In Browser).

Now switch to Code View; you’ll also want to open up the File window at the same time.  You’ll see several things:

• The generated page uses an include, placed via a require_once function, that is given the name you provided for the connection above.

• This script is in a Connections folder; as an include, I would move it into the includes folder that should be part of your site structure.

• The PHP closes and then immediately opens again. This is inefficient: PHP lines that are next to each should be in the same tag.

• The Connections script contains the username and password to the site in plaintext: for security purposes, you should put a .htaccess file that denies visitors into the folder, preventing them from reading this file.

• The PHP code produced is long and not terribly well documented; in reality, you could write this same script in three lines of code. (DreamWeaver is doing the same thing every WYSIWYG editor does, and over coding everything).

• Once you start editing this code DreamWeaver may protest that you’re “breaking” it, meaning that the point-and-click tools I’ve shown you may no longer continue to make changes to the code.

While there are advantages to using point-and-click tools such as DreamWeaver to create database connectivity on a web page, doing so separates limits customization choices while maximizing the number of steps required to get results (“where was that option in the menu again?”). Generally speaking, hand coding a database connection is faster and creates more reliable code using fewer lines. It also allows you to actually understand what you are doing, rather than pushing buttons.

In the steps that follow, I’m assuming that you have a MySQL server, and have created a database table with information inside it.

First, we are going to write this script on a single PHP page, the same page on which we are going to show database results. While this isn’t terribly efficient for a site in which multiple pages draw information from a database, it does avoid the issue that I discussed in Creating a MySQL Connection In DreamWeaver: that of leaving your connection information “in the clear” as an unsecured include file for anyone to find.

Obviously, we must establish our database connection, draw information from a table, and translate it into data that we can use before we use the information anywhere on our web page: for that reason, I usually write the connection script at the very start of the page.

The first line of PHP code looks like this. (Note that I’m using the short version of the opening PHP tag to save a little space).

1. <? $myDatabase = mysql_connect("mysql_server", "user", "password")
2. or trigger_error(mysql_error(),E_USER_ERROR); ?>

This establishes a connection to your MySQL server, returning an error if no connection can be made. (Of course, you’d replace mysql_server, user and password with the appropriate information.)

The next line chooses the database you wish to draw information from. Again, you’d replace database with the actual name of your database.

1. <? mysql_select_db("database", $myDatabase); ?>

It is the two lines above that would typically be used in an include, as we would require every page that interacted with a database to have them. The lines that follow would usually be unique to each page.

1. <? $query = "SELECT * FROM table"; ?>

Once working with MySQL becomes familiar, you’ll find that you will spend most of your time tweaking queries.  At the very least, you’d need to replace table with the actual table name.

Now we need to execute this query on our established database connection:

1. <? $mydata = mysql_query($query, $myDatabase) or die(mysql_error()); ?>

This retrieves our data from the MySQL database table, showing the cause of the error if the process cannot be completed. The information that is brought back from a SELECT is “raw data” – we need to convert it into a state we can use with PHP. The next line does just that, transforming the first row into an array, labelling each slot in the array with the name of the column the information relates to:

1. <? $myrows = mysql_fetch_assoc($mydata); ?>

That’s essentially it. The entire code is:

1. <? $myDatabase = mysql_connect("mysql_server", "user", "password")
2. or trigger_error(mysql_error(),E_USER_ERROR);
3. mysql_select_db("database", $myDatabase);
4. $query = "SELECT * FROM table";
5. $mydata = mysql_query($query, $myDatabase) or die(mysql_error());
6. $myrows = mysql_fetch_assoc($mydata); ?>

So long as we know the names of the columns, we can print out the data from any column in the first row of retrieved data by using:

1. <?=$myrows['column_name’]?>

Note that this only works for the first row of data that is returned by our query (for example, details of just one person from a users database table). If we have more than one row of information that we wish to display, we will have to use some kind of loop to cycle through them… which will be discussed in the next entry for this series.

Recently I showed you how to create a basic connection to a MySQL database with PHP and extract information via a query. At the time, I mentioned that once you have the ability to get data from a database, you will likely spend most of your time working on the query, trying to determine what information to retrieve. The query that we started with does the opposite to that:

1. SELECT * FROM products

Phrased that way, this query retrieves absolutely everything from the table named products: every piece of data, in every column. While your database table is small, containing only a few records, this isn’t a problem… but when it grows to hundreds, thousands, or even millions of rows, the work required to fetch and display all of that information can be significant.

We can immediately start to filter the data we retrieve by adding a WHERE clause:

1. SELECT * FROM products WHERE price < 50

This retrieves complete data for each record in the products table, but only for records in which the price column has a value of less than 50. (For the sake of this example I am assuming that price is a numerical field).

We can further limit the amount of data we receive by using that very word and a value:

1. SELECT * FROM products WHERE price < 50 LIMIT 10

This query retrieves the first 10 rows that match our criteria, and only 10; very useful when creating pagination (showing the user a limited number of results, so as not to overwhelm them (or our server)),

We can limit our results to just one record by using the value of the record’s primary key:

1. SELECT * FROM table WHERE id = 5 LIMIT 1;

(Including the LIMIT 1 is somewhat redundant here; we should only have one record with a value of 5 in its id field, but leaving it in doesn’t do any harm).

You can also cut down on the information you receive by specifying that you only want the data from certain columns, and not others. For example, this statement might be useful to retrieve data for the purposes of creating some navigation:

1. SELECT id, name FROM entries

In this example, while we are back to gaining information from every row, the only data we will receive is the is id and name value in each row; other information that might be in the table, such as description, will not be retrieved.

By default, data gained from a MySQL database is listed in the order it was entered, typically meaning by the ascending value of the primary key field for each record. We can order by any other field by specifying it:

1. SELECT * FROM products WHERE price < 50 ORDER BY price

The order of your retrieved data will automatically be sorted by ascending (ASC) value: that is, the rows will be ordered by whatever first appears in the field: alphabetically (a-z) or numerically, (0,  1, 2…). Often, you will want to order products by highest to lowest price instead, in which case your query becomes:

1. SELECT * FROM products WHERE price < 50 ORDER BY price DESC

You can also chain conditions together by using AND: note that references to text values should be quoted:

1. SELECT name,description FROM products WHERE price > 50 AND color=’black’ LIMIT 10
2. ORDER BY price DESC

As you can see, MySQL is a deceptively simple language: you can create very complex queries by chaining easy syntactical rules together. What you can’t do at the moment is to make the query itself dynamic; i.e. changing the data that is returned based on user input. Right now, all of the determinants in the query are hard-coded: in the statement above, you will get information back on all records in the products table that are listed as being black and having a price greater than 50, but the user has no ability to change the ‘50’ value to anything else. We’ll tackle that feature next in this series.

The easiest way to alter a MySQL query based on user choices is to place that choice in the form a URL variable, otherwise known as a $_GET variable. For example, let’s say we wanted to give our site visitor the choice of seeing the details for products we have listed in a database table called bikes:

If we wanted just the snow bike row as a result, our MySQL query would be:

1. SELECT * FROM bike WHERE idno = 1;

But we want to make it such that the id value that we use is a variable. Keeping things simple, we’ll hard-code the links for the user into the PHP page:

1. <ul id=productlist">
2. <li><a href="?id=1">Snow Bike</a></li>
3. <li><a href="?id=2">Road Bike</a></li>
4. <li><a href="?id=3">Mountain Bike</a></li>
5. </ul>

We don’t need a page name before the question mark delimiting our GET variable as we’ll be looping back on the same page. At the top of our page, we want to connect to our database. We’ll use the same lines of PHP code we did previously:

1. <? $myDatabase = mysql_connect("mysql_server", "user", "password")
2. or trigger_error(mysql_error(),E_USER_ERROR);
3. mysql_select_db("database", $myDatabase); ?>

The change happens in the very next few lines:

1. <? if (isset($_GET['id’]))
2. { $id = mysql_real_escape_string($_GET['id’]); }
3. else { $id = 1; } ?>

We’re using mysql_real_escape_string to ensure that what we get from the URL is just a number, and not anything that will corrupt our database when we use it in a query. Anytime you are building a query based on user input, it is vital to check and double-check the variables you are using for security. (If we were using a later version of PHP we might use FILTER_SANITIZE_NUMBER_INT instead.) If $_GET['id'] isn’t set – i.e. the user has come to the page for the very first time – $id is set to 1.

Now that we have a number, we can create our query:

1. <? $query = "SELECT * FROM bikes WHERE idno = $id LIMIT 1";
2. $mydata = mysql_query($query, $myDatabase) or die(mysql_error());
3. $myrows = mysql_fetch_assoc($mydata); ?>

Assured that we are getting only one row returned from the database, we can echo it on our page. First, we’ll check that we have any response, then provide output:

1. <? if ($myrows) { ?>
2. <h1><?=$myrows['name’]?></h1>
3. <img src="assets/images/<?=$myrows['pic’]?>" alt="<?=$myrows['name’]?>"
4. title="<?=$myrows['alt’]?>" />
5. <p>Material: <?=$myrows['frame_comp’]?></p>
6. <p>Color: <?=$myrows['frame_color’]?></p>
7. <? } else { ?>
8. <h1>No product found</h1>
9. <? } ?>