You are here: Home

Need Acrobat Reader for PDF documents?

PHP scripting and Structured Query Language

PHP

Here is a piece of code from a Sites4Doctors practice website. It generates the ’You are in:...’ route through the folders that appears just below the photo-montage at the top of every page. Please don’t take this too literally: by the time the code has been messed about to fit it in a web page, some errors may well have slipped in. Note also that the layout is neither standard nor important to the script’s function - this is the way I and many other programmers choose to lay our code out so that we can read it. Other programmers use quite different conventions. White space means nothing - curly brackets and semi-colons are absolutely critical.

// *** Build and display track string for ’You are in’ bar ***

if ($query_folderID > 0)

{

$query = "SELECT * FROM tblFolders_$site_id WHERE id = $query_folderID";

$result = mysql_query($query, $link);

$num = mysql_num_rows($result)

or die("<p>$query<br />No current folder found.</p>");

$row = mysql_fetch_array($result, MYSQL_ASSOC);

extract($row);

mysql_free_result($result);

}

else

{

if ($query_pageID > 0)

{

$query = "SELECT * FROM tblPages_$site_id WHERE id = $query_pageID";

$result = mysql_query($query, $link);

$num = mysql_num_rows($result)

or die("<p>$query<br />No current folder found.</p>");

$row = mysql_fetch_array($result, MYSQL_ASSOC);

extract($row);

mysql_free_result($result);

$query = "SELECT * FROM tblFolders_$site_id WHERE id = $folderID";

$result = mysql_query($query, $link);

$num = mysql_num_rows($result)

or die("<p>$query<br />No current folder found.</p>");

$row = mysql_fetch_array($result, MYSQL_ASSOC);

extract($row);

mysql_free_result($result);}

}

$track_string = $folderName;

while ($parentID > 0)

{

$query = "SELECT * FROM tblFolders_$site_id WHERE id = $parentID";

$result = mysql_query($query, $link);

$num = mysql_num_rows($result)

or die("<p>$query<br />No current folder found.</p>");

$row = mysql_fetch_array($result, MYSQL_ASSOC);

extract($row);

$track_string = "<a href=’page.php4?folder=$id’><u>".$folderName."</u></a><strong> > </strong>".$track_string;

mysql_free_result($result);

}

}

SQL

The other language I had to learn - in fact I had to learn it for my NHS work and simply switch to a slightly different dialect for Sites4Doctors - is SQL (Structured Query Language), used to get data in and out of MySQL databases (and, come to that, for creating databases and their tables).

$query = "SELECT * FROM tblFolders_$site_id WHERE id = $query_folderID";

This basically tells the database management system to get from the table called ’tbl_Folders_’ followed by the id number of the site ($site_id’ is a variable appended to ’tblFolders_’) all the fields (or columns, depending on which school of database jargon you favour) from all the records (rows) where the field (column) called ’id’ is equal to the value which has previously been in the variable called ’$query_folderID’. If the site id is 197 and the folder id is 9, PHP will cleverly substitute the values for the variable names (identified by the dollar-sign prefix) and send the following to MySQL:

$query = "SELECT * FROM tblFolders_197 WHERE id = 9";

Here is a much more complex query working with several tables at once, written on several lines only for clarity:

$query = "SELECT tblSites.id AS ID, tblSites.setupInvoiced AS startDate,tblSites.clientOrg AS Name, tblSites.domainNameRegistered AS domainNameRegistered, cmsUsers.lastName AS Surname, cmsUsers.firstName AS Forename,cmsUsers.email AS Email, tblSites.phone AS Phone

FROM cmsUsers

INNER JOIN tblSites ON tblSites.id = cmsUsers.siteID

WHERE cmsUsers.mainContact=-1

ORDER BY ID";

Earlier I mentioned ’the superb MySQL relational database management system’. The ’relational’ bit means that you can set relationships between tables, either before work starts or in individual queries. In this example, the ’INNER JOIN’ sets a relationship between tables called ’cmsUsers’ and ’tblSites’ based on the field (column) ’siteID’ in ’cmsUsers’ containing the same value as ’id’ in ’tblSites’ - only records that conform to this rule will be retrieved. The query tells MySQL to get the fields specified in the first line - note that the field-names have to be prefixed by the table names to tell MySQL where they are - eg: ’tblSites.id’). The AS bit says that they will be delivered under the new names ’ID’, ’Surname’ etc. The ’WHERE’ clause tells the system only to execute the previous instructions when the field ’mainContact’ in ’cmsUsers’ is equal to -1, which you may be surprised to hear means ’True’ (zero means ’False’).

So what this query does is to get the site id, the date on which site setup was invoiced, the name of the client organisation, the domain name registered for the site, then the last name, first name, email address and phone number of the person identified as the main contact for the site. PHP then goes on to use this data to compile a web page that lists every site with its ID number and web link, and the name and contact details of the contact person - a list that is guaranteed to be up-to-date every time I access it, a valuable administrative tool for me.

Personal site for Paul Marsden: frustrated writer; experimental cook and all-round foodie; amateur wine-importer; former copywriter and press-officer; former teacher, teacher-trainer, educational software developer and documenter; still a professional web-developer but mostly retired.

This site was transferred in June 2005 to the Sites4Doctors Site Management System, and has been developed and maintained there ever since.