PHP generate sql where clause from array

Question

I would like to create dinamically in my php class a where clause from a array where are defined search fields.

$search = array('brand' => 'something', 'model' => 'something');
$myclass->testarr($search);

CLASS

public function testarr($search){

    if (!empty($search)){

        foreach ($search as $key => $value) {

            $where = $key . " = " . $value;
        }

    $clause = !empty($where) ? 'WHERE' : '';

    $result = $this->db->mysqli->query
    ("SELECT * FROM tb1 $clause $where");

    }

}

My problem is to manage a clause with more than one field by entering the suffix AND. How could I do that? Thanks

mysqli_stmt_bind_param is a better option

Arun Killu
– Arun Killu

2013-12-06 11:10:19 +00:00
Commented Dec 6, 2013 at 11:10 — Arun Killu
– Arun Killu, Commented Dec 6, 2013 at 11:10

kero · Accepted Answer · 2013-12-06 11:29:52Z

5

I would advice to do this:

$where = array();
if (!empty($search) && is_array($search)) {
    foreach ($search as $key => $value) {
        $where[] = $key . " = " . $value;
    }
}
if (!empty($where))
    $query = sprintf('SELECT * FROM tb1 WHERE %s', implode('AND ', $where));
else
    $query = 'SELECT * FROM tb1';

Using implode makes things easier.

Beware however of escaping issues, as your code is prone to security issues.

edited Dec 6, 2013 at 11:29

kero

10.7k5 gold badges44 silver badges52 bronze badges

answered Dec 6, 2013 at 11:14

Michaël Perrin

6,3185 gold badges44 silver badges69 bronze badges

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

2 Comments

Paolo Rossi Over a year ago

Clean, but $where may be empty, and "WHERE" should be removed

mickmackusa Over a year ago

No one should use this insecure/unstable answer. A properly implemented prepared statement is called for.

kero · Accepted Answer · 2013-12-06 11:10:37Z

1

There is one flaw with your code: $where = $key . " = " . $value; will overwrite $where in each iteration, you need to use .= to concatenate. Then this could be done e.g. the following way

$where = "";
foreach ($search as $key=>$value) {
    if (!empty($where)) $where .= " AND ";
    $where .= $key . " = " . $value;
}
$clause = !empty($where) ? 'WHERE '.$where : '';

This will add a AND before every condition, starting from the second (because for the first the if will fail).

I suggest researching prepared statements, these will make your code alot more secure and once you understood the concept, they become quite easy to handle (imo). Because if that is most of your code at the moment, you are quite vulnerable to SQL injections.

answered Dec 6, 2013 at 11:10

kero

10.7k5 gold badges44 silver badges52 bronze badges

3 Comments

Paolo Rossi Over a year ago

I thank you for your solution. Can you tell me how to do it with prepared statements? Thanks

kero Over a year ago

Well okay, this code wouldn't be much simpler, as you'd have to iterate twice over $search (once for keys and once for values) - I think Michaël Perrin is quite elegant

mickmackusa Over a year ago

No one should use the insecure/unstable snippet. A properly implemented prepared statement is called for.

Collectives™ on Stack Overflow

PHP generate sql where clause from array

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related