Open source software security

Using PHP to Access MySQL Results

30 November -0001
The combination of PHP and a MySQL database is a perfect one. In addition to both being free, Open Source technologies, there are many powerful PHP functions that allow you to access MySQL data quickly and easily. Much of PHP's power derives from its ability to access and manage stored data, and a MySQL database is a fast, efficient, and scalable way to store site data. While there are many ways to access stored, MySQL data, this article aims to get you writing concise code that is easy to interpret, maintain, and manage. By writing clean and easy to read code you insure that future modifications to your code take as little time as possible.

For this example we'll be using the following table to return results to a MySQL result page:

visits (
	Visit_id int auto_increment not null primary key,
	Visit_page varchar(255),
	Visit_ip varchar (16),
	Visit_datetime datetime
)


Basically, this is a simple logging table that can be used to track 'hits' on various pages of a website. Assuming we want the most recent 20 records in the table, the textbook method for accessing and presenting this information would be:

$link = mysql_connect( "dbhost", "user", "pass");
mysql_select_db("database", $link);
$theSQL = "select visit_ip, visit_page, visit_datetime from visits order by visit_datetime desc LIMIT 20"
	$query = mysql_query($theSQL);
while ($result = mysql_fetch_array($query)) {
	echo $result["visit_ip"] . "\n;";
}
mysql_close($link);


Let's look at this code for a moment and dissect it. The first two lines merely set up the data connection using the PHP functions mysql_connect and mysql_select_db. The last line closes the open connection (good practice so you don't leave hanging database connections that the server has to clean up later). The main guts of the query occur with the mysql_query function, which returns a result set that we assign to the variable $query. Then we have a 'while' loop to loop over the results by placing the results in an array called $result using the mysql_fetch_array function to translate the result set $query into an array. This PHP script should spill out up to 20 lines of code, depending on how many records are actually in the visits table. You'll notice that we're referring to the array in the 'echo' statement by its field key of 'visit_ip'.

We can also use a 'for' loop in place of the 'while' loop to scan across the results using the following code [please replace the < with a less than sign if your browser dislays it in this code snippit, there is some wierd problem with the XHTML display}:

for ($x=0;$x < mysql_numrows($query);$x++) {
echo mysql_result($query,$x,"visit_ip") . "\n";
}


This code uses two new PHP functions, mysql_numrows is used to count the number of rows returned in the result set $query, and mysql_result which is used to examine one row at a time from the result set. We're using the incremental integer $x to keep track of our location in the result set for this example. You could alternatively limit the number of display results by altering the 'for' loop.

The problem with this code architecture, however, is that it leaves a lot of code on the page and becomes a problem if you have to maintain the code. For instance, if you are using code in this structure to make several database queries across several pages and you database username were to change, you would have to explicitly update every instance of the database password. In addition, you're repeating a lot of typing every time you want to query the database by having to explicitly set up the connection, and then tear it down. You run the risk of creating errors by copying or manually typing the connection code over and over again. In order to alleviate this problem you can use PHP's native include structure. In any PHP page, you can call another PHP file using:

Include ("somepage.php");


And the entire text of the 'somepage.php' is inserted into the page at that point as it compiles. If you were to write a library of functions, you could include them at the start of every page with one simple include statement. Now, while you could simply write an include to instantiate a data connection, and one to tear it down, it is far more efficient to take advantage of PHP's function features and write a custom function to handle the entire database transaction. With every select statement you will want to pass an SQL query to the function and return the result set. By placing the following code in a global include file that is referenced on every page you can easily query the database and get result sets:

function selectSQL($theQuery)
	{
	$openlink = mysql_connect($GLOBALS["dbhost"], $GLOBALS["dbuser"], $GLOBALS["dbpass"]) 
		or die ("failed to connect to server: " . mysql_error());
		mysql_select_db ($GLOBALS["db"]) or die ("failed to connect to database ($db): " . mysql_error());
		$query = mysql_query($theQuery) or die ("Select query failed: " . mysql_error());
		
		$array_counter = 0;
		$return_array = array();
      while ($row = mysql_fetch_array ($query)) 
	  {
        $return_array[$array_counter]=$row;
        $array_counter++;
      }
      mysql_free_result ($query);
      return $return_array;
	  
	  mysql_close($openlink) or die ("couldn't clean up connection: " . mysql_error());
	}


This function is a little complex, so it bears some explaining. The first thing you might notice is the $GLOBALS variables. These are variables that are set in a global scope, usually with an include file that is put on every page. Using this structure you can list the database, username, and password for the MySQL connection in one place and reference it throughout the code (making it easy to update if the information changes down the road). You will also notice the 'or die' statements. These are used to echo error and debugging information back to the screen if the function should fail. You may want to change this or even remove it so that users can't see it, but often times it is better to leave debugging information around while you're developing an application. Changing the 'or die' statements to a mail function that will email alerts of failures to an administrator ensures that down the line the users won't see ugly, and possibly insecure error messages, and that an admin will be alerted every time the database function fails. The majority of this code should look relatively familiar given the earlier examples. The function takes one argument, which is the query string that will be passed to the database. The function then creates an array of the result set and returns it. To instantiate this function simply call it and assign the results to a variable like so:

$queryResults = selectSQL("select * from visits");


Once you have the results assigned to a variable you can loop over them exactly as before, perhaps even parsing the array into key, value pairs like so:

$theSQL = "select visit_ip, visit_ref, visit_page, visit_datetime from visits order by visit_datetime desc LIMIT 20"
$query = selectSQL($theSQL);
while ($element = each( $query))
{
  print $element['value']['visit_ip'] ;
}


Often times manually building an associative array is the easiest way to access results. For instance, let's assume that we want to pull out a distinct hit count for each day of the week from our visits table. We want the days to be listed manually, rather than alphabetically, so we can't simply structure our query and loop over the result set. This problem may also arise when you want specific results from the result set to assign to a variable and you don't know beforehand in what order your results will be generated. You can use the following query (combined with the function code above) to print out the hit count for Sunday:

$theSQL = "select count(visit_id) as cod, date_format(visit_datetime, '%W') as theDay";
$theSQL .= "from visits group by theDay order by theDay";
$query = selectSQL($theSQ);
$dayArray;
while ($element = each( $query))
{
	 $tmpArray = array ($element['value']['theDay'] => $element['value']['cod']);
	 $dayArray = array_merge ($dayArray, $tmpArray);
}

echo "Sunday: " . $dayArray['Sunday'] ;


Basically, the above code creates a new associative array, $dayArray, to hold the values we parse out of the query result set array. An associative array is useful for referring to a piece of information by a non-numeric key, in this case the day name.

Now, if you've gotten this far you're probably wondering about queries that don't use a 'select' statement (insert, update, or delete statements). You can use the same function based approach with these statements as well using the following code:

function iudSQL($theQuery)
	{
		$openlink = mysql_connect($GLOBALS["dbhost"], $GLOBALS["dbuser"], $GLOBALS["dbpass"]) 
			or die ("failed to connect to server " . mysql_error());
		mysql_select_db ($GLOBALS["db"]) 
			or die ("failed to connect to database ($db): " . mysql_error());
		$query = mysql_query($theQuery) 
			or die ("Insert/Update/Delete query failed: " . mysql_error());
		
		//return the ID if we're doing and insert
		if (strtolower(substr($theQuery,0,6)) == "insert")
			{$theID = mysql_insert_id();
			 mysql_close($openlink) 
				or die ("couldn't clean up connection: " . mysql_error());
			 return $theID;}
		else {
			mysql_close($openlink) 
				or die ("couldn't clean up connection: " .mysql_error());
			return true;}
	}


This code comes in particularly handy for inserts since it will return the id of the inserted row when called. For instance, if the next row to be inserted into visits is 61, we can capture that information using:

$theID = iudSQL("insert into visits(visit_datetime, visit_page) values (now(), 'test page')");


Then we can check the value of the variable $theID and it will contain the id of the inserted row (which is useful if you want to update multiple tables across the database with the new information).