PHP 'Where' query conditional on 'If' 'isset'

Question

I am new to PHP so any help on the following would be really appreciated:

I am trying to run a query with a WHERE condition, but I only want to apply the where condition if the variable has been set (via a radio button).

I basically want to be able to say - if a variable is not set, do not include it in the where condition. The complication is that I will be doing this with 3+ variables...

At its most basic - here is the code I have so far:

if(isset($_POST['category'])){ 
$category = $_POST['category'];}

if(isset($_POST['brand'])) {
$brand = $_POST['brand'];
}

{
$q = 'SELECT name, category, brand, price, imageurl, purchaselink FROM clothes WHERE category = "'.$_POST['category'].'" AND brand = "'.$_POST['brand'].'"';

[truncated]

Thanks very much in advance!

Build the where clause as a second string variable.

developerwjk
– developerwjk

2014-06-06 22:23:56 +00:00
Commented Jun 6, 2014 at 22:23 — developerwjk
– developerwjk, Commented Jun 6, 2014 at 22:23

Community · Accepted Answer · 2023-11-17 19:59:20Z

1

You can do this with an array, but first of all DO NOT BUILD QUERIES THIS WAY since you'll be vulnerable to SQL injection. Use PDO instead.

The idea is to have a list of suitable conditions:

$conds = [ 'brand', 'category', 'name' ];

$where = [ ]; // Empty array

foreach ($conds as $cond) {
    if (!empty($_POST[$cond])) {

        $sql = "({$cond} = ?)";    // We use PDO and bound values.

        $where[$sql] = $_POST[$cond];

        // In *deprecated* MySQL we would use at least
        // $sql = "({$cond} = '" . mysql_real_escape_string($_POST[$cond]) . "')";
        // $where[$sql] = true;
    }
}
// Now we have a list of pairs:
//     brand = ?     =>  'MyBrand',
//     name  = ?     =>  'MyName',

if (!empty($where)) {
    $sql_string .= ' WHERE (';
    $sql_string .= implode( ' AND ', array_keys($where) );
    $sql_string .= ')';
}

// $sql_string is now SELECT ... WHERE ( (brand=?) AND (name=?) ... )
// Using the MySQL version, we would have ... WHERE ( (brand='MyBrand') AND ... ) )

// With PDO we PREPARE the query using sql_string

// http://dev.mysql.com/doc/apis-php/en/apis-php-pdo-mysql.html
// http://www.php.net/manual/en/intro.pdo.php

// We need an open PDO connection saved into $pdo

$stmt = $pdo->prepare ($sql_string);

// Then we execute the query.
// Bind the values to array_values($where).
$stmt->execute( array_values($where) );

while ($tuple = $stmt->fetch(PDO::FETCH_ASSOC)) {
    ...
}

A shorter way, MySQL only (since it does not distinguish between keys and values) would be

$where = [ ]; // empty array()
foreach ($conds as $cond) {
    if (empty($_POST[$cond])) {
        continue;
    }
    // THIS IS NOT SECURE. See e.g. http://johnroach.info/2011/02/17/why-mysql_real_escape_string-isnt-enough-to-stop-sql-injection-attacks/
    $escaped = mysql_real_escape_string($_POST[$cond]);
    $where[] = "({$cond} = '{$escaped}')";
}
$query = "SELECT ...";
if (!empty($where)) {
    $query .= " WHERE (" . implode(' AND ', $where) . ")";
}

This approach has the additional advantage that you can have the 'AND' parameterized - the user can choose whether have the conditions ANDed or ORed via a radiobutton:

    $and_or = ('OR' == $_POST['andor']) ? ' OR ' : ' AND ';
    $query .= " WHERE (" . implode($and_or, $where) . ")";

Note that the actual value of 'andor' is NOT used -- if it is an OR, all well and good, ' OR ' is used. Anything else that might be accidentally sent in a POST by a customer, such as "--; DROP TABLE Students;" , is considered to mean ' AND '.

edited Nov 17, 2023 at 19:59

CommunityBot

11 silver badge

answered Jun 6, 2014 at 22:31

LSerni

57.7k10 gold badges70 silver badges110 bronze badges

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

3 Comments

Mimouni Over a year ago

I think Mr @Iserni your solution is global and good (this is a good job), but here I think our friend is new in php, he try some thing simple not a strongest security and global with 20 lines of code. but if we want to do things with greated methods I suggeste to use an ORM like Propel or Doctrine. :)

Gibs Over a year ago

Thank you Iserni - I definitely want to use PDO but am trying to execute using: $sql_string->execute(); echo --> then my html code. I am clearly doing something wrong in the execute though - I don't know what thought as I understand that I don't need to bind the variables as this has happened already!

LSerni Over a year ago

You can't straight execute $sql_string, you need to prepare it as a statement. I've added a couple of links, and the code to use the $pdo you create following the manual (second link)

manout · Accepted Answer · 2014-06-06 22:23:52Z

0

you have to incluide all inside if(){}

 if(isset($_POST['category']) && isset($_POST['brand'])){ 
 $q = 'SELECT name, category, brand, price, imageurl, purchaselink FROM clothes WHERE   category = "'.$_POST['category'].'" AND brand = "'.$_POST['brand'].'"';

}

answered Jun 6, 2014 at 22:23

manout

412 silver badges10 bronze badges

Comments

Mimouni · Accepted Answer · 2014-06-06 22:26:31Z

0

I use this technique to make my filters :

$q = 'SELECT name, category, brand, price, imageurl, purchaselink FROM clothes WHERE 1=1 ';


if(isset($_POST['category'])) $q .= ' AND category ="'.$_POST['category'].'"';

if(isset($_POST['brand'])) $q .= ' AND brand = "'.$_POST['brand'].'"';
// here you can add other filters. :) be happy

answered Jun 6, 2014 at 22:26

Mimouni

3,6483 gold badges32 silver badges37 bronze badges

1 Comment

LSerni Over a year ago

This is risky if someone sends a value containing quotes (let alone malicious input). At the very least, if not PDO, use escaping.

Collectives™ on Stack Overflow

PHP 'Where' query conditional on 'If' 'isset'

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related