| Text only | |||||
 |
 |
||||
|
|||||
WWW Information Pack
Topics covered: Uses of web-database technology, PHP, MySQL, ASP, PHPMyAdmin, creating databases, tables and fields, SQL, SELECT and INSERT queries, using Dreamweaver to connect to databases, date calculations in SQL, passing variables on the query string, relational table structures.
In Factsheet 18 we introduced the concept of interactive web pages, which allow the user to submit information to a web server and obtain a response. Although this is a useful technique, there are many more sophisticated applications of web technology that require the web server to be able to store the information supplied by the user in a database. Similarly, it is sometimes necessary for web sites to be able to retrieve information dynamically from an underlying database.
For example, if you need to make a large listing of publications or journal articles available on the web, it can be more efficient to store this list in a database, and generate the web pages from that database, rather than manually producing many lengthy conventional web pages in plain XHTML. The use of a database for this task confers the following advantages:
Figure 19.1 - Top: this site maintains an on-line database of its published articles, which can therefore be easily searched and listed in different ways; Bottom: an underlying web-based administration system allows new articles to be added into the database easily. Note that each article is made up of several distinct items (or database fields). Naturally, this administration system is password-protected, so that only valid site administrators can use it.
The use of web-database technology therefore not only improves the efficiency with which you can generate web-based listings of information, it also provides a convenient method by which non-technical users can be enabled to keep that information up to date.
Other possible applications of web-database technology include:
Figure 19.2 - A web-based listing of events, generated dynamically from an underlying database. The use of web-database programming for this system enables the events to be displayed automatically in date order, and to be filtered according to their location. Old events will automatically disappear from the listing once their finishing date has passed. New events can be added by non-technical administrative staff using simple web-based forms, so there is no need for them to learn Dreamweaver or XHTML coding.
Database-driven web pages are generated dynamically on the web server, whenever a user requests to view them (Figure 19.3):
Figure 19.3 - The processes that underlie the generation of a database-driven web page
When a remote user's request to view a web page (1) is received by the hosting web server, rather than simply delivering a "static" XHTML page, the server instead runs a script (or program). The task of this script (2) is to connect to a database that is running on the web server, and retrieve the appropriate set of data from it. Having done that, the script must then (3) format the retrieved data into XHTML, ready for presentation back to the user. Finally (4) the completed XHTML web page is sent back out to the remote user. This entire process usually happens so rapidly that the remote user may be unaware that they are viewing a database-generated page.
At the University of Aberdeen (as on many other UNIX and Linux-based web servers) the most convenient scripting language to use for web-database interaction is PHP, (see Factsheet 18) and the database itself is called MySQL. Both PHP and MySQL are freely-available Open Source technologies (see Factsheet 7 for a discussion of Open Source software).
On Microsoft Windows-based web servers, the same principles apply, but different tools are used. The scripting language is likely to be Active Server Pages (ASP), and the database is often Microsoft's commercial product SQL Server. It is also possible to use Microsoft Access databases, but Microsoft themselves admit that Access is not really sufficiently robust for web use - especially if many users need to use the site simultaneously.
The University of Aberdeen provides a devolved web-based administration service which allows individuals to create, delete or modify MySQL databases on the central UNIX computing system. This service is available at www.abdn.ac.uk/local/mysql/mysqladmin/, but you must first register to use it by filling out form RF21 (linked from the above page). Once you have registered for this service, you can log in and create a MySQL database, the name of which will be prefixed with your own user-id and an underscore, e.g.: com184_database1. You will need to specify a password (which should be different from your main University computing account password). Click the button, and then click the button on the following screen, to create your new database.
Figure 19.4 - Creating a new MySQL database with the University of Aberdeen's administration service
MySQL is designed to be a web-based database. Unlike Microsoft Access (which is designed for desktop use), MySQL does not have a user-friendly "front end" data entry interface. Fortunately, an excellent tool called PHPMyAdmin has been developed, which enables you to work with your MySQL database (e.g. to create tables and add data) via a web browser.
Log in to PHPMyAdmin at www.abdn.ac.uk/local/mysql/phpmyadmin/ using your newly-acquired database name and password (not your central computing account ones). You will see the name of your new database in the blue left-hand part of the window. Click once on that name, and you will be prompted to create a table in your database (Figure 19.5).
A database table is similar to a table of data in a spreadsheet - it is an organised framework within which you can store items of data. In the above example, we are creating a table called events which will be used to store a list of events. Each event will be made up of five separate fields (individual pieces of information).
Figure 19.5 - Creating a new table in a MySQL database, using PHPMyAdmin
The next screen requires you to specify the names and details of the fields that you want to store for each item in the events table (Figure 19.6).
Figure 19.6 - Specifying the fields to be used within a table,
in PHPMyAdmin.
(Note that some less important columns in this table have been cropped out
of the image to save space)
In the above example, we have created five fields, of four different data types (as specified in the Type column):
We have specified that each event will have a title (a string of up to 200 characters long), a date (to be stored in MySQL's special date format), a location and a description (both of which will be stored as text, without a strict limit on the number of characters).
Importantly, we have also created a field called id which is an integer number of up to 6 digits in length. As we add items to the database, this value will auto-increment (i.e. count upwards, so that each new event is given a unique number). This field will therefore serve as a unique identifier for each event - in database terms this is called a primary key.
MySQL allows you to choose from a number of different data types - for a review of these types see: www.mysql.com/doc/en/Column_types.html or www.htmlite.com/mysql003.php. In addition, MySQL requires you to specify whether each field may be null (i.e. not set to any value). In many cases you may wish to enforce that a database record must contain some value for a particular field (e.g. the surname in a list of employees, or a field that will serve as a primary key), in which case you should set that field to not null. In the current example, we will leave all fields set to the default not null state in PHPMyAdmin.
Click on the button and (provided you have made no errors in the field details) the events table will then be created. The names of all of the tables in your database are displayed in the left-hand blue side-bar of PHPMyAdmin, beneath the database name. You simply click on the name of a table in order to work with it.
PHPMyAdmin provides a horizontal row of navigation tabs from which you can find tools to data into your table, the data in your table, data, or change the table's (Figure 19.7). Try inserting a couple of fictitious events into the table... remember that you do not need to specify a value for the id field because that field automatically increments. Note that the MySQL format for dates is yyyy-mm-dd, e.g. 2003-09-23
Figure 19.7 - Inserting a new item into the events table, using PHPMyAdmin
Now that you have a MySQL database, with some data inside it, the next task is to write a PHP script to connect to this database, retrieve the data, and display it in a web page. The following description provides a simplified overview of this process. For more detailed discussion, see the references at the end of this Factsheet. (Note also that you can use Dreamweaver to produce your PHP page automatically for you - see Section 19.6).
The most important point to remember is that whenever you retrieve data from your database (or insert new data into it) you will be using commands written in SQL (Structured Query Language). SQL is a very logical and powerful standard language that is used for interacting with relational databases. (See www.sql.org for more details.) So we will be using PHP to construct SQL statements, which are then used to interact with the database.
To get started, we create a new plain text file and give it a filename ending in .php .
In this file we use PHP to create a few variables that contain the data necessary for connecting to our database. Obviously, you need to substitute your own database name and password into the first two lines below:
<?php
$database_name="com184_database1";
$database_password="x94RtU5l";
$database_host="mysql.abdn.ac.uk";
Now the line of code that actually opens a connection to the database:
$conn = @mysql_connect($database_host, $database_name, $database_password);
And we check that the connection has succeeded - if not we print an error:
if(!$conn){
echo "Couldn't connect to database server";
exit();
}
Now we construct an SQL command, which will tell the database to retrieve the full content of our events table:
$sql = "SELECT * FROM events ORDER BY date ASC";
Next we actually implement this SQL query on our database, to produce a set of results:
$results=mysql_db_query($database_name,$sql,$conn);
We create the header elements of a web page, in preparation for displaying the results:
echo "<html><head><title>Events
listing</title></head><body>";
echo "<h1>Unmissable events</h1>";
Now we loop through every record in the results, and for each item, we print its details:
while ($output=mysql_fetch_array($results)) {
echo "<p><b>Event title:</b> ". $output[title];
echo "<br /><b>Date:</b> ". $output[date];
echo "<br /><b>Location:</b> ". $output[location];
echo "<br /><b>Details:</b> ". $output[description];
echo "<hr /></p>";
}
Finish off the footer elements of the web page:
echo "Be there, or be somewhere else.</body></html>"
Finally, close the database connection and the PHP code:
mysql_close($conn);
?>
Now, when we publish and view the above web page, if all has gone well we should see a listing of events drawn directly from our MySQL database:
Figure 19.8 - A web page written in PHP which has connected to a MySQL database in order to retrieve a simple events listing
Note that in this example, the MySQL database name and password have been stored within the PHP script in plain text. This means that it is technically possible for anyone gaining unauthorised access to your PHP script to read these details. Armed with this information, they could modify or delete any of the information within your database. You therefore need to take care to ensure that your PHP script cannot be read by unauthorised people. The central web publishing process at www.abdn.ac.uk/local/publish will take the appropriate security steps to ensure that this can not happen - so you should be sure to publish your pages via this approved method (Factsheet 10).
If your PHP script only needs to read information from a database (rather than inserting or modifying data), then you can avoid the need to include your database password in the script altogether, by using a special "public" user account. This account has no rights to modify data on the MySQL server, but can be given read-only permission to your database.
To make use of this account, log in again at www.abdn.ac.uk/local/mysql/mysqladmin/ , select the database that you want to work with, and then select the option to . Click the button, and then click the button to grant read-only access to your database to this public account. Now you can remove the $database_password statement from your script, and amend the connection line to:
$conn = @mysql_connect($database_host, "public", "public");
If the above "raw" PHP code is too intimidating, then it is also possible to use Dreamweaver MX (but not earlier versions of Dreamweaver) to produce a database-driven web page. The advantage of using Dreamweaver MX for this task is that it is quick, easy and visually helpful. The negative side is that Dreamweaver's PHP code is not commented, and may be hard to decipher if you should need to undertake more sophisticated modification work on it in the future.
Firstly, you must use Dreamweaver's Site tool to configure a web site and specify a Testing Server (the use of Dreamweaver as a site management tool is covered in the DISS IT Training Unit's Further Macromedia Dreamweaver course - see www.abdn.ac.uk/diss/training/). Under the tab, select (Figure 19.9).
Figure 19.9 - Configuring your University of Aberdeen web account as a Testing Server in Dreamweaver MX. In this example, electing to connect to the server by FTP means that these settings should also work from outside the University network.
Having done this, create a new document in Dreamweaver, but in the dialogue box, select .
Then open the tab of the window (using the menu command: ). In this window, click on the button to create a MySQL databsase connection (Figure 19.10). Be sure to enter your database name and password here:
Figure 19.10 - Creating a new connection to your MySQL database in Dreamweaver MX
If this works successfully, then Dreamweaver will display a new connection object in the tab of the window, and you will be able to expand it to see the tables and fields inside your MySQL database (Figure 19.11):
Figure 19.11 - A MySQL database connection, expanded to show its constituent tables and fields, in Dreamweaver MX
At this point, you can click on the tab of the same window. Use the button to create a new from your database connection (Figure 19.12). (This is, in effect, equivalent to querying your database with an SQL SELECT query as we did in Section 19.5 above.)
Figure 19.12 - Creating a Recordset in Dreamweaver MX
Dreamweaver MX will then automatically insert the PHP code for this query into your web page.
Having made your query of the database, you can import the results into your web page by dragging-and-dropping the relevant Recordset fields from the window (Figure 19.13):
FIgure 19.13 - Importing the results of a database query into a web page, by dragging and dropping the relevant fields from the window to the web page window, in Dreamweaver MX
Finally, you can select you entire table (in the web page window) and define it as a , using the menu command (and selecting ). This tells Dreamweaver that you want to loop through all records from your database query, and display them one after another.
Now, when you preview your web page in the browser (with the key), you will see that your entire set of records has been displayed (Figure 19.14). You can then concentrate on formatting them neatly, using Dreamweaver's visual formatting tools.
FIgure 19.14 - The results of previewing the above Dreamweaver MX database-generated page: the entire set of records from the database has been listed.
For more details about using Dreamweaver MX as a PHP-MySQL development tool, see www.devshed.com/Server_Side/PHP/DWMXandPHP/
Writing data into a MySQL database is simply an extension of the techniques so far discussed. We will discuss how to do it in "raw" PHP code, although it can also be done in Dreamweaver MX (see the URL immediately above).
From Factsheet 18, you should be able to construct a simple web form which sends the user's input data to a PHP script on the server.
If your input form contains four text entry fields (title, date, location and description), then the PHP script will receive the user's input data as $_POST['title'], $_POST['date'], $_POST['location'] and $_POST['description'].
Your form-handling PHP script can therefore take these four pieces of user input data and build then into an appropriate SQL statement that will INSERT them into the database:
// assuming we have already included
// the code to open a connection to the database,
// as in the previous example, then...
$sql = "INSERT INTO events (title,date,location,description) VALUES ('".
$_POST['title'] ."','". $_POST['date'] ."','". $_POST['location']
."','". $_POST['description'] ."')";
// now run this query, as before:
$results=mysql_db_query($database_name,$sql,$conn);
// and give the user some feedback:
echo "SQL string is ".$sql;
if ($results) {
echo "<p>New item entered successfully!";
}
// then insert closing code as before
The syntax of the SQL INSERT statement above is quite tricky, due to the large number of single and double quotes, and because this statement is being built up progressively by concatenating various PHP variables. Nevertheless, if you achieve this successfully, then the PHP script should respond as below (Figure 19.15):
Figure 19.15 - Top: a very simple data entry form which sends user input data to a PHP script. Bottom: the output of the PHP script after it has successfully added this item to your events database.
Note again that there is no need to include a data entry box in your form for the id (primary key) field in the events table, because this value automatically increments with every new addition to the table.
The PHP-MySQL coding techniques described above provide a basic foundation upon which you can build extremely sophisticated web-database applications.
As a good starting point, you might like to modify the PHP code in the examples above to improve the visual display of the results.
You should also extend your knowledge of SQL commands, so that you know how to retrieve different sets of data, and how to delete and modify specific database records. SQL is an extremely powerful and flexible language, and you can use it to process your data in many different ways:
You could limit your events listing to display only those events with the word "festival" in the title or description, by using the SQL string:
SELECT * FROM events WHERE title LIKE '%festival%' OR description LIKE '%festival%'
This could form the basis of a keyword search function, in which you use PHP to capture a user's input search word, and then place it within a SQL "LIKE" clause.
You could improve the appearance of the dates in the above example by asking MySQL to convert them automatically to long format ("Wednesday 24th December 2003"), using the command:
SELECT *, DATE_FORMAT(date, '%W %D %M %Y') AS prettydate FROM events
...and then using the command echo $output[prettydate]; in your subsequent PHP results listing.
You can also perform complex date calculations within SQL statements. The following SQL command will retrieve only those events that are taking place within the next 60 days:
SELECT * FROM events WHERE to_days(date) - to_days(NOW()) < 61 AND to_days(date) - to_days(NOW()) > 0
You will need to consult a manual of MySQL commands in order to understand such date calculations - see for example: www.mysql.com/doc/en/Date_and_time_functions.html
If you want to be able to modify the details of an event in the above example, you will need to build a web-based update form. This is too complex and lengthy a process to describe fully here. However it involves an important technique by which references to individual database items can be passed between pages:
In your main listing of events, create a link from each event item to a PHP-based update form (we will call it update.php). But, for each item in the list, insert a reference to the id [primary key] field of that event onto the end of the link target:
// PHP code to create the link to the update form.
// Place this code inside the "while" loop used in Section 19.5
above:
echo "<br /><a href=\"update.php?id=" . $output[id]
."\">modify this item</a>";
So the link from event number 43 in the list will be directed to update.php?id=43
This additional piece of data (?id=43) on the end of the URL is called the query string, and it allows your update.php script to "know" which database item you wish to modify. When the update.php script is run, the id value is available for it to use, as the variable $_GET['id'].
Armed with this piece of information, your update.php script can retrieve the existing information for that specific event from the database, and populate the fields of a form with it (ready for you to review and update that information). Ultimately, your PHP script will need to submit an UPDATE command to the database to modify this specific record.
So in PHP, the array $_GET is used to access variables that are passed between pages on the query string, whilst the array $_POST captures data that is submitted via a web form (with method="post").
If you wish to organise your events into a small number of specific categories (e.g. festivals, parties, lectures, open days...), then this is perhaps best done by adopting a relational database structure.
You would maintain a table of events, and a second separate table of categories. Each event in the events table would then contain a numerical reference across to the appropriate category in the categories table:
Figure 19.16 - An illustration of the logical relationship between the data in two linked database tables. The field in the table is a reference to the numerical primary key value ( field) of the corresponding category in the table.
The use of such multi-table relational database structures helps to safeguard the consistency and logic of your data, by preventing unnecessary (and error-prone) duplication of information in your data tables.
In such a scheme, you could retrieve just those events which have been categorised as parties, using the following SQL command:
SELECT * from events, categories WHERE events.category_ref=categories.id AND categories.name='party'
More advanced web-database applications will quite commonly make use of 20 or 30 linked data tables to structure their data. This enables them to capture the complex logical relationships between, for example, students, their enrolment upon courses, their assessment grades for individual tasks within a course, and the tutors for those courses. At this level of complexity, you will have earned your credentials as a professional web application developer!
The MySQL database is explained in great detail (but also very clearly) in the excellent book MySQL by Michael Kofler, published by Apress 2001, ISBN 1-893115-57-7. This book also provides detailed information about SQL and the use of PHP to connect to MySQL.
There is also a good section of PHP-MySQL connectivity in the book Beginning PHP4 by W. Choi et. al., published by Wrox, 2000, ISBN 1861003730
For on-line information about web-database development, see:
www.devshed.com (especially the MySQL
section)
There is a good article about date arithmetic with MySQL at
www.devshed.com/Server_Side/MySQL/DateArithmetic/page1.html
O'Reilly's database section at databases.oreilly.com
provides a lot of information about SQL
(but don't buy any books about Transact-SQL or PL/SQL, because these are two
commercial "dialects" of the SQL language that are specific to Microsoft
and Oracle databases respectively).
There is a good general SQL tutorial at www.1keydata.com/sql/sql.html.
MySQL is freely available from www.mysql.com
PHPMyAdmin is freely available from www.phpmyadmin.net
 Contents page | Next Factsheet  |
