PHP SQL: Way to skip over section of a query if variable is blank

Question

I'm writing a query that uses input from a search form where Brand, Type and Price are optional input fields:

SELECT * FROM `database` WHERE `brand` LIKE "%' . $brand . '%" AND `type` LIKE "%' . $type. '%" AND `price` LIKE "%' . $price . '%"

I am wondering if there is a way to say 'all' if nothing is entered into one of the fields. For example if they do not enter a value in the price field is there a way to tell SQL to just say ignore that section, eg:

AND `price` LIKE "*";

So the reuslts are still filtered by Brand and Type but can have any Price.

Any advice on this is appreciated! Thanks

MidnightLightning · Accepted Answer · 2012-09-13 05:37:57Z

3

As Ariel mentioned, it would be better to have PHP do the filtering as you build the query. Here's a code sample for doing it that way:

<?php
$sql = 'SELECT * FROM `database`';
$where = array();
if ($brand !== '') $where[] = '`brand` LIKE "%'.$brand.'%"';
if ($type !== '')  $where[] = '`type` LIKE "%'.$type.'%"';
if ($price !== '') $where[] = '`price` LIKE "%'.$price.'%"';
if (count($where) > 0) {
  $sql .= ' WHERE '.implode(' AND ', $where);
} else {
  // Error out; must specify at least one!
}
// Run $sql

NOTE: Please, please, please make sure that the $brand, $type, and $price variable contents are sanitized before you use them this way or you make yourself vulnerable to SQL injection attacks (ideally you should be using the PHP PDO database connector with prepared statements to sanitize the input).

answered Sep 13, 2012 at 5:37

MidnightLightning

7,0087 gold badges48 silver badges72 bronze badges

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

4 Comments

Alain Collins Over a year ago

Change the initialization of $sql to 'SELECT * from database where 1=1' then you don't have to worry about all the "AND"s. Just pile all the extra clauses with "AND" after that.

MidnightLightning Over a year ago

"all the AND's"? There's exactly one "AND" in my script. My method reduces the need to one, while yours would have four repetitions.

Alain Collins Over a year ago

If you like building up arrays and then concating them together, rather than writing legible SQL, have at it.

MidnightLightning Over a year ago

If you like writing long, rambling, inefficient SQL queries that put more load on the database server than they need, to return a simple result, have at it your way.

Ariel · Accepted Answer · 2012-09-13 05:33:15Z

0

Normally you do that in the front end language, not SQL.

But price LIKE '%' does, in fact, mean all (except for NULLs). So you are probably fine.

answered Sep 13, 2012 at 5:33

Ariel

26.8k5 gold badges62 silver badges71 bronze badges

Comments

LSerni · Accepted Answer · 2012-09-13 05:56:00Z

If you have your form fields organized, you can do something like:

<?php
    $fields = array(
        // Form    // SQL
        'brand' => 'brand',
        'type'  => 'type',
        'price' => 'price',
    );

    $sql  = 'SELECT * FROM `database`';
    $comb = ' WHERE ';
    foreach($fields as $form => $sqlfield)
    {
        if (!isset($_POST[$form]))
            continue;
        if (empty($_POST[$form]))
            continue;
        // You can complicate your $fields structure and e.g. use an array
        // with both sql field name and "acceptable regexp" to check input
        // ...

        // This uses the obsolete form for mysql_*
        $sql .= $comb . $sqlfield . ' LIKE "%'
             . mysql_real_escape_string($_POST[$form])
             . '"';
        /* To use PDO, you would do something like
             $sql .= $comb . $sqlfield . 'LIKE ?';
             $par[] = $_POST[$form];
        */
        $comb = ' AND ';
    }
    // Other SQL to go here
    $sql .= " ORDER BY brand;";

    /* In PDO, after preparing query, you would bind parameters
       - $par[0] is value for parameter 1 and so on.
       foreach($par as $n => $value)
           bindParam($n+1, '%'.$value.'%');
    */

Collectives™ on Stack Overflow

PHP SQL: Way to skip over section of a query if variable is blank

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related