Baglan Dosmagambetov
2003-03-03
Having in mind PHP developers like myself I strived to provide an alternative and hopefully useful method for easing the daunting task of database communication. In my line of work and, I presume, in line of work of many of my colleagues database-driven PHP applications, and therefore communication with database, play very important role. As is, PHP provides a very flexible and easy way for database integration. Native drivers for majority of databases and ODBC connectivity make the task of database communication almost trivial.
Sometimes, especially if you have really many queries in many different places of your program, writing twin-like database communication code and keeping it up to date can become a boring chore. At this point people as lazy as I am usually start to think about just what can be done to make development easier, faster and more elegant.
This article is about separating database-related logic in OO (object-oriented) fashion.
This article is primarily for PHP programmers. Nevertheless, the basic idea is not unique to PHP and guided by basic understanding of OO ideas and SQL, anybody should be able to grab it.
As I mentioned in introduction part, database inegration can become a pretty messy business. Especially if database-related code is more or less database-specific (I mean is intended for one special database like say MySQL) and scattered across the multiplicity of various PHP files.
I personally find if mind-dumbing to go thru all the files and change some stuff in SQL queries. So being a rather lazy person I try to devise some methods so that I could be left at ease. None of them are particularly "new" or "revolutionary" but I find them pretty useful. One of them is called "database abstraction layer" (skipping technical details it is ia way to make all the databases look the same for the program code) and is not in scope of this article. Another is what I decided to call "table handlers".
So, table handler is a piece of code that defines all the multiplicity of communications with some certain database table within the application (or site if you will) and resides in some separate place (file) so that once some changes have to be done, changing coupla lines in that place would be sufficient.
Usefulness of OO methodology in scope of this article is "re-usability" of code. Vast majority of database communication functionality I've coded so far stikingly resembles each another in following respects:
Just like in "abstraction layers", similarity in concept calls for similirity in implementation.
One could argue that the same re-using can be achieved with functions; and that is quite true. Nevertheless OO methodology offers some extra. In my oppinion it is:
etc.
Now let's try to put the ideas described above into code. First let's write a code descibing common database communication in terms as general as possible. Firstly, as OO methodology is going to be used, we'll have a class. Classes have
Lets start from properties:
Now methods:
This list looks fine but what about variable number of fields in a table? For inserting and updating records we would need functions accepting variabnle number of parameters. One of the solutions would be to define functions with variable number of parameters (I believe this feature is supported in PHP). Another method, which I have actually implemented here, would be to get the parameters form the list. This is especially useful as query data in PHP can be accessed from $_GET and $_POST global variables.
Before we see some code I'd like to note that I used my own database abstracion layer (mainly because I'm used to it). Hopefully I'll write an article about abstraction layers too so that no further questions would remain but for now let me just explain what id does:
So, here's the code:
<?php
/**
TableHandler
*/
class TableHandler
{
// Our abstraction layer
var $conn;
// Table name
var $table;
// Fields of the table, excluding the 'id'
var $fields = array();
/**
Initializes the handler
*/
function TableHandler( $conn )
{
// What is actually done,
$this->conn = $conn;
}
function insert( $env )
{
$temp = array();
foreach( $this->fields as $field )
$temp[] = "'".addslashes($env[$field])."'";
$sql = "insert into ".$this->table." (".join(",",$this->fields).")values (".sdfsdfsdfjoin(",",$temp).";";
$this->conn->exec( $sql );
}
function update( $id, $env )
{
$temp = array();
foreach( $this->fields as $field )
$temp[] = $field."='".addslashes($env[$field])."'";
$sql = "update ".$this->table." set ".join(",",$temp)." where id='$id'";
$this->conn->exec( $sql );
}
function delete( $id )
{
$sql = "delete from ".$this->table." where id='$id'";
$this->conn->exec( $sql );
}
function getList()
{
$sql = "select * from ".$this->table;
return $this->conn->getResult( $sql );
}
function getInfo( $id )
{
$sql = "select * from ".$this->table." where id='$id'";
return $this->conn->getSingleRow( $sql );
}
}
?>
Once the basis for all database communication is established, it's time to write a handler for a specific table. Let's decide on the table structure first. A basic phonebook would be just fine. With name, surname, phone, email address and notes the SQL should look like:
CREATE TABLE `phonebook` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(40) NOT NULL, `surname` VARCHAR(40) NOT NULL, `phone` VARCHAR(100) NOT NULL, `email` VARCHAR(100) NOT NULL, `notes` TEXT NOT NULL );
Now the handler for this table should look like:
class Phonebook extends TableHandlerThat's basically it but some search capabilities would help too:
{
var $fields=array ( 'name', 'surname', 'phone', 'email', 'notes' ); var $table = "phonebook";
}
class Phonebook extends TableHandlerNow we're done.
{
var $fields=array ( 'name', 'surname', 'phone', 'email', 'notes' ); var $table = "phonebook"; function search( $string ) { return $this->conn->getResult( "select * from ".$this->table." where name like '%$string%' or surname like '%$string%' or phone like '%$string%' or email like '%$string%' or notes like '%$string%'"); }
}
It's time to demonstrate the methods described above in a real-timeish setting. Full-blown application would be more illustratory but for the sake of brevity I will only show code for 'add' and 'list' functionality here.
HTML for the 'add' form the add for is as following:
... <form action="add.php" method="POST"> <input type="text" name="name"> <input type="text" name="surname"> <input type="phone" name="phone"> <input type="email" name="email"> <textarea name="notes"></textarea> <input type="submit" value="Add"> </form> ...
Code for adding the record should be as trivial as:
... $phonebookHandler->add($_POST); ...
HTML for the 'list' is:
...
echo "<table>
<tr>
<th>Name</th>
<th>Surname</th>
<th>Phone</th>
<th>Email</th>
<th>Notes</th>
</tr>
";
$list = $phonebookHandler->getList();
foreach($list as $item)
{
echo "<tr>
<td>".$item['name']."</td>
<td>".$item['surname']."</td>
<td>".$item['phone']."</td>
<td>".$item['email']."</td>
<td>".$item['notes']."</td>
</tr>"
}
echo "</table>";
...
Method described above, tho not without flows of it's own, gives a possibility to reduce tremendously amount of work related to database integration thru:
therefore making initial writing, rewriting and supporting of the code less daunting a task. TableHandler class is general enough to be re-used not only within the same application but between different application as well. In fact I've been using it (in couple with database abstraction layer) for several months and many projects now.
I hope this article would be of help to some of you.