4 ms·
Ask YC: Those using PHP, what do you use for talking to your database (MySQL)?
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.
- noodle 18y agotypically through whichever active record type interface provided to me by the framework i'm using.
- mk 18y agoWhen 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.
- streety 18y agoFor 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.
- bapbap 18y agoActive Record via CodeIgniter for me!
- thorax 18y agoLikewise. 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.
- agotterer 18y agoI use a custom object wrapper around the mysql functions
- ars 18y agoThe 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 18y agoI 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 18y agoprivate 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.
- alex_c 18y agomysql/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 18y agoYou 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 18y agoWhy do people always assume that you will move your application to a different DBMS at some point?
- compay 18y agoThat'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.
- cosmo7 18y agoYou'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.
- gaius 18y agoEven 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...
- pauljonas 18y agoIn 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...
- pgte 18y agoI'm using CakePHP's ORM.
- rami 18y agosame here
- Maascamp 18y agoADODB. 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 18y agoADODB 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 18y agonothing 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.
- shafqat 18y agoWe'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?
- rob 18y agoHave you tried Doctrine? It's available as a plugin, I believe.
- sgrove 18y agoWe'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?
- brianr 18y agoPDO is good.
- mattdennewitz 18y agocheck out the ORM in phreeze: http://www.phreeze.com http://www.phreeze.com its excellent, and the framework itself is pretty snappy.
- terrysilver 18y agoI 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 http://devzone.zend.com/node/view/id/686
- markbao 18y agoI use Kohana's ORM, which fully supports its Active Record query builder system.
- sapphirecat 18y agoI 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.
- cosmo7 18y agoZend 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.
- santacruz 18y agoPDO
- dshah 18y agoI'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.