PHP dynamically generate a mysql where clause in php

Question

To generate dynamically a mysql where clause using php I have done it this way

$_GET['cat_2'] = '11';

$catid_1 = isset($_GET['cat_1']) && $_GET['cat_1'] <> '' ? $_GET['cat_1'] : '%';
$catid_2 = isset($_GET['cat_2']) && $_GET['cat_2'] <> '' ? $_GET['cat_2'] : '%';
$catid_3 = isset($_GET['cat_3']) && $_GET['cat_3'] <> '' ? $_GET['cat_3'] : '%';
$catid_4 = isset($_GET['cat_4']) && $_GET['cat_4'] <> '' ? $_GET['cat_4'] : '%';

// array for where clause
$search['catid_1'] = $catid_1;
$search['catid_2'] = $catid_2;
$search['catid_3'] = $catid_3;
$search['catid_4'] = $catid_4;

$where = array();
if (!empty($search) && is_array($search)) {
foreach ($search as $key => $value) {

        $operator = $value == '%' ? ' LIKE ' : ' = ';
        $where[] = $key . $operator . "'" . $value . "'";
        }
    }

if (!empty($where)) {
$whr = sprintf('WHERE %s', implode(' AND ', $where));
}

this code produces this

WHERE catid_1 LIKE '%' AND catid_2 = '11' AND catid_3 LIKE '%' AND catid_4 LIKE '%'

Now i'd like to try to add a different "AND" clause for catid_2 and catid_3 if those are <> % but unfortunately I can not do it

I would like to get this

WHERE catid_1 LIKE '%' AND (catid_2 = '11' OR type = '0') AND catid_3 LIKE '%' AND catid_4 LIKE '%'

Is a possible thing to do? how can I modify my code to do it? Thank you

Ian Kenney · Accepted Answer · 2014-02-06 11:45:01Z

1

First off, there seems to be no point in adding a condition for where catid_* like '%', may as well just leave it off and only add if you have a value.

So you could just add the where clause you want for each param if it is present, something like the following (untested) sample

$where = array();
$cat1 = $_GET['cat_1']; // should sanitize these to prevent sql injection
$cat2 = $_GET['cat_2']; // 

if( isset($cat_1) && $cat_1 <> '' ) { 
   $where[] = "catid_1 = '" . $cat_1 . "'";
}
if( isset($cat_2) && $cat_2 <> '' ) {  
  $where[] = "(catid_2 = '" . $cat_2 . "' OR type = '0')"
}

if (!empty($where)) {
  $whr = sprintf('WHERE %s', implode(' AND ', $where));
}

answered Feb 6, 2014 at 11:45

Ian Kenney

6,4561 gold badge27 silver badges45 bronze badges

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

2 Comments

Paolo Rossi Over a year ago

thank you but I do not understand how I can apply your solution to my code in foreach function...

Ian Kenney Over a year ago

it was an alternative to using your foreach function - it simplifies things a little I think.

Collectives™ on Stack Overflow

PHP dynamically generate a mysql where clause in php

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related