Appendix A: Supporting Functions

Throughout this book, a series of functions are used, particularly when it comes to database access. Because these functions are not intrinsically tied to any of the chapters and not really the purpose of the book, they are covered here. I have also included some brief code snippets that I believe to be of use while working on the topics covered here.

common_db.php

These functions are used to facilitate database access, MySQL in particular. The first few are either similar or identical to functions presented in other PHP books from Wrox.

 <?php   $dbhost = 'localhost';   $dbusername = 'example';   $dbuserpassword = 'example';   $default_dbname = 'example';   $MYSQL_ERRNO = '';   $MYSQL_ERROR = '';   $default_sort_order = 'ASC';   $default_order_by = 'uid';   $records_per_page = 5; 

Here default values are initialized, including the username and password for the database. Remember: Do not save include files within the document root files with an .inc or any other extension that will show their contents to remote users (passwords and all). Allowing remote users to run includes at a whim could have unexpected consequences. The best solution is to store your include files in a separate directory outside the web root and reference them from your script using their fully qualified path.

 function db_connect() {  global $dbhost, $dbusername, $dbuserpassword, $default_dbname;  global $MYSQL_ERRNO, $MYSQL_ERROR;  $link_id = mysql_connect($dbhost, $dbusername, $dbuserpassword);  if(!$link_id)  {     $MYSQL_ERRNO = 0;     $MYSQL_ERROR = "Connection failed to the host $dbhost.";     return 0;   }   else if(empty($dbname) && !mysql_select_db($default_dbname)) {     $MYSQL_ERRNO = mysql_errno();     $MYSQL_ERROR = mysql_error();     return 0; } else return $link_id; } 

This function handles connecting to the database and returning a connection ID. It is used by many other functions within this file, and is called frequently from the including files as well.

 function sql_error() { global $MYSQL_ERRNO, $MYSQL_ERROR; if(empty($MYSQL_ERROR)) {     $MYSQL_ERRNO = mysql_errno();     $MYSQL_ERROR = mysql_error(); } return "$MYSQL_ERRNO: $MYSQL_ERROR"; } 

Should an error be encountered during database access, this function will provide the information necessary to diagnose the problem.

 function getAssoc($query, $force_array = 0) { // Force Array // 0 = Accept Default // 1 = Force Single Row // 2 = Force Multi Row (may have only one item)   /* There are 6 Possibilities for this function to deal with    Force Array      Results    Desired Action    0                   =1       Pushfirst row directly    0                   >1       Loop through results    1                   =1       Push first row directly    1                   >1       Push first row directly    2                   =1       Loop through results    2                   >1       Loop through results    So, If there is more than 1 result, and force array = 0 OR force array = 2, loop   through the results, otherwise, just push the first row.   */  $link_id = db_connect();  $result = mysql_query($query);  if (mysql_num_rows($result) == 1)  {   $SingleResult = TRUE;  }else  {   $SingleResult = FALSE;  }  if (($SingleResult == FALSE && $force_array == 0) || ($force_array == 2))  {   $results = array();   while ($query_data = mysql_fetch_assoc($result))   {    array_push($results, $query_data);   }  }else  {   $results = mysql_fetch_assoc($result);  }  return $results; } 

This is one of my favorite functions for database access. I use it essentially every time I want to run a query. The logic can be a bit confusing, but the point of the function is relatively simple: to run the specified query and return the result(s) in an associative array. Setting the force_array option allows the calling function to specify that it would always like an array of arrays as a result, or that it would always like just a single associative array as a result, or finally (the default setting) that the way results are returned should depend on the number of rows the query returns.

 function rowCount($query) {  $link_id = db_connect();  $result = mysql_query($query);  $rowCount = mysql_num_rows($result);  return $rowCount; } 

This is just your basic row counting function.

 function insertQuery($query) { $link_id = db_connect(); $messages = mysql_query($query, $link_id); return $messages; } function insertQueryReturnID($query) { $link_id = db_connect(); $messages = mysql_query($query, $link_id); return mysql_insert_id($link_id); } ?> 

These last two functions are closely tied, as they are both designed to be used to INSERT, UPDATE, or REPLACE type operations. The first function is appropriate for use under most circumstances; it returns the number of rows affected. The second function is appropriate when inserting a row with a query that allows the database to specify the primary key; it returns the primary key assigned by the database to the inserted row (assuming the primary key is set to auto increment).

Dates

From a MySQL internal date format (like timestamp or datetime) to RFC 822 Format (used in RSS), this would be used within a MySQL query:

 DATE_FORMAT(<Field Name>,'%a, %d %b %Y %T EST') 

From the present date according to the server to RFC 822:

 date("r") 

From a MySQL internal date format to W3C's date-time format & ISO 8601 (used in Atom):

 DATE_FORMAT(<Field Name>,'%Y-%c-%dT%H:%i:%S-04:00') 

From the present date according to the server clock to W3C's required date format / ISO 8601:

 echo date("c")                        // PHP 5 Only echo date("Y-m-d\TH:i:sO")            // PHP 4 and previous