CODEWITHSUNDEEP
phpsql
danjarvis
danjarvis

Reputation: 10190

SQL query building practices

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

Upvotes: 1

Views: 137

Answers (3)

mario
mario

Reputation: 145482

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.

Upvotes: 1

Grumpy
Grumpy

Reputation: 2243

from the manual

SELECT * FROM table WHERE 1

so yes, you can do that

Upvotes: 0

Tom H
Tom H

Reputation: 47454

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.

Upvotes: 2

Related Questions