

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

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.
======
ars
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.

~~~
agotterer
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.

~~~
invisible

      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.

------
Maascamp
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.

~~~
nuclear_eclipse
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.

~~~
xenoterracide
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.

------
sapphirecat
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.

------
agotterer
I use a custom object wrapper around the mysql functions

------
cosmo7
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.

------
terrysilver
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>

------
streety
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.

------
pauljonas
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...

------
dshah
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.

------
shafqat
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?

~~~
sgrove
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?

------
pgte
I'm using CakePHP's ORM.

~~~
rami
same here

------
mk
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.

------
alex_c
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.

~~~
jm4
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.

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

~~~
gaius
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...

------
bapbap
Active Record via CodeIgniter for me!

~~~
thorax
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.

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

------
mattdennewitz
check out the ORM in phreeze: <http://www.phreeze.com>

its excellent, and the framework itself is pretty snappy.

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

------
brianr
PDO is good.

------
santacruz
PDO

