Six MySQL/PHP functions to streamline development

by Sanders "Kaufman, Jr." | Jul 02, 2002 7:00:00 AM

Tags: Sanders Kaufman, Jr.

Takeaway: Code reuse is one of the keys to increasing developer productivity. This article looks at several handy functions you can use to save time when building applications using PHP and MySQL.

Time is your most precious commodity, and you definitely don't need to waste it on implementing repetitive tasks. One way to reduce such inefficiency is to take advantage of reusable code. In this article, I'll introduce six functions to help you quickly build database-enabled Web sites using PHP and MySQL.

The first function verifies your connection to a MySQL server, the second verifies the existence of a specified database, and the third lets you determine whether a particular table within that database exists. The fourth function enables you to run a SQL query without retrieving a result set (like when you're managing table definitions with DDL). The fifth and six functions run a SQL command and return the result set as an HTML table or an XML data island. You can format the result for delivery on the fly to a Web page, mobile device, or other client using CSS StyleSheets or XSLT style translations.

fnConnectionOK()
To use the fnConnectionOK(), you must have the server name, user name and password. In the example below, the server name is “mysql.kaufman.net,” The user name is “guest,” and the password is “secretword.”
function fnConnectionOK() {
// Verifies a connection to a MySQL database server
if (!$oConn = @mysql_connect(“mysql.kaufman.net”, “guest”, “secretword”)) {
$bRetVal = FALSE;
} else {
$bRetVal = TRUE;
}
return $bRetVal;
}

Usage
Using fnConnectionOK() is simple. The function accepts no parameters and returns a Boolean value.
$bRetVal = fnConnectionOK();

fnDatabaseExists()
The fnDatabaseExists() function verifies whether a particular database exists on your MySQL server. As before, you need to have a server name, user name, and password.
function fnDatabaseExists($dbName) {
//Verifies existence of a MySQL database
$bRetVal = FALSE;
if ($oConn = @mysql_connect(“mysql.kaufman.net”, “guest”, “secretword”)) {
$result = mysql_list_dbs($oConn);
while ($row=mysql_fetch_array($result, MYSQL_NUM)) {
if ($row[0] ==  $dbName)
$bRetVal = TRUE;
}
mysql_free_result($result);
mysql_close($oConn);
}
return ($bRetVal);
}

Usage
This function accepts one parameter—the name of the database you're verifying on your MySQL server. It also returns a Boolean value indicating whether that database exists.
$bRetVal = fnDatabaseExists(“MyDatabase”);

fnTableExists()
The fnTableExists() function determines whether a particular table exists within a specified database on your MySQL server. Again, you need to have a server name, user name and password.
function fnTableExists($TableName) {
//Verifies that a MySQL table exists
if (!$oConn = @mysql_connect(“mysql.kaufman.net”, “guest”, “secretword”)) {
$bRetVal = FALSE;
} else {
$bRetVal = FALSE;
$result = mysql_list_tables('MyDatabase', $oConn);
while ($row=mysql_fetch_array($result, MYSQL_NUM)) {
if ($row[0] ==  $TableName)
$bRetVal = TRUE;
break;
}
mysql_free_result($result);
mysql_close($oConn);
}
return ($bRetVal);
}

Usage
This function also accepts one parameter—the name of the table you're verifying within the specified database on your MySQL server. It also returns a Boolean value indicating whether the table exists.
$bRetVal = fnTableExists(“Users”);

fnRunSQL()
The fnRunSQL() function takes a SQL statement as a parameter (no semicolons, please) and runs it without returning a result set. This is handy for working with Data Definition Language (DDL) SQL commands that create, drop, and modify tables.
function fnRunSQL($sSQL) {
//Runs a SQL statement and returns
// - TRUE if successful
// - FALSE if it couldn't connect
// - The MySQL error code if the SQL statement fails
if (!$oConn = @mysql_connect(“mysql.kaufman.net”, “guest”, “secretword”)) {
$bRetVal = FALSE;
} else {
if (!mysql_selectdb('MyDatabase',$oConn)) {
$bRetVal = FALSE;
} else {
if (!$result = mysql_query($sSQL, $oConn)) {
$bRetVal = mysql_error();
} else {
$bRetVal = TRUE;
mysql_free_result($result);
} }
mysql_close($oConn);
}
return ($bRetVal);
}

Usage
As we mentioned, the fnRunSQL() function accepts one parameter—a SQL statement. Although the function doesn’t return a result set, it does return values. If the connection to the database or server fails, it returns a Boolean FALSE. If it succeeds, it returns a Boolean TRUE. If the connection is okay, but the SQL command generates an error, the function returns a string description of the error.

Be a little careful here because the string error value will equate to TRUE in a Boolean equation. If your return value equates to TRUE in Boolean, you’ll want to verify that it also equates to a blank string to make sure that no error string was generated by the SQL statement itself.

To deal with the mixed type (Boolean/string) a little extra work is required to handle errors gracefully.
$sRetVal = fnRunSQL(“CREATE TABLE users (userid INT NOT NULL, username TEXT)”);
if (!$sRetVal) {
echo “Error connecting to database”;
} else {
if (!$sRetVal = “”) {
echo “Error: $sRetVal”;
} else {
echo “SQL executed successfully!”;
} }

fnSQLtoHTML()
The fnSQLtoHTML() function transforms a SQL call into an HTML table. The HTML table contains the values returned by the SQL statement. If the SQL statement returns an error, the function returns that error instead of an HTML table.
function fnSQLtoHTML($sSQL) {
//Returns an HTML table from a SQL statement
if (!$oConn = @mysql_connect(“mysql.kaufman.net”, “guest”, “secretword”)) {
$sRetVal = mysql_error();
} else {
if (!mysql_selectdb('MyDatabase',$oConn)) {
$sRetVal = mysql_error();
} else {
if (!$result = mysql_query($sSQL, $oConn)) {
$sRetVal = mysql_error();
} else {
$sRetVal = "<table border=1>\n";
$sRetVal .= "<tr><th colspan=" . mysql_num_fields($result) . ">";
$sRetVal .= mysql_field_table($result,0) . "</th></tr>";
$sRetVal .= "<tr>";
$i=0;
while ($i < mysql_num_fields($result)) {
$sRetVal .= "<th>" . mysql_field_name($result, $i) . "</th>";
$i++;
}
$sRetVal .= "</tr>";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
$sRetVal .= "\t<tr>\n";
foreach ($line as $col_value) {
$sRetVal .= "\t\t<td>$col_value</td>\n";
}
$sRetVal .= "\t</tr>\n";
}
$sRetVal .= "</table>\n";
mysql_free_result($result);
}
}
mysql_close($oConn);
}
return ($sRetVal);
}

Usage
This function also accepts one parameter—a SQL statement. It returns a basic HTML table. If there is an error anywhere in the process of executing the query, the function returns a string description of the error.
echo fnSQLtoHTML(“SELECT userid, username FROM users”);

fnSQLtoXML()
The most useful function on the list may be fnSQLtoXML(). It takes a SQL statement as a parameter (as before, no semicolons) and returns an XML data island. You can insert this data island in your HTML document for use as a client-side recordset.

This approach is handy for working with data that the user will want to perform a variety of analyses on but will not be modifying. The data from the result set can be sorted, filtered, grouped or what-if’d by the user without having to make multiple round trips to the server to retrieve the same basic data over and over. It also shifts the processing demands of the data views to the client computer, reducing the load on both the Web and database server.
function fnSQLtoXML($sSQL) {
//Returns an XML data island from an SQL statement or an error string
if (!$oConn = @mysql_connect(“mysql.kaufman.net”, “guest”, “secretword”)) {
$sRetVal = mysql_error();
} else {
if (!mysql_selectdb('MyDatabase',$oConn)) {
$sRetVal = mysql_error();
} else {
if (!$result = mysql_query($sSQL, $oConn)) {
$sRetVal = mysql_error();
} else {
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
$sRetVal = "\n<" . mysql_field_table($result,0) . ">";
$iThisField = 0;
foreach ($line as $col_value) {
$oTMP = mysql_fetch_field($result, $iThisField);
$iThisField ++;
$sThisFieldName = $oTMP -> name;
$sRetVal .= "\n\t<$sThisFieldName value=" . $col_value . ">";
$sRetVal .= "</$sThisFieldName>";
}
$sRetVal .= "\n</" . mysql_field_table($result,0) . ">\n";
}
mysql_free_result($result);
}  }
mysql_close($oConn);
}
return ($sRetVal);
}

Usage
As with fnSQLtoHTML(), this function accepts a SQL statement as its single parameter. It returns a basic XML data island. If there is an error anywhere in the process of executing the query, it returns a string description of the error.
echo fnSQLtoXML(“SELECT userid, username FROM users”);

---

Download the code covered in this article

 dbfunctions.php

---

Impact on performance and scalability
A cost of using PHP-encapsulated database functions is their impact on performance and scalability. In the above samples, repeated use of the fnRunSQL() function, for example, would result in establishing and disestablishing connections and reserving and freeing other system resources for each instance of the command.

You can get around this inefficiency by creating functions, specific to your applications needs, based on the ones provided above. For example, the fnRunSQL() function could accept an array of SQL commands instead of just one. Then, the function could use the same connection for all of your queries.

You’ll notice that there are no or die(); components to these equations. This is because all error handling is performed within the functions in a series of nested if/else constructs. This minimizes the error handling you’ll need to perform when using these little goodies in your own applications. But bear in mind that when you handle errors this way—by simply testing for FALSE instead of using the or die(); option—you run the risk of spawning “rogue” or “zombie” processes. These are processes that the spawning process (our PHP page) has launched but that never end.

You can still use this Boolean error-handling method, but you’ll also want to use a little trickery via the mysql_list_processes() and mysql_thread_id() functions to determine whether you’ve spawned a rogue process. If so, you’ll want to issue the die(); command. (Note: This is an issue only in Mod PHP and not in CGI PHP. Mod PHP runs better, but CGI PHP runs more safely.)

Summary
Using these six functions, you should be able to build PHP applications that work with a back-end database without worrying about establishing and closing connections or freeing up unused database resources and other plumbing. You can get HTML tables and XML data islands from SQL statements and perform SQL-based data management functions.

 

Print/View all Posts Comments on this article

Six MySQL/PHP functions to streamline de WearsManyHats | 07/02/02

Sloppy Programming Practices techrepublic@... | 07/02/02

Shameless Bucky Kaufman (MCSD) | 07/08/02

Indeed... Sebastien Rosset | 07/10/02

keep it goin! ray@... | 08/20/02

assignment shelleydoll | 07/09/02

What about pear? dmorphis@... | 07/02/02

Pear is Good Bucky Kaufman (MCSD) | 07/08/02

pear is to slow john@... | 11/06/02