4

I am new to PDO and not totally sure if I am looking for my desired usage in the right place/way.

PDO seems very nice but I find this kind of code rather long and don't want to use it for every query that I run:

$sth = $database->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->execute(array(':calories' => $calories, ':colour' => $colour));

I rather use something like this:

$array = 
 'calories' => $calories,
 'colour' => $colour
);
$db->insert("fruit", $array);

(Similarly I'd like to make update calls, and I want this to work for each insert/update, so that the key name is taken as the field in the table, and the value as the value to insert/update).

What I value is:

  • Having minimal code in my own scripts, using only arrays and dealing with the PDO statements (including error capturing) somewhere else.
  • Still taking advantage of the prepared statements and object-oriented programming that comes with PDO.

I found many and answers but they propose many different types of solutions including:

All in all, there seem to be many options but I have no good idea of what the most recommended way is for my purpose, or even what the specific differences are.

Please illuminate me: what and where should I be looking for here?!

Community
  • 1
  • 1
  • 7
    PDO is a raw SQL driver interface. The only convenience it provides uniformly are bound parameters. So yes, helper functions or abstractions are the way to go. Just like nobody is using unadulterated JavaScript anymore, you shouldn't write tedious PDO incantations. There's however no one-size-fits all. ORMs and query builders are often portrayed as SQL training wheels, while Active Record or CRUD wrappers simplify the `->insert` use case. Depending on your DB structure custom utility functions (at least for array binding or simpler param binding) can be more productive. – mario Aug 31 '14 at 04:47
  • @mario Thanks, that is the kind of answer I was looking for! Could you make this an answer? If you could throw in a couple examples of each or recommendations on how to select between the different options that would make it even more useful! –  Aug 31 '14 at 05:02
  • "ORMs and query builders are often portrayed as SQL training wheels" That's a really awful way of putting it. They are in no way "training wheels". You use an ORM when you trust it to emit the correct query, and you bypass it or customize it when you can't. 95% of the time a good ORM will do the right thing and with far less code and risk of error than doing it by hand. – tadman Aug 31 '14 at 07:39
  • @tadman I'd say that very much depends on your kind of application. A mostly plain CRUD application will probably do fine most of the time with an ORM. If you have somewhat complex data structures though with a high degree of database normalisation and/or you're using special features of a specific database an abstract ORM doesn't support, it may be much more work to configure the ORM and make it behave than it would be to write queries by hand. If you find that you're bypassing your ORM more than half the time, it's probably not worth it. – deceze Sep 08 '14 at 10:41
  • @deceze Most people aren't writing those sorts of applications, and even those that are, the bulk of the queries can be handled with a regular ORM. Just because you have a few edge cases doesn't mean you need to subject yourself to the world of hurt that is writing every query by hand. – tadman Sep 08 '14 at 14:23

2 Answers2

0

There's a number of systems for eliminating routine queries and replacing them with higher-level code.

One is to use an ORM like Propel or Doctrine.

Even better is to use a full development framework like Laravel that helps you with a lot more than just database queries. Most applications will need to consider HTML escaping, XSS protection, URL mapping, authentication, session management, and more. Writing this all yourself is difficult and error-prone.

tadman
  • 208,517
  • 23
  • 234
  • 262
0

Well as mentioned in other answers the gentle way to do that things is to use some orm in your case orm which has also pdo support.

For example doctrine. It is very complex and powerfull. If you want to use some lightweight and yet full functional orm there are options for that to.

For example db.php (http:\dbphp.net) and also have a look for the simplest usage demo of that framework https://github.com/hazardland/db.php/blob/master/samples/001.showcase.php

BIOHAZARD
  • 1,937
  • 20
  • 23