Custom PHP database functions

Keeping your code organised, clean and fast

One advantage of using functions which is often overlooked, is code contained within functions is ignored until the function is called from another part in the script, which can make your script load faster. It also keeps your code organised and clean.

This article will focus on database functions, I use hand written functions like these as i find them faster and easier to maintain compared to using a package like PEAR::DB. I wrote these functions when I was using PHP 4 so there is no OO-style querying.

Database Connections

function connect() {
 $con = mysql_connect('localhost', 'user', 'password');
 if(!$con){
  rigger_error("Problem connecting to server");
 }	
 $db =  mysql_select_db('database_name', $con);
  if(!$db){
   trigger_error("Problem selecting database");
 }	
return $con;
}

function disconnect($con) {
 $discdb = mysql_close($con);
  if(!$discdb){
   trigger_error("Problem disconnecting database");
 }	
}

I include the link identifier value in the mysql_select_db() function, this is not required but improves security. If the connection parameter is not specified, the last link opened by mysql_connect() is assumed.

As you can see I have a function for closing my database connection, although this isn't necessary as PHP will automatically close the connection when the script ends, I was taught the habit of closing what you open.

Updating the database

function execute_update($sql){
 $con = connect();
 $result = mysql_query($sql, $con);
 if(!$result){
  trigger_error("Problem updating data");
 }
 disconnect($con);
}

This function requires one parameter, the MySQL code. The link identifier value is needed for the mysql_query() function, again this parameter is not required but is best practice to include it. We use the custom function connect() to obtain the value. The MySQl query is then run and the result is stored in the $result variable and checked, if all is ok the disconnect() function is run.

Querying the database

function execute_query($sql) {
 $con = connect();
 $result = mysql_query($sql, $con);
 if(!$result){
  trigger_error("Problem slecting data");
 }
 while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
  $result_array[] = $row;
 }
 disconnect($con);
 return $result_array;	
}

This function will output the mysql result as a standard PHP array with the field names as the keys.

The MySQL result is saved in the variable $result, I run a while() function to go through and add the MySQL result to an array. I use the MYSQL_ASSOC parameter so I have the field names for the array keys. The database connection is then closed and the array is returned

An example of this function in action is below. Selecting all the article titles on this site and using the execute_query() function to get an array of the titles. I then cycle through them and list them out in a list.

$sql = "SELECT title
		FROM `articles_table`
		ORDER BY `date`";

$articles_array = execute_query($sql);

foreach($articles_array as $article) {	
	echo "< li >$article['title']< /li >\n";
}

As hopefully you can see, the code is now hugely reduced and much cleaner. I will add some of my functions I use for common non database functions in my next article.

Published 02.10.08