Ask YC: Those using PHP, what do you use for talking to your database (MySQL)? | Hacker News Reader

Ask YC: Those using PHP, what do you use for talking to your database (MySQL)?

9 pointsrob35 comments

Say, for example, you're working with MySQL. Are you using the mysql/mysqli_* functions, PDO's drivers, ADOdb, etc? Or perhaps an ORM like Doctrine or Propel? I'm just curious to see what others are using, as there are a lot of choices out there.

View on HN

The regular mysql functions, except I made a wrapper for mysql_query to abort on an error, so I don't have to type that every time. But my wrapper explicitly allows you to use the regular mysql_query function if you want. I hate wrappers that hide the underlying system.

I don't wrap the mysql_fetch* functions because that slows them down (lots of string copying). Plus, they don't need it.

I also like:

  function ms($string) {
    return "'" . mysql_real_escape_string($string) . "'";
  }

Very helpful for preventing sql injections, it's so short to type that I always remember to use it. And by including the quote marks right in it I don't have to go nuts escaping the quotes in the outer query.

If you use mysqli you don't need this - use bind variables instead.

I use this to clean all my strings:

private function clean_string($string, $strip_tags = true) {

     if (!strlen($string)) return NULL;

     if (get_magic_quotes_gpc()) $string = stripslashes($string);
		
     if($strip_tags)
	$string = strip_tags($string);
		
     if (!is_numeric($string)) {
         $string = mysql_escape_string($string);
     }
		
     return $string;

}

Edit: You only need to use mysql_real_escape_string if you are inserting binary data or need to use database character sets. mysql_real_escape_string also requires a mysql connection and link identifier.

  private function clean_string($string, $strip_tags = true,$empty_null = true) {
    if (!strlen($string) and $empty_null) return NULL;
    if($strip_tags)
      $string = strip_tags($string);
  
    return get_magic_quotes_gpc() ? mysql_escape_string(stripslashes($string)) : mysql_escape_string($string);
  }

This is most likely faster because you're not changing the memory so frequently. In this is_numeric isn't needed because it'd just slow things down. It's really good practice to use mysql_real_escape_string, but I respect this way for simplicity's sake.

ADODB. I've used PDO as well (which is slightly faster), but I found ADO to be a little more robust. Also supports memchached out of the box.

ADODB also has the excellent feature of a shorthand, generalized SQL format that's incredibly useful for specifying cross-platform schema.

We use this in the Mantis project to define a list of sequential schema definitions, which then gets applied one-by-one to generate or upgrade an installation's database, and it works on MySQL, PostgreSQL, MS SQL, Oracle, and DB2.

nothing against adodb but if mantis is any clue it doesn't solve this problem. I've tried installing postgres in mantis and it was a no go due to known bugs, related to the area of schema definitions.

I used PDO a lot at my last job; it's one of the decisions I regret. Whenever I tried to do anything somewhat advanced, it had a tendency to segfault. Especially bothersome to track were segfaults due to leaving a statement open: at least through 5.1.6, PHP destroys objects at end-of-request in order by creation, and PDO doesn't like having the connection destroyed before its statements.

At my current place, I inherited a badly-designed wrapper class around OO-style mysqli (and stored procedures); the mysqli part seems to be pretty good.

I use a custom object wrapper around the mysql functions

Zend Framework's Zend_Db is nice - usually something like

   $select = $db->select();
   $select->from( 'T_Shirt' );
   $select->where( 'something = ?', $a );

And so on. You still have to write queries longhand if you're doing something more complex that a few joins, but Zend_Db is pretty handy.

I have used the basic mysqli functions everyone uses in past projects but for my latest I started using mysqli prepared statements. I don't have benchmarks but they're supposed to be faster and more secure:

http://devzone.zend.com/node/view/id/686

For me it depends entirely on the project. If it is a tiny little thing I'm going to be finished with in an hour I might use the mysql/mysqli functions. For larger projects I've used MDB2 until fairly recently. I've been playing around with ZF for the past few months though and have been using Zend_Db.

In recent projects, PDO.

Prior, used a custom object wrapper around the mysql functions — and it would be really simple change to swap out a different DB platform as all DB calls are abstracted and located in one class file...

I'm currently using DB_DataObject.

But am considering switching to Zend or CodeIgniter.

DB_DataObject is working fine for now (it's lightweight, and I had it working pretty quickly). Saves time without getting in the way.

We're using the Symfony Framework, and connect using the Propel ORM. To be frank, I'm no longer happy with the Propel ORM - I find it quite inefficient. Sigh.

Anyone else using Propel?

We're using Symfony as well, and it's been quite wonderful. Fitting into the framework took some time, but its abstraction allowed us to move from mysql to posgresql for testing, and then back to mysql without much thought at all. Also, optimizing queries for a specific database is fairly simple.

Propel has been a bit of a kludge at times, but it does a decent job. We're experimenting with 1.3 and Doctrine to evaluate our migration path however.

Anything that's hit you as particularly inefficient?

Have you tried Doctrine? It's available as a plugin, I believe.

I'm using CakePHP's ORM.

same here

When using plain PHP I just the mysqli functions. The project I am working on now is using Codeigniter, so I am using it's ORM.

mysql/mysqli calls inside my model classes.

I would say it does depend on the size of the project, so far I've only written small/medium projects built from scratch. I haven't had any reason to regret that decision yet, the code is simple, easy to read, maintain, and optimize.

You will regret that decision when you have to move your app to a different database. The only reason that PDO, ADO, etc. stuff exists is because the built-in database functions are platform-specific and totally inconsistent with one another.

Why do people always assume that you will move your application to a different DBMS at some point?

Even if you've no plans to, be able to is a useful discipline and keeps your code clean. For years I worked on a Solaris project that we also maintained a build for that no-one used on NT. But when a customer wanted an AIX version, it took as long to produce one as it did to find an AIX box to compile it on.

Same is true of databases. There are things that people don't even think about if they've only ever used one database. What's your DB's lock escalation strategy? Are cursors cheap or expensive? What kinds of indexes don't you have, and could any of them ever be useful? And so on...

You'll remember this when someone is suddenly interested in your project and waving wads of cash at you, but it must run on SQL Server, today.

Are you kidding me? It does happen occasionally. I'm guessing you haven't been a developer for very long if you've never run into this. The fact that abstraction libraries and ORMs exist in the first place suggests that it's a real problem.

My comment was based on my own experience having to move a PHP app from SQL Server to Oracle. And it was just about the last app I'd ever expect to have to move from one database to another.

I'm not sure why my original comment was modded down because it's just plain common sense. With all the libraries out there for abstracting database access and ORMs you're just begging for trouble if you still use the database specific stuff. Unfortunately, in my case none of that stuff was available yet.

That's a good point. In 10 years I've only had to do that once.

Still, I like using an abstraction layer because they tend to hide the quirks of the particular database - I use MySQL, Postgres and SQLite all fairly often, and by using PHP's PDO I can not worry about the differences as much as if I were using the database-specific drivers.

I am aware that I might regret that decision IF I have to move my app to a different database.

Even so, I don't think such a move would be that painful. I've taken great care to isolate all SQL queries from the rest of the code, and to keep them all in one place. I also have queries which I've had to optimize for the specific database I'm using (MySQL). I doubt an out-of-the-box DAL would be able to help with those in the current incarnation, or eliminate the need to rewrite them if the database changes.

The biggest challenge I can foresee is if they grow to the point where I need multiple databases in order to scale. I don't have enough experience yet to know whether existing frameworks would help in such a scenario, or just get in the way. But changing from MySQL to PostgreSQL? I'm fairly confident I can live with that.

But first and foremost, they're small-to-medium projects (in terms of code size), with relatively simple data models, and a single developer. I wouldn't advocate this approach for large projects.

The problem with moving to a different database when you've used the database specific functions in PHP is that you have to use completely different functions for the new database. Moving from MySQL to PostgreSQL isn't so bad because those functions are fairly consistent. I think in most cases you just change mysql_ to pg_.

Moving to something like Oracle or ODBC is a major task. You end up having to rewrite quite a bit of code even if you've isolated your queries. I had to learn this the hard way through experience and thought I'd share so someone else could avoid the hassle. The app that I had to move was very small and about the last thing I thought I'd have to spend much time maintaining. Six months later I was wasting half a day rewriting database code. Not sure why I would get modded down for this.

Active Record via CodeIgniter for me!

Likewise.

Their version of Active Record isn't the same as those from other frameworks, but we use the built-in one from Code Igniter. There's another floating around that's closer to the RoR style.

I use Kohana's ORM, which fully supports its Active Record query builder system.

check out the ORM in phreeze: http://www.phreeze.com

its excellent, and the framework itself is pretty snappy.

typically through whichever active record type interface provided to me by the framework i'm using.

PDO is good.

PDO

Reply on news.ycombinator.com