Matching variables and DB columns
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”, “Touchstone”);
Because the id column is auto-incremented, it will happen to have the value 1 for this first row.
<?php
include_once(“dbconnect_vars.php”);
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 =
mysql_fetch_array($result);
$class_var_entries =
get_class_vars(get_class($this));
while ($entry = each($class_var_entries)) {
$var_name = $entry[‘key’];
$var_value = $entry[‘value’];
if ($var_value == “DBSET”) {
$this->$var_name =
$db_row_array[$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
