0

For the longest time, I've been using the following basic formatting for SQL queries within my PHP:

$sql = "SELECT * FROM `user-data` WHERE `id` = '".$id."' LIMIT 1;";
$fn = mysql_fetch_assoc(mysql_query($sql));

While this works flawlessly, it can get really messy for longer bits of code, and something deep inside of my conscience cringes at the string concatenation every time I do it. Still, it works and I use it almost everywhere without major issues. (That's a tame example, I'm not dense enough to pass user data directly into an SQL string without escaping it first, etc etc.)

What I'd like to do is something a bit more object oriented, but I'm not sure what the best approach would be. It'd be nice to just be able to sql->insert($values [, $where, $extra]); or something similar, using PHP's natural Associative Array types to pass in the query strings in a more simplified manner. Less flexible? Yes. More readable? Heck yes, and harder to make "silent" syntax errors at that.

What are the community's takes on this? What approaches have you seen to this problem that were the most effective for projects you were working on?

Not that it matters, but I personally don't do much more complicated than SELECTs, INSERTs, and UPDATEs, with occasional nesting of subqueries, but that's mostly because my SQL flavor doesn't do stored procedures.

Blank
  • 7,088
  • 12
  • 49
  • 69
  • While I'm quite aware that there are bound to be frameworks that exist to solve this, I'm trying to avoid those answers. I'd like to know how one would accomplish this starting out with just a vanilla PHP install, with a focus on examining the underlying code that makes it happen. – Blank Nov 06 '09 at 04:16

8 Answers8

4

PDO is a good, solid, secure solution that many frameworks build off of. If you're going to start from the bottom, PDO is a solid foundation.

Mike B
  • 31,886
  • 13
  • 87
  • 111
0

Maybe it would make you a little happier at least to use PHP's string variable substitution:

$sql = "SELECT * FROM `user-data` WHERE `id` = '$id' LIMIT 1;";
Grumdrig
  • 16,588
  • 14
  • 58
  • 69
0

There is MDB_QueryTool I never tried.

IMHO Zend_DB is really cool, the zend framework allow you to use only the part you are interested in so you might want to take it a look event if you don't want the full framework.

what I like in Zend_DB is the table select syntax

$userRowset = $user->fetchAll( $user->select()
 ->where('name LIKE ?', $name . '%')
 ->order('id ASC')
 ->limit(10) 
);

You can easily see all the criterias and table involved so I find better then doing plain SQL. Just one warning Zend_DB doesn't handle all the SQL, so time to time you would have to write plain SQL but that's really rare.

RageZ
  • 26,800
  • 12
  • 67
  • 76
0

Doctrine is an ORM wrapped around PDO.

moo
  • 7,619
  • 9
  • 42
  • 40
0

I've been wondering why I am always seeing the more complicated form of string building like this: "literal string " . $a . " more literal", rather than "literal string $a more literal", or in your case:

"SELECT * FROM `user-data` WHERE `id` = '".$id."' LIMIT 1;";

instead of this:

"SELECT * FROM `user-data` WHERE `id` = '$id' LIMIT 1;";

For more complicated expressions, I like to use sprintf (but I was a c programmer for a long time):

$sql = sprintf("SELECT * FROM `user-data` WHERE `id` = '%s' LIMIT 1", $id);

This can also be written in this format:

$sql = sprintf("
    SELECT * 
       FROM `user-data` 
    WHERE `id` = '%s' 
        LIMIT 1", 
    $id);

In this case, it doesn't buy much, but when there are several variables embedded in the string, it makes it easier to manage.

Marty Fried
  • 487
  • 1
  • 5
  • 12
0

Another vote for doctrine. Don't waste your time with PDO. I can't emphasize this enough. Go with an orm. Forget about wasting time writing CRUD methods, custom caching logic, and worrying about premature optimization such as "overhead" resulting from a library. The overhead incurred by spattering statements like "select * from app_users" and their associated ugly heredocs isn't worth it.

If you need to fall back to sql, you can. The other 90% of the time you're in a state of bliss.

http://www.doctrine-project.org/

Koobz
  • 6,928
  • 6
  • 41
  • 53
  • Nick, I noticed your comment about understanding the underlying code. About two weeks into writing your own sql, you're going to start seeing patterns emerge. You might still be reluctant to use an ORM though ('its so much more than I need!'), and start writing your own utility functions that capture these emerging patterns and idioms. Two weeks after that you're going to look at doctrine and realize it blows your utilities out of the water. You'll see it's awesomely documented, unit tested and stable. Even from a pedagogical perspective an orm is good. Watch the db logs if you're curious;) – Koobz Nov 06 '09 at 07:55
  • Just need to add. If you're interested in making applications Doctrine is the way to go. If you're interested in writing your own database framework, that's when you'll want PDO. But that's not the fun part of web development :) Doctrine has already done this and they're a couple of generations into it so lessons have been learned and refinements made (Adodb, Pear, MDB2, Propel, Doctrine). Can't emphasize it enough: try the ORM, especially because it sounds like you haven't before, and cry tears of joy. – Koobz Nov 06 '09 at 08:03
  • Doctrine run trought PDO. Doctrine may be right for big projects, but, if he uset to write the query as "$sql = "SELECT * FROM `user-data` WHERE `id` = '".$id."' LIMIT 1;";", i dont think learning Doctrine does worth the price. – Strae Nov 06 '09 at 08:45
  • If it's the only query you ever write in your php career then yes, don't spend time learning doctrine. In the context of application development, the sooner you decide to use an orm the better IMO. Many projects start small, grow big, and at that point refactoring a bunch of bad sql is not fun. Even in the case of user data, what happens when you want to search by firstname, lastname. Order by different criteria? Your data layer is flaky and inflexible to schema changes. – Koobz Nov 06 '09 at 09:22
  • I agree with the use of a framework totally, and I do fully intend to use one. However, I'm mostly interested here in how said framework works, and what the major differences are. For example, so far I'm liking the look of the Zend_DB framework (in terms of code) a lot more. Doctrine looks powerful, but also way complex to learn, especially since I rarely do anything more complex than basic SQL commands. (I just use a *lot* of them.) – Blank Nov 06 '09 at 18:43
  • Also, upvoting, since this is a good answer and doesn't deserve a downvote? – Blank Nov 06 '09 at 18:44
0

You can use mysqli to write little place holders in you SQL and then fill them in. It should be less susceptible to SQL injection attacks than string concatenation.

$conn = new mysqli($server, $username, $password, $database);
$stmt = $conn->prepare('SELECT * FROM people WHERE age = ? AND name != ?');
$stmt->bind_param('is', 20, "Austin");
Austin
  • 2,771
  • 1
  • 18
  • 14
0

Try:

$stat2 = <<<SQL
SELECT *  from YOUR.DET_TABLE
WHERE ID = ?
ORDER BY ID, EFF_DT
SQL;
  $d_cur = $conn->prepare($stat2);
  $status   = $d_cur->execute(array($selected));
James Anderson
  • 27,109
  • 7
  • 50
  • 78