PHP+PDO+MySQL: How I’m doing it and a question…

UPDATE: This post is extremely outdated. It was written in August 2009, 3 years ago at the time of this writing. A lot has changed since then. Today, I strongly recommend using a framework like CodeIgniter, or even WordPress to write any large piece of software.

I recently decided to rewrite an application that I created for an English school where I used to work, and this time I want to do it right. I chose to write it in PHP with Object Orientation in mind, and use PDO+MySQL for the DAO portion.

The reason for using PDO is that it’s the closest thing to the DBI that I found for PHP – and the reason for PHP is that it’s just plain simpler than using pure Perl, and I don’t have the time to learn Catalyst or some other Perl Web framework.

Anyway, here’s what I’m doing for the PDO portion.

First I created a Db class which is just a DB connection wrapper.

<?php
class Db{

	public $dbh  = null;

	public function Db($db_type,$db_host,$db_user,
			         $db_pass,$db_base,$tbl_prefix){

		if ($db_type == 'mysql') { // in case I want to add oracle in the future

			$mysql_DSN = "mysql:host=".$db_host.";dbname=".$this->db_base;

			try {
				$this->dbh = new PDO($mysql_DSN, $db_user, $db_pass,
							   array(PDO::ATTR_PERSISTENT => true, PDO::ERRMODE_WARNING => true,
							         PDO::ATTR_ERRMODE => true));

			}
			catch(PDOException $e){
				$rc[msg] = $e->getMessage();
				die($rc[msg] . "  " . __LINE__);
			}

			return $this->dbh;

		}

	}

}
?>

Secondly, I created the DAO classes which extend the DB. They are responsible for prepare‘ing and execute‘ing the queries, and returning the data. Here’s an example one (I stripped some bells and whistles in order to not confuse anyone):

<?php
class StudentsDao extends Db {

	public $sth = array();

	// Constructor
	public function StudentsDao($db_type=NULL,$db_host=NULL,$db_user=NULL,
				  	       $db_pass=NULL,$db_base=NULL,$tbl_prefix=NULL) { // These params are to be passed to Db class

		// Connect to DB
		$this->Db($db_type,$db_host,$db_user,
			      $db_pass,$db_base,$tbl_prefix);

		// Prepares queries
		$this->sth = $this->prepare($tbl_prefix); // Look at prepare() further down

	} // end of function StudentsDao

	// Function that prepares queries
	public function prepare() {

		$sth = null; // will hold our temporary array

		// get all students
		$string = sprintf('select * from %s.%sstudents where id = ?',DB_BASE,TBL_PREFIX);	// I have these constants defined in the main page
		$sth['getStudentById'] = $this->dbh->prepare($string);

		// update Student by Id
		$string = sprintf('update %s.%sstudents 
				   set field1 = ?, field2 = ?, field3 = ?
				   where id = ?',DB_BASE,TBL_PREFIX);	// Note that this table has 3 control fields (id, sys_creation_date, and sys_update_date)  prior to field1, field2, and field3 - I don't want them to be touched

		$sth['updStudentById'] = $this->dbh->prepare($string);

		return $sth;

	} // end of prepare()

	// Here we have the handlers for the queries prepared above.

	// Fetches all students
	public function getStudentById($id) {

		$s = $this->sth['getStudents'];
		$s->execute(array($id)) || die(print_r($s->errorInfo,1));
		$result = $s->fetchAll(PDO::FETCH_ASSOC);

		if (sizeof($result) == 0) {
			// no data found
			$out[err_cd] = 1;
		}	
		else {
			// data was found
			$out[err_cd] = 0;
			$out[data] = $result;
		}

		return $out;

	}

	// Updates student by Id - this is a tricky one
	public function updateStudentById($data,$id){ // $data can have data regarding one or more fields

		// Populate current fields
		$fields = $this->getStudentById($id); // get current values

		// Remove unwanted fields
		$fields = array_slice($fields[data][0],3); // remove the 3 initial control fields

		// Replace with new fields
		foreach($data as $k=>$v){
			$fields[strtolower($k)] = $v;
		}
		$fields['id'] = $id;

		$s = $this->sth['updStudentById'];
		$s->execute(array_values($fields)) || die(print_r($s->errorInfo,1));
		$count = $s->rowCount();

		if ($count == 1) {
			$out[err_cd] = 0;
		}
		else {
			$out[err_cd] = -1;
		}
		$out[err_msg] = $this->status[$out[err_cd]];
		return $out;

	}
?>

In the example above, you will probably frown on the fact that I’m calling getStudentById() at the beginning of updateStudentById(). It’s OK – I frown on it, too. Let me explain why I did that:

I wanted to be able to pass the function an associative array containing only the fields I want to update – be it only field1, or field1 and field2 and so on. But I wanted to do that against my already prepared query, and not touching any additional fields. In my application, students table has a BUNCH of fields. In other words, update any given field or set of fields using the single prepared statement, without having to set ALL the fields in $data

My original idea was to basically, to emulate something like this:

update students set field1=field1, field2='some new value ', field3=field3 where id = 1

That would set field1 to whatever value field1 already had.

Here’s the question part:
Unfortunately, I couldn’t figure out how to do that using the PDO (how to bind a field name instead of a value against the ?-mark). If you know how to do that, do leave a comment explaining how! 😀

The technique shown in the example will basically pull all values from the record and populate them in the $fields array. The array_slice() is used to remove the control fields that I have in my table, and the rest gets compared against the $data array and if any matching fields are found in $data, those fields get their values assigned over the existing $fields values.