Matching variables and DB columns

Jan 07, 2011 Author: Dusan

One frequent use for PHP objects in database-driven systems is as a wrapper around the entire database API. The theory is that the wrapper insulates the code from the specific database system, which will make it trivial to swap in a different RDBMS when the technical needs change. (We’ve never seen it work out quite this way in practice, but . . . don’t get us started.) Another use that is almost as common (and that your authors like better) is to have object instances correspond to database result rows.

In particular, the process of reading in a result row looks like instantiating a new object that has member variables corresponding to the result columns we care about, with extra functionality in the member functions. As long as the fields and columns match up (and as long as you can afford object instantiation for every row), this can be a nice abstraction away from the database.

A repetitive task that arises when writing this kind of code is assigning database column values to member variables, in individual assignment statements. This feels like it should be unnecessary, especially when the columns and the corresponding variables have exactly the same names. In this section, we write a hack to automate this process.

For concreteness, let’s start with an actual database table. Following are the MySQL statements necessary to create a simple table and insert one row into it:

mysql> create table book
(id int not null primary key auto_increment,
author varchar(255), title varchar(255),
publisher varchar(255));
mysql> insert into book (author, title, publisher)
values (“Robert Zubrin”, “The Case For Mars”,

Because the id column is auto-incremented, it will happen to have the value 1 for this first row.
class Book
public $id;
// variables corresponding to DB columns
public $author = “DBSET”;
public $title = “DBSET”;
public $publisher = “DBSET”;
public function __construct($db_connection, $id) {
$this->id = $id;
$query = “select * from book “ .
“where id = $id”;
$result = mysql_query($query, $db_connection);
$db_row_array =
$class_var_entries =
while ($entry = each($class_var_entries)) {
$var_name = $entry[‘key’];
$var_value = $entry[‘value’];
if ($var_value == “DBSET”) {
$this->$var_name =

public function rename () {
$return_string = “BOOK
”; $class_var_entries = get_class_vars(get_class($this)); while ($entry = each($class_var_entries)) { $var_name = $entry[‘key’]; $var_value = $this->$var_name; $return_string .= “$var_name: $var_value
”; } return($return_string); } } $connection = mysql_connect($host, $user, $pass) or die(“Could not connect to DB”); mysql_select_db(“oop”); $book = new Book($connection, 1); $book_string = $book->rename(); ?> <HTML><HEAD></HEAD><BODY> <?php echo $book_string ?> </BODY></HTML>

The database query returns all columns from the book table, and the values are indexed in the result array by the column names. The constructor then uses get_class_vars() to discover all the variables that have been set in the object, tests them to see if they have been bound to the string “DBSET”, and then sets those variables to the value of the column of the same name.

The result is the output: BOOK Author: Robert Zubrin Title: The Case For Mars Publisher: Touchstone

views 6669
  1. Add New Comment