SQL query building practices

Question

I am building a SQL query that dynamically changes based on passed in $_GET parameters. I simply want to know if putting in a 'dummy' constraint is an 'acceptable' practice, so I do not have to check the SQL string for the existence of 'where' before I add new constraints (each time).

For example, the 'dummy' constraint':

$sql = "select * from users u where u.id != 0";

Now, in each block where I determine if I have to add more constraints, I can just do:

if (!empty($uid))
    $sql .= " and (u.id = {$uid})";

Instead of doing:

if (!empty($uid)) {
    $sql .= strpos($sql, "where") === false ? " where " : " and ";
    $sql .= " (u.id = {$uid})";
}

Answer 1

I've used that convention (although I usually use WHERE 1=1 AND.) Depending on your RDBMS, using a column there could affect performance. For example, if you had an index that would otherwise be a covering index except for that column.

Please make sure that you understand the potential pitfalls of this kind of dynamic SQL, but if it's what you ultimately end up doing, adding that extra bit seems to make sense to me.

Answer 2

from the manual

SELECT * FROM table WHERE 1

so yes, you can do that

Answer 3

Instead of appending to the SQL string for each check, you could collect the conditions:

if ($_GET["uid"]) {
      $where[] = array("u.id =", $_GET->sql["uid"]);

if ($_GET["photo"]) {
      $where[] = array("u.has_photo =", 1);

And complete the SQL string when you're through:

foreach ($where as $add) {
    $sql .= ...;
}

Otherwise, it's an acceptable approach. I wouldn't turn out the big weapons and use a full blown ORM query builder for that.