Writing Clean Robust PHP database Code [Tutorial]

[__Darknite]

PHP for all its sins is a very powerful tool. In terms of speed of productivity, nothing can touch it. Throw some code into a script, upload it and you are done!

 

However this ease comes at a cost: what first appears to be "quick and easy" turns out to be an maintenance nightmare. Writing clean and robust code in PHP is hard.

 

Often times, many newcomers do not always have a background in software engineering. This mixture of ease of use combined with no formal training in clean architecture can lead to "spaghetti code". This is one of the reasons PHP has gotten a bad reputation among professional developers.

 

However with some effort and careful design, this does not have to be the case.

 

In this short tutorial we shall look at writing clean and robust code that deals with databases.

 

All web applications will have certain "settings", or "configurations". A bad approach is to copy and paste these settings all over the place!

The clean approach is to place all settings and configurations into one file. This keeps things under control. When you need to make a configuration change, you no longer have to hunt you entire code base.

 

So lets first create a "configuration" file:

 

configuration.php:

 

//==================================
// config
//==================================
define("db_user", "user");
define("db_pass", "password");
define("db_name", "demo");
define("db_server", "127.0.0.1");

 

Now connection to a database in php is very common, however the code is quite a lot of boiler plate. Also when parameters are used for sql queries these need to be carefully "escaped" to avoid nasty security attacks such as "sql injections"

 

By creating functions, we can encapsulate the core database access functions. This will reduce the code and also make it very clean. The sample below shows the data access functions:

 

data_access.php:

 

//==================================
// Data Access
//==================================
function runSQL($Query)
{
//connect to server, then test for failure
if(!($conn = mysql_connect(db_server,db_user,db_pass)))
{
	 print("Failed to connect to database!<br>\n");
	 exit();
}
//select database, then test for failure
if(!($dbResult = mysql_query("USE ".db_name, $conn)))
{
	print("Can't use the database.<br>\n");
	exit();
}

$rs = mysql_query($Query) or die(mysql_error());
mysql_close($conn);
return $rs;
}
function runSQLParms($Query,$Params)
{
//connect to server, then test for failure
if(!($conn = mysql_connect(db_server,db_user,db_pass)))
{
	print("Failed to connect to database!<br>\n");
	exit();
}
//select database, then test for failure
if(!($dbResult = mysql_query("USE ".db_name, $conn)))
{
	print("Can't use the database.<br>\n");
	exit();
}
foreach($Params as $key => $value)
{
	$Query = str_replace($key,mysql_real_escape_string($value,$conn),$Query);
}
  //echo $Query;
  $rs = mysql_query($Query) or die(mysql_error());
  mysql_close();
  return $rs;
}

 

In the above data access functions, we have two main functions runSQL and runSQLParms. These two functions are now ready to be used in your projects to cleanly access data.

 

Example:

 

Imagine we have a php file where we are required to do various database actions. First we need know we need the database "configurations" and also our database access functions. Lets import them at the start of the file:

 

sample.php:

 

require_once("configuration.php");
require_once("data_access.php");

 

Lets say we need to flush some data from a temporary table (we need to execute some sql):

 

function FlushTemporaryData()
{
$sql = "delete from temp_table";
runSQL($sql);
}

 

What if we needed to delete a particular record between say a date range? We would require sql that has parameters. Usually these parameters would be values passed from client side forms. When accepting data from users, there is always a danger of "sql injections".

 

We can safely do it using the second database access function:

 

function DeleteUserLog($UserId, $Start_Date, $End_Date)
{
$sql = "delete from user_logs where userId = @UserId and log_date between @Start_Date and @End_Date";

$p["@UserId"] = $UserId;
$p["@Start_Date"] = $Start_Date;
$p["@End_Date"] = $End_Date;

runSQLParms($sql, $p);
}

 

As you can see the runSQLParms function requires two things: The SQL query, and an array of Parameters. The function will automatically take each parameter and merge it into the SQL query. Also each parameter will be escaped to stop sql injections.

 

Of course, we will often require to get some data back and do things with the result (such as render it as html).

 

lets image we would like to display the top 5 students based on their score:

 

function DisplayTopStudents()
{
$sql = "select First_Name, Last_Name, Score order by Score desc limit 5;";

$rs = runSQL($sql);

$html = "";
while($row = mysql_fetch_array($rs))
{
   $html = $html . $row["First_Name"] . " " . $row["Last_Name"] . "Score:" .  $row["Score"]   . "<br/>";
}

  echo $html;
}

 

 

This is just the tip of the "iceberg". Hopefully you can see how easy it would be to create and maintain these functions.

 

Questions and feedback welcome!

[Nathan]

Ah good to know, so just by calling them that 1 time it will connect anywhere in the php code?

[__Darknite]

yes that's correct, once you import the "configurations" and the "data access" (which you would normally do in each php file that you need them). You can then call those two functions any time you need them. They safely open a database connection and close it when its done.

[Thomas]

Thank you for this _Darknite. This is going to be a great help when I get around to getting into php again.

[Microsuck]

Great tutorial! Is this similar to using prepared statements?

[__Darknite]

Hello Micorsuck,

 

Yes its very similar. I wrote those small functions after coming from .net camp. In the .net world stored procs are second nature. I wanted to emulate a similar clean codepattern in php.

[Microsuck]

Hello Micorsuck,

 

Yes its very similar. I wrote those small functions after coming from .net camp. In the .net world stored procs are second nature. I wanted to emulate a similar clean codepattern in php.

 

Neat! I will have to bookmark this tutorial!

 

Thank you!

[Nathan]

Can I define paths in the config.php file so that if I want to install this on another site I only have to change all the paths for .css files ect... in one place.

 

Would this work?

 

Config.php

//==================================
// config
//==================================
define("db_user", "user");
define("db_pass", "password");
define("db_name", "demo");
define("db_server", "127.0.0.1");
$path=http://Digitize Design.net/pm/css;

 

sample.php

<?php
require_once("config.php");
>?


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title><?php echo $core->site_name;?></title>
<script language="javascript" type="text/javascript">
var SITEURL = "<?php echo SITEURL; ?>";
</script>
<link href="[b]$path[/b]/front.css" rel="stylesheet" type="text/css" />
<script type="text/javascript" src="[b]$path[/b]/assets/jquery.js"></script>
<script type="text/javascript" src="[b]$path[/b]/assets/jquery-ui-1.8.13.custom.min.js"></script>
<script type="text/javascript" src="[b]$path[/b]/assets/global.js"></script>
<script type="text/javascript" src="[b]$path[/b]/assets/tooltip.js"></script>
<script type="text/javascript">

[Thomas]

Yes you can

[Nathan]

Just like I did above? The $path will automatically be passed?

[Thomas]

Indeed it should do.

[Nathan]

Doesn't seem to work, I'm guessing it's b/c I'm trying to use the variable outside of the PHP <?php and ?>?

 

Also what if I only want to grab the variable $path and not connect to the database each time?

[Thomas]

This might help: http://php.about.com/od/learnphp/qt/php_links.htm

 

As for the 2nd one as the config.php file only stores the variables and doesn't actually connect to the database. Therefor just by defining the variables nothing happens.

[Nathan]

ugh, that sucks, didn't want to have to echo out all my code

[Microsuck]

If I am not mistaken, your $path variable is a string and needs to have quotes or apostrophes (commonly called "single quotes").

[__Darknite]

Hi Nathan,

 

I would try and code in a more robust manner.

 

As an example, with the "header" section I would wrap it into a function. This will allow you to put additional logic that may preform all kinds of things:

 

* load a different set of javascript between live and test.

* Auto load javacript on the fly etc

 

Example:

 

header.php

 

InjectHeader();

function InjectHeader()
{
//define javascripts
$js = array("jquery.js", "global.js", "tooltip.js");

//do your auto logic here, i.e. dynamically add, remove javascript per page

//finally render it:
RenderHeader($js);
}

function RenderHeader($js)
{
  $header = "";

  foreach($js as $x)
  {
	   $header = header . "<script type=\"text/javascript\" src=\"" . $path . "/assets/" .  $x  . "\"></script>";
  }
  echo $header;
}

 

 

 

Edit

 

The problem relating to the $path my be related to do with "variable scoping":

 

$path = "foo"; //this is global scope


function hello()
{
      $path; //this is local variable and NOT the same as above
}

function world()
{
     global $path; // you are telling php to use the global variable!
}

Edited May 30, 2012 by __Darknite

[bryce12]

Apart from manageability this also makes our code reusable. Imagine having to maintain and change code where there's lot of repetition! I have seen instances where people just copy/paste hundreds of lines instead of making their code more modular. Writing good code in any language is difficult and it takes lots of discipline to develop concise and reusable code.

[Talk Tech News]

A great tutorial. PHP does seem to have a bad reputation with some people but it is very powerful.

[Thomas]

Bad reputation? Like what?

 

Most websites are run by PHP nowadays. It doesn't have much competition.

[__Darknite]

Bad reputation? Like what?

 

Most websites are run by PHP nowadays. It doesn't have much competition.

 

 

Without a doubt PHP is very popular, but that does not mean it still doesn't have a bad reputation among developers. just Google:

 

"php bad reputation"

 

There are about 12.9 Million results, reading some of those articles should give you a clearer idea.

 

Naturally there are a lot of rebuttal articles too. However As the saying goes, there is no smoke without fire. There certainly are some root issues with PHP (most can be overcome). PHP's history has much to do with it.

Edited June 12, 2012 by __Darknite

[DarkGizmo]

Thanks for this tutorial.

[Marc]

Only just taken the time out to read this properly. An excellent tutorial there m8 that Im sure will be greatly appreciated

[Thomas]

Only just taken the time out to read this properly. An excellent tutorial there m8 that Im sure will be greatly appreciated

 

I still haven't got around to read this. Dam my weakness for wine.

[Marc]

I still haven't got around to read this. Dam my weakness for wine.

 

Not a weakness at all m8, in fact I was told never to quit at anything as quiters never win. Therefore I drink and I smoke! Listen Im a developer, logic is in my blood. None of these fussy lines.

[flotwig]

Why not just use PDO?

http://us2.php.net/pdo

PDO does pretty much what you're trying to do. It provides an object-oriented structure and a proper interface for prepared statements. It doesn't use mysql_* functions but it has equivalents.

Here's a PDO script from start to finish:

<?php
new PDO('mysql:host=localhost;dbname=somedb','user','pass');
$sql = 'SELECT * FROM `sometable` WHERE `somecol`=?';
$sth = $dbh->prepare($sql); // prepared statements woo!
$sth->execute(array('somevalue')); // ^
$out = $sth->fetchAll();
var_dump($out);
?>