We continue the study of how to write three-tier dynamic Web applications using PHP and MySQL.
Pagination is the process of displaying lists one page at a time, with navigation between (adjacent) pages. It requires maintenance of an offset variable (which page is to be displayed next) and, in this case, the use of some MySQL-specific features, discussed below.
Pagination requires the following changes to the list-processing code above.
item_list.phpThis script must be modified to accept an offset from the URL, and to pass the offset, the previous and next offsets, and the total number of items, to the template, in addition to the item list.
define("ITEMS_PER_PAGE", 10);
$offset = $_GET['offset'];
if (! isset($offset)) $offset = 0;
list($item_list,$num_items) = get_items($offset, ITEMS_PER_PAGE);
$smarty = new Smarty;
$smarty->assign("item_list", $item_list);
$smarty->assign("offset", $offset);
$smarty->assign("items_per_page", ITEMS_PER_PAGE);
$smarty->assign("num_items", $num_items);
$smarty->display("item_list.tpl");
get_items()This function must be modified to return an array containing both the list of items on the current page and the total number of items found.
function get_items($offset, $items_per_page) {
$connection = mysql_open();
// Construct query, preparing for pagination
$query = "SELECT SQL_CALC_FOUND_ROWS * from items " .
"ORDER BY id " .
"LIMIT $offset, $items_per_page";
// Get list of items on this page
$results = mysql_query($query, $connection) or showerror();
// Get total number of items
$result2 = mysql_query("SELECT FOUND_ROWS()", $connection) or showerror();
$row = mysql_fetch_array($result2);
$num_items = $row[0];
// Copy result set to array
$items = array();
while ($row = mysql_fetch_array($results)) {
$items[] = $row;
}
mysql_close($connection) or showerror();
return array($items, $num_items);
}
item_list.tplThis template must be modifed to include previous and next links.
{* Show current page of items *}
<ul>
{foreach $item_list as $item}
<li><a href="item_detail.php?id={$item.id}">{$item.summary}</a></li>
{/foreach}
</ul>
{* Show links to previous/next pages *}
<p>
{if $offset == 0}
Previous
{else}
<a href="item_list.php?offset={$offset-$items_per_page}">Previous</a>
{/if}
{if $offset+$items_per_page >= $num_items}
Next
{else}
<a href="item_list.php?offset={$offset+$items_per_page}">Next</a>
{/if}
Study this database-backed
guest book application with paging. This application extends the
pagination pattern above. It modifies the previous guest book version by
presenting results one page at a time, with previous and next links to get
to the previous or next page of results. In addition to keeping track of
offsets, it must also keep track of user-entered name and
email queries.
There are several ways in which pagination can be implemented. This
implementation uses MySQL-specific language features. Unfortunately, these
features are not in standard SQL. In particular, MySQL allows the
LIMIT clause in SQL queries. The first argument of the
LIMIT clause is the offset (from the start of the result set) and
the second argument is the number of rows to be returned in the result set.
The script results.php maintains a variable $offset that
indicates which page of results is to be displayed. Updated values of
$offset are passed in the query part of the previous and next
links. The SQL_CALC_FOUND_ROWS element in the SELECT query allows the total
number of rows in the result set to be computed in a separate, efficient
query using the FOUND_ROWS() function.
Note that the application runs the complete query again each time a new page is requested. This has the advantage that any concurrent updates will be shown when the next page is displayed. It has the apparent disadvantage of being inefficient, but there is no convenient way to avoid this problem with the stateless HTTP protocol.
An alternative MySQL-specific solution is to omit the LIMIT clause
in the SQL queries, to return all results, and to use the MySQL
function mysql_data_seek($results, $offset), which sets the
result cursor so that the next row returned is the one at the given
offset. This is less efficient because all results must be (implicitly)
returned before selecting the ones required.
A database-independent solution is to return all the results as before,
create a PHP array $rows, say, containing all the results, and to
use the PHP array slicing function
array_slice($rows, $offset, $length),
but this is even more inefficient.
Exercise. Add page number links between the "Previous" and "Next" links, so that it is possible to jump directly to a particular page of results.
Hint. The best way to do this seems to be to leave the PHP script unchanged, and to modify the Smarty 3 template to display a list of page number links between the "Previous" and "Next" links as follows:
{for $page=1 to ceil($num_items/$items_per_page)}
<a href="item_list.php?offset=...">{$page}</a>
{/for}
Managing dates and times in computer programs is very complicated. There are different calendars, different time zones, different daylight savings rules, leap years (and leap seconds), and many different implementations. Here is a suggestion for one way to manage dates and times with PHP and MySQL, based on representing epochs (time instants) as Unix timestamps in PHP and as SQL timestamps (which are completely different) in MySQL, and performing date/time calculations in PHP (despite the extensive date/time functions available in MySQL).
An instant of time (such as 6:00pm on 15 May 2003) is called an epoch. Epochs are represented in PHP as Unix timestamps (the integral number of seconds since 1 Jan 1970) and in MySQL as visual numerical encodings. For example, the epoch at 21:30 on 15 May 2003 is represented by the Unix timestamp 1052998200 and by the MySQL timestamp 20030515213000 (or possibly by "20030515213000"). So it's necessary to convert Unix timestamps to MySQL timestamps when inserting rows into MySQL tables and to convert MySQL timestamps to Unix timestamps when retrieving rows from MySQL tables. These two conversions can be done in PHP as follows:
$epoch = time(); // current time
$insert = "insert into items values (..., from_unixtime($epoch), ...)";
...
$query = "select ..., unix_timestamp(time) as time, ... from items where ...";
...
while ($row = mysql_fetch_array($result)) {
$epoch = $row["time"]; // stored time
...
}
Here, function time() (also used below when discussing
cookies) is a PHP function that returns the current epoch ("now") as a Unix
timestamp. Table items has a column time, whose
type is timestamp. Alternatively, you could define the column
time to have type bigint and avoid the two
conversions completely. However, this would make it impossible (or at
least more difficult) to use any of the extensive MySQL date/time
functions if you wished to do so.
An advantage of thinking in times of epochs is that it's easy to add a number of seconds to an epoch to move forward in time and to subtract two epochs to find the difference between them.
That leaves the problem of converting more familiar forms of dates into Unix timestamps on input and converting Unix timestamps into readable forms of dates on output.
Input can be handled by the amazing PHP function
strtotime(string). This function takes a string
representing a time and returns the corresponding Unix timestamp. Examples
of possible arguments to strtotime() are "now", "tomorrow",
"+2 days" (from now), "1 week", "20 May" (this year), "20 May 2003",
"2003-5-20", or "20 May 2003 8:30pm", "tomorrow 22:00", and similar
combinations. Wow! Other useful input functions are mktime()
for converting numerical representations of dates to timestamps and
checkdate() for checking that numerical dates are meaningful.
See the online documentation of these functions for details.
Output can be handled by the PHP function
date(format,timestamp) which uses the
string format to return a readable string
representation of timestamp. If the second argument is
omitted, the current time ("now") is used.A similar output function is
strftime(). See the online documentation of these functions
for details.
Converting a number of seconds (e.g., the difference between two Unix timestamps) is most easily done by hand using simple arithmetic.
This simple event
management program illustrates these techniques
and a possible format for function
date() in a simple application that enters and
displays events, the time of each event, and the remaining
time to each event. The data model used is defined in file events.sql.
There are two approaches to the simpler problem of representing and processing dates when times within dates are not required.
Example. The program age provides a computes a person's age from their birthdate using PHP only.
It accepts a birthdate as input and displays an age in years as output. It
converts the birthdate to a Unix timestamp, then constructs
DateTime objects from the birthdate and now, then computes the
difference between these two DateTime objects as a
DateInterval object, and then extracts the number of years
from the DateInterval object.
Note that you could store the Unix timestamp in a database table (a) either
as a bigint (b) or as a MySQL date after conversion using the MySQL
function from_unixtime(). (See below.)
Note that you can't just represent the difference between two dates as an integral number of seconds, because the same number of seconds represents different numbers of years, months, days depending on the starting and ending dates.
MySQL stores dates, datetimes and timestamps differently. See section 11.3.
Date and Time Types of the manual for details. In particular, dates
are normally stored as 10-character strings of the form 'yyyy-mm-dd'.
The MySQL function
from_unixtime() will convert a a Unix timestamp (e.g., one
returned by function time() or strtotime()) to a
10-character string of this form to be stored in a column of type
date. Users can also be asked to enter dates into forms
in this form directly (provided input validation is then done).
See section 3.3.4.5 Date Calculations of the manual for details of how to compute an age in years from a birthdate and how to compute a lifespan from a birthdate and a deathdate.
Uploading, storing, retrieving and displaying images is a little complicated. OK, using raw PHP, it's very complicated. There are four main tasks: uploading an image from a form using PHP, resizing the image if necessary, storing the image (and its metadata) in an SQL database, and retrieving and displaying the image.
Images are selected using a form element input with
type="file". This form element may specify a size in the
same way as an input element with type="text". Before this
element, there must be a hidden form element that specifies a maximum image
size. This value should be chosen carefully; at least it must be no larger
than images that can be stored in the database. The form element itself
must use method="post"and must have an
enctype="multipart/form-data" attribute.
The image uploaded from the form is accessed in a PHP script from the
$_FILES global array, using the name of the file element in the
form. This image is actually a (complex) associative array, with a
content ("tmp_name"), a size and a name. The uploaded content
can be stored in a permanent file for future access. From the image
content, the image size can be extracted with the function
getimagesize(). This is also an associative array with several
useful components.
Carefully study the files getDetails.html and
showDetails.php of this file
uploading example to see how all this work.
Tricky. Provided in libraries, not in PHP itself. PHP has a GD library
that can resize JPEG files but not other image file types. The main ideas
for rescaling images are incorporated into the guestbook application with
images below. It resizes all uploaded JPEG images to fit into a 300x300
rectangle, preserving their shapes.
The key library function used here is imagecopyresampled(),
described in the PHP
documentation.
You can't just store the image data itself in a database; you also have to
store the image's mime type (jpeg, gif, png, etc.),
name and dimensions. The image data must be stored in a blob
(maximum size 64KB), mediumblob (4MB), longblob (4GB). See file messages.sql
from another version of the guestbook example.
Before storing the image data or image name, you have to call the function
addslashes() to protect these values against SQL-injection
attacks.
See function addEntry() in file includes/defs.php
of the guestbook example to see how images and their metadata should be
inserted into the database. This function also considers the possibility
that no image was uploaded.
This has to be done using two SQL queries. The first query returns the image name and size. If the size is empty, no image was stored and hence no image needs to be displayed. Otherwise, the PHP script (or Smarty template) outputs an image element of the following form at an appropriate place in the generated HTML document:
<img src="getImage.php?id=id" width="width" height="height" alt="imagename">
Here, the src attribute of the img element is a PHP
script instead of a static HTML page. The width/height attributes and the
image name attributes of this element were retrieved using the previous
query.
Script getImage.php retrieves the image with the given id from the database
using a second SQL query. It also retrieves the mime type of the image.
The script must then generate an HTTP response using PHP header()
function calls, specifying the image size, type, name and content. (Note
that a browser may thus need to make several nested HTTP requests to
render a single page. This is why rendering a page with many images is
relatively slow, even if the images are retrieved from static HTML pages.)
See this guestbook with images example (zipped archive). This version of the guestbook example allows users to optionally upload an image with each message. It uses the method described above with almost no change.
You should be able to use the methods
process_uploaded_image_file() and resize_image_file()
from file includes/defs.php pretty much without change in your own
applications. The other functions in file includes/defs.php will
need to be adapted to each application.
The actual layout of the images on the page is specified using a stylesheet.
The stylesheet floats images to the right margin, wraps message text around
images, and starts each new div and h1 element below the
previous (right-floated) image.
An alternative approach is to store images in the file system on the server, and store URLs that reference the images in the database. It's still necessary to store metadata for each image in the database. And it's necessary to be very careful with naming conventions on the server to be able to access images and to avoid over-writing images.
In more compex examples, it is good practice to download thumbnail images by default, and make these links to larger versions of the images. All image scaling should be done once only, and not every time an image is downloaded, as scaling is an expensive process.
It may be simpler to do image processing with the ImageMagick package, but this is not installed by default with PHP and is not currently included in our implementation of PHP.
It may also be simpler to use class.upload.php, but I have not investigated this myself yet.