Scripting with PHP for MySQL ~ the Life Events Database

The Life Events Database (LEDB, for short) is a database of events. There are 4 tables (events, images, users, logs). Each event in the events table has a date, short description, full description, primary image filename, and other fields that describe the type of event, access needed for viewing, etc. In the case an event has multiple images, secondary image filenames are stored in the images table. Users, in addition to the typical name info, username, password & email, also have access level (what they can see) and privilege (what they can do). Account activity is logged in the logs table.

When scripting for a database, in the simplest form, the thought is: what do I need from the database and how will it be presented. Here, I'm presenting a list of events in chronological order, checking the user's access level against the event's access level, checking the images table to see if there are secondary images, and presenting it all in a table format.

Why not make everything dynamic? Even the links at the top of the page can be customized for the user. Here it is necessary to dynamically write the links to move between the 33 records found. In the image above in the top left and right corners, it shows "Prev 10" and "Next 3 ". If we were viewing 10 of 10 records, it would not be necessary to show those links.

Within the body tags of my HTML is a single line of code:

<?php
display_results($user,$user_access,$record_start,$num_records,
   $where_string,$criteria_string,$sortorder);
?>

Except for this function call, all other PHP code preceeds the HTML. The code includes the display_results function, which writes the entire contents of the page, and code that insures all the variables that are sent as function parameters have values.

$user and $user_access gets their values from the user's login session variables (if there was no login, defaults are provided). $record_start is either 0 or a multiple of $num_records which is currently set at 10. $sortorder is ASC (ascending) by default, but can be changed using a form within the Date heading cell.

Both the $where_string and $criteria_string are built from arrays using form variables passed by the Search page. The $where_string follows the WHERE keyword in the SQL select statement sent to the database. Only the $criteria_string isn't used in the SQL statement, but is there to provide information to the user about the search type submitted.

(more coming soon)