Object-oriented MySQL query interface in PHP

Question

I'm building a MySQL query "interface". It will allow me to make selects more easily. However, i'm having a doubt about passing empty parameters. Some of my function parameters are not required, so, i'm passing them empty.

For example: query('table', '*', '', '', '0,10');

Being: query($table, $from, $where, $orderby, $limit)

Is this against some best practice? Am i doing it wrong?

Answer 1

Try it like this:

$query = new Query();
$query->setTable($table);
$query->setFrom($from);
$query->execute();

That way you can basically have named and/or optional parameters, even though PHP doesn't support them.

Answer 2

I would set the default arguments in the function itself, so you can control if is a default value to just ignore it... like this...

query($table, $from=null, $where=1, $orderby=null, $limit=10)

Answer 3

If you want to build this yourself because it is your task or because you want to do it for the sake of learning, go ahead. Otherwise don't do it yourself, that's exactly what frameworks are for. Zend_Db (Zend Framework) for example, does this all very neatly.

If you want to have optional parameters, you have to define default values in the function. The optional parameters have to follow after the non-optinal ones.

An even better (more flexible, better maintainability) approach would be to add each part of the query by its own function. Create a query class and create methods to add all the different parts of the query to the query object.

Answer 4

a seperate Query Builder class seems the best way to go, I have used Doctrine ORM for a while and it also had a seperate query builder.

But if this isn't for learning purposes I highly suggest using an existing solution as Doctrine or Zend_Db (I have only used doctrine however).

Answer 5

if you want a real OOP interface, you'd better to get familiar with Doctrine.

$q = Doctrine_Query::create()
    ->select('u.id')
    ->from('User u')
    ->whereIn('u.id', array(1, 3, 4, 5));

echo $q->getSqlQuery();

(example snipped from the other recent question)

Doctrine query builder will let you avoid empty parameters as well as procedural style (which, to be honest, has nothing to do with your OOP intentions) and many more things to use and to learn from.

Answer 6

If you are trying to learn OOP, then try PHP Data Object - PDO. It provides a consistent OOP interface for accessing databases from PHP. It is already defined as an extension for PHP, so you better try to learn and use it rather than inventing the wheel again.

But if you still insist on making an Object Oriented Database Access layer, then you should follow this answer. I think it is pretty neat.