When dealing with data, one finds that, essentially, the same code is repeated in various incarnations:
-- MySQL:
CREATE TABLE users (
id int NOT NULL auto_increment PRIMARY KEY,
name varchar(255) NOT NULL,
email varchar(255) NOT NULL,
UNIQUE KEY akUser (email)
)
// PHP
class User extends DBModel {
protected $id;
protected $name;
protected $email;
function __construct() {
$this->queries = array(
'getById' => 'SELECT id, name, email FROM users WHERE id = :id',
'addUser' => 'INSERT INTO users (name, email) VALUES (:name, :email)',
'updateUser' => 'UPDATE users SET name = :name, email = :email WHERE id = :id',
);
}
public function loadById($id) {
$stmt = $this->getPreparedStatement('getById');
$stmt->bindValue(':id', $id);
$stmt->execute();
$stmt->setFetchMode(PDO::FETCH_INTO, $this);
return $stmt->fetch();
}
public function save() {
if ($this->id === NULL) {
$this->add();
}
else {
$this->update();
}
}
public function add() {
$stmt = $this->getPreparedStatement('addUser');
$stmt->bindValue(':name', $this->name);
$stmt->bindValue(':email', $this->email);
$stmt->execute();
}
public function update() {
$stmt = $this->getPreparedStatement('updateUser');
$stmt->bindValue(':name', $this->name);
$stmt->bindValue(':email', $this->email);
$stmt->bindValue(':id', $this-id);
$stmt->execute();
}
}
If you notice, the fields 'name' and 'email' each occur 7 times throughout PHP and MySQL. While this is not much of a problem with this small example, with large tables, and a large number of tables, this gets out of hand fairly quickly.
As a quick side-note: with PHP running as a web service, the application lifetime is very short. Many of the queries that get executed — adding a user, for instance — would get executed only once in the application lifetime, so prepared statements really don't offer much of an advantage. Even with connection sharing, the individual instances don't have access to the previously prepared statement (unless I'm missing something), so it would re-prepare the statement anyway.
With some basic database-level reflection (the results of which are either cached, or automatically generated before deployment), SQL statements can be created automatically, eliminating the need to code the basic SELECT/INSERT/UPDATE/DELETE statements individually – cutting down on code considerably.
-- (table create statement, of course, stays the same)
class User {
protected $id;
protected $name;
protected $email;
public function getById($id) {
return MyDb::getById('Users', $id, $this);
}
public function save() {
return MyDb::save('Users', $this);
}
}
My question: is the duplication of code worth the few CPU cycles and more complex code & maintenance needs?
Best Answer
Essentially you're asking whether code cleanliness is more important than performance; well, that can vary depending on the situation. Without really knowing more about the exact situation, I would say: