Is there a better, more "Standard" way to perform SQL queries in PHP without using a framework?

Question

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.

Answer 1

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.

Answer 2

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;";

Answer 3

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.

Answer 4

Doctrine is an ORM wrapped around PDO.

Answer 5

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.

Answer 6

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/

Answer 7

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");

Answer 8

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));