Inserting and selecting a row: the "submit()" and "load()" methods - PHP

With websites now featuring full-blown dynamic applications that link to databases, data accessing has become a critical process. Often, an object-oriented solution is wanted to manage the data access operations. This works well, except when certain statements are hard-coded in that can cause headaches when a update is required. Alejandro Gervasio explains how a new category of tools, known as database interfaces, help to solve this problem.

TABLE OF CONTENTS:

By: Alejandro Gervasio

Poor Best

Rating:

/ 53

August 10, 2005

print this article

SEARCH DEV SHED

TOOLS YOU CAN USE

As stated previously, here's the code to generate the "submit()" method:

// create "submit()" method
$str.='function submit(&$db){'."n";
// build insert query
$str.='$sql="INSERT INTO users SET ';
foreach($this->options as $option){
$str.=($option!='id')?$option.'='$this->'.$option.'',':'';
}
$str.='";'."n";
// remove trailing comma
$str=preg_replace("/,"/",""",$str);
// perform query
$str.='$db->performQuery($sql);'."n";
$str.='$this->id=&$db->getInsertID();'."n";
$str.='}'."n";

As you can appreciate, this method simply packages an "INSERT" clause into the class file and performs the row insertion, by executing the query. Please notice that each method accepts a reference of a MySQL database connection object &$db, for purposes of database connectivity and being able to execute the SQL statement. In this case we're not using the native MySQL PHP functions. Instead, we're utilizing the method "performQuery()" provided by the object $db. Also, the ID of the row inserted is stored, by calling the "getInsertID()" method.

A similar approach is taken with the rest of the methods, thus the "load()" method is generated as listed below:

// create "load" method
$str.='function load(&$db){'."n";
// build query
$str.='$sql="SELECT * FROM users WHERE id='$this->id'";'."n";
// perform query
$str.='$result=$db->performQuery($sql);'."n";
$str.='$row=$result->fetchRow();'."n";
$str.='return $row;'."n";
$str.='}'."n";

In this case, the "load()" method encapsulates a SELECT statement, in order to retrieve an specific row. In this scenario, it's easy to guess how the other methods will be generated. Take a deep breath and join me in the next explanation.

Next: Updating and deleting a row: defining the "update()" and "delete()" methods >>