Generating SQL query based on URL parameters

Question

Suppose my URL is http://something.com/products.php?brand=samsung&condition=new

For the above query I am using isset() and $_GET[]) functions along with lots of if-else statements in PHP to generate a sql query for displaying the products which satisfy the search criteria.

For example: if I am dealing with only brand and condition parameters then this is how I will generate the query:

$sql = "select * from products where 1=1 ";
if(isset($_GET['brand']))
{
     if(isset($_GET['condition']))
     {
         $sql = $sql + "and brand=".$_GET['brand']." and condition=".$_GET['condition'];
     }
}
else
{
     if(isset($_GET['condition']))
     {
         $sql = $sql + "and condition=".$_GET['condition'];
     }
     else
     {
         $sql = $sql + ";";
     }
}

Now suppose my URL is having 10 parameters (or more). In this case, using if-else is not at all good. How can I generate the query without using so many if-else statements? Is there any better method/script/library available for doing this thing?

cwallenpoole · Accepted Answer · 2011-08-03 02:56:24Z

5

There are a number of ways to do this, but the easiest way would be to loop through the acceptable columns and then append appropriately.

// I generally use array and implode to do list concatenations. It avoids
// the need for a test condition and concatenation. It is debatable as to
// whether this is a faster design, but it is easier and chances are you 
// won't really need to optimize that much over a database table (a table
// with over 10 columns generally needs to be re-thought)
$search = array();
// you want to white-list here. It is safer and it is more likely to prevent
// destructive user error.
$valid  = array( 'condition', 'brand' /* and so on */ );


foreach( $valid as $column )
{
   // does the key exist?
   if( isset( $_GET[ $column ] ) )
   {
      // add it to the search array.
      $search[] = $column . ' = ' . mysql_real_escape_string( $_GET[ $column ] );
   }
}
$sql = 'SELECT * FROM TABLE_NAME WHERE ' . implode( ' AND ', $search );
// run your search.

If you really are trying to get rid of the 'if' statements, you could use this:

$columns = array_intersect( $valid, array_keys( $_GET ) );
foreach( $columns as $column )
{
    $search[] = $column . ' = ' . mysql_real_escape_string( $_GET[ $column ] );
}
$sql = 'SELECT * FROM TABLE_NAME WHERE ' . implode( ' AND ', $search );

But you may want to run actual benchmarks to determine whether that is a substantially better option.

edited Aug 3, 2011 at 2:56

answered Aug 3, 2011 at 2:45

cwallenpoole

82.4k26 gold badges132 silver badges174 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

sumit Over a year ago

Thanks for the answer. But your answer will execute the same number of if-else statements as my code, which will generate a lot of load on the server.

Chris Hepner Over a year ago

@iSumitG In comparison to everything else, if/else statements like this are really fast. As in, thousands of times faster than a database query. The question here really should be "how do I make this code more readable/reusable?" rather than faster. If you think it matters, benchmark the performance of the two and see if it makes a significant difference.

cwallenpoole Over a year ago

@iSumitG Actually, from the looks of it, your code will produce O(n^2) if/else conditions. Mine will only produce O(n) conditionals where n = number of columns in the white-list (which is pretty optimal). I added a secondary block to allow you to remove the actual use of if, but that is still O(n) (the difference is that n is the value of whichever array is smaller). But, because the creation of the Array takes time, I'm not certain that the use of array_diff + array_keys will really save that much time over the raw white-list. You will need to benchmark.

Collectives™ on Stack Overflow

Generating SQL query based on URL parameters

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related