current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free.
up vote 0 down vote favorite

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

share|improve this question
1  
mysqli_stmt_bind_param is a better option –  Arun Killu Dec 6 '13 at 11:10

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
Clean, but $where may be empty, and "WHERE" should be removed –  Paolo Rossi Dec 6 '13 at 11:26
    
@PaoloRossi Now it should work –  kingkero Dec 6 '13 at 11:35
    
Thanks you a lot! –  Paolo Rossi Dec 6 '13 at 12:36
up vote 1 down vote

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.

share|improve this answer
    
I thank you for your solution. Can you tell me how to do it with prepared statements? Thanks –  Paolo Rossi Dec 6 '13 at 11:16
    
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 –  kingkero Dec 6 '13 at 11:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.