Approaches to Web Development for Bioinformatics

Medical Computing > Web Development for Bioinformatics > Relational Databases > PHP

Accessing a Relational Database with PHP

PHP supports a number of databases. Since MySQL is the M in LAMP I will demonstrate with MySQL. Here is a script that demonstrates looking up all the records in the gene_info table. If you copy this script to your web server it will generate a HTML table with a line for each gene_info record. Don't forget to change the host name and database. In production never print out errors like this. It could give a hacker information that he could exploit.

A MySQL extension is included with PHP on Windows but not on Linux for PHP 5. If you are using Linux get the driver from the MySQL site⁴⁰. The MySQL extension needs to be enabled correctly setting the extension directory and by editing the php.ini file to uncomment the mysql extension shared library line. For example, on Windows,

pnp.ini

extension_dir="C:\Program Files\PHP\ext"
...
extension=php_mysql.dll

On Windows you need to make sure that the file libmysql.dll is on the system path, most likely in the PHP directory. If you have installed on Windows and can't find these files try downloading the zip version of the distribution.

Here is a program to demonstrate database access with the schema defined above.

PHP

<?php

// Get a connection to the database
$link = mysql_connect('host', 'username', 'password')

or die('Could not connect: ' . mysql_error());

mysql_select_db('genes')

or die( 'Could not select database' );

// Perform SQL query
$query = 'SELECT id, symbol, name FROM gene_info';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// Print results in HTML
echo "<table><tbody>\n";
echo "<tr><th>ID</th><th>Symbol</th><th>Name</th></tr>" ;
while ($row = mysql_fetch_assoc($result)) {

echo "\t<tr>\n";
echo "\t\t<td>" . $row["id"] . "</td><td>" . $row["symbol"] . "</td><td>" . $row["name"] . "</td><td>";
echo "\t</tr>\n";

}
echo "</tbody></table>\n";

// Free result set
mysql_free_result($result);

// Closing connection
mysql_close($link);

A few points are noteworthy here:

It was easy to write - straight to the database and out as HTML with not much in between. This is great for small projects but attention needs to be taken to maintainability as the project grows. The object oriented features in PHP 5 can help here.
I used the MySQL API directly. This is not as much of a concern with a scripting language like PHP than with strongly typed languages like Java. Even so, it can leave you exposed if you decide to change the database. You can make use of an abstraction layer to isolate your application code from vendor specific code. Several database abstraction API's are PDO, PEAR DB, ADODb, and MDB2.
The host, user name, and password for the database are directly in the code. This is not a good practice. They should be stored in a private, secure area. But where? PHP does not give the kind of strong guidance on this that other platforms do.

If you are constructing SQL queries including user entered text it is critical to escape that text so that special SQL characters are escaped. Not doing this could allow a hacker to access your database. The PHP MySQL function mysql_real_escape_string() can be used for this. It will escape \x00, \n, \r, \, ', " and \x1a.

Previous Contents Next

References

Contributed Comments and NotesAdd a comment.

There are no user comments.

Please send ideas and opinions by email at alexamies@gmail.com.