Bernhard Häussner

My MySQL API onion for PHP

26.07.2009, 17:42
MySQL Onion

MySQL Onion

A huge part of web applications is usually the interaction with the SQL database. This is why I want as little work as possible connecting, escaping values, getting the right tables an so on in PHP. But it should stay simple and allow modular approaches. Therefor I'm using some nested APIs for doing queries easily:

PDO

The very fist thing I am using is PDO. It can handle many RDBMS, but I am most of the times using MySQL or SQLite. By using PDO as an API for the following layers I can make sure most of the code will work for many RDBMSs. PDO even simplifies transactions and prepared statements. Here's some sample PHP code using PDO:

$pdo=new PDO('mysql:host='.$host.';dbname='.$db, $user, $passwort);

$pdo->exec('UPDATE test SET foo="bar" WHERE id=4');

$satement=$pdo->prepare('SELECT * FROM blogeintraege WHERE id=:id');
$satement->bindValue(':id',3,PDO::PARAM_INT);
$satement->execute();
$data=$satement->fetchAll();

PDO Simplifier

The next layer is a class that will hold a MySQL database Connection (a PDO Object) and offer some simple functions for doing e.g. a simple prepared statement. Instead of binding each values manually, you can throw an array in.

It also includes a cache, if you want to run statements more than once. It can append a prefix to all queried tables and checks dynamically inserted tables for validity to avoid SQL-injections and MySQL errors. It is used like that:

$res=$db->sql("SELECT * FROM blogeintraege");
$res=$db->sql(
  "SELECT * FROM #test WHERE id=:id",
  array('id'=>$id),array('id'=>PDO::PARAM_INT),
  array('test'=>'blogeintraege'),
  array('limits'=>array(0,$l),'buffered'=>false)
);

For one array element this does not look too tiny, but the more values are bound, the more useful it gets. And it is very useful if you already have your values in an array, like $_GET.

Note that nearly everything is optional. The table array can contain more tables, for example you can have an array of tables for different languages, if they are in different tables. The bind-types don't need to be specified too. You can even leave out everything except the query as shown in the fist line of code. The Result will by default be returned as a nice array (the GROUP_CONCAT fields are array'ed too) but you can use all other PDO fetch types.

This layer follows a rather functional approve, so I needed another layer for accessing the central sql()-Function in an OOP manner. This should avoid some runtime errors and you can modify the SQL in a modular system.

Statement builder

So I created a wrapper object, that holds a pointer to the database and will construct the parameters for sql(). This comes in handy as more and more optional parameters are added.

The PDO Simplifier has a method to build such statement-objects called sqlO(). This is how the wrapper is used:

$db->sqlO('INSERT INTO blogtaglinks SET ##,type=3')
   ->setSet(array('ID_tag'=>$lasttagid,'ID_entry'=>$id))
   ->exec();
$res=$db->sqlO("SELECT * FROM #test WHERE id=:id")
        ->setData(array('id'=>$id))
        ->setDataTypes(array('id'=>PDO::PARAM_INT))
        ->setTables('test'=>'blogeintraege'),
        ->setLimits(0,$l)
        ->setBuffered(false)
        ->exec();
);

As you can see, it is a little more code, but the code is pretty self-explanatory and now one can build the sets and the other parameters as arrays and then include them easily in the statements.

A bit different: Zend Framework's $db->select() approach

A next step would be to build queries with a single API. This is a feature implemented by the Zend Framework, where you can build your SQL with some API functions and it will even work across various databases:

select = $db->select()
  ->from('blogeintraege',array('id','Titel'))
  ->where('id < ?', $id)
  ->order('id DESC')
  ->limit(0,10);

Well doesn't that look nice?

Kurze URL http://1-co.de/b/1G. Post to twitter

Kommentare

keine





 
Χρόνογραφ
© 2008-2017 by Bernhard Häussner - Impressum - Login
Kurz-Link zu dieser Seite: http://1-co.de/b/1G