How can I use multiple WHERE statements when using IN with array

Question

I have the following code:

function av_connections_search($string){
    global $wpdb;
    // remove url parameter from string
    $string = trim($string,'s=');
    $search = explode('%20',$string);
    // query the wp_connections database table according to search entry
    $sql = "
        SELECT *
        FROM {$wpdb->prefix}connections
        WHERE contact_first_name IN (".implode(', ', array_fill(0, count($search), '%s')).")
      ";
    // prepare() to prevent sql injection
    $query = call_user_func_array(array($wpdb, 'prepare'), array_merge(array($sql), $search));
    // get query results
    //var_dump($query);
    $results = $wpdb->get_results($query);
    // return if no results
    if(empty($results)){
        return false;
    }
    // flush $wpdb cache
    $wpdb->flush();
    // return data to search.php
    return $results;
}

where $string looks like ?s=search+these+terms when passed to the function

My question, how can I use multiple WHERE statements? I've tried simply:

WHERE contact_first_name IN (".implode(', ', array_fill(0, count($search), '%s')).")
OR contact_last_name IN (".implode(', ', array_fill(0, count($search), '%s')).")

but it fails completely. When I do:

WHERE contact_first_name OR contact_last_name IN (".implode(', ', array_fill(0, count($search), '%s')).")

it only returns contact_last_name. What am I missing?

EDIT: Ok, so I'm quite sure the problem lies within this:

$query = call_user_func_array(array($wpdb, 'prepare'), array_merge(array($sql), $search));

but, for a lack of sleep I cannot wrap my mind around why it's not merging the array with both WHERE clauses.

EDIT 2 Should this not work? It works great if I use the single WHERE clause, but the moment I use OR and another clause, it returns nothing, which makes no sense because this is the query:

SELECT * FROM wp_connections WHEREcontact_first_nameIN (%s, %s) ORcontact_last_nameIN (%s, %s) " [1]=> string(4) "Mina" [2]=> string(5) "Morse"

EDIT 3 I do not believe prepare() is the problem. Consider this (doesn't work):

global $wpdb;
$string = trim($string,'s=');
$search = explode('%20',$string);
$how_many = count($search);
$placeholders = array_fill(0, $how_many, '%s');

$format = implode(', ', $placeholders);

$query = "SELECT * FROM wp_connections WHERE contact_first_name IN($format) OR contact_last_name IN($format)";

$results = $wpdb->query($query, $search);
return $results;

Even removing prepare completely, same result. What am I missing? If I remove the OR condition and just check one value, it works fine, but OR kills the query.

EDIT 4 It turns out this was the solution:

$results = $wpdb->query($query, $search, $search);

I was missing the second $search variable... *scratching head still

just echo `implode(', ', array_fill(0, count($search), '%s'))` are u getting the correct words...???? — Sherin Jose, Sep 26 '13 at 10:32
Try this link http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_in — Sherin Jose, Sep 26 '13 at 10:38
With this `$query = call_user_func_array(array($wpdb, 'prepare'), array_merge(array($sql), $search));` which returns `SELECT * FROM wp_connections WHERE contact_first_name IN ('mina', 'john', 'paul')` — Tim Hallman, Sep 26 '13 at 10:40
Try putting that query in the mysql query builder and execute... — Sherin Jose, Sep 26 '13 at 10:41
Prepare is not expected to work with "IN" clauses, see this http://stackoverflow.com/questions/920353/php-pdo-can-i-bind-an-array-to-an-in-condition and this http://stackoverflow.com/questions/920353/php-pdo-can-i-bind-an-array-to-an-in-condition — George Lund, Sep 26 '13 at 16:43
So, why does it work with one condition then? I don't see where prepare() isn't meant to be used with IN. Also, both those links are the same. — Tim Hallman, Sep 26 '13 at 16:52
I've found a solution... still trying to wrap my head around the problem though. I'll post it shortly. — Tim Hallman, Sep 26 '13 at 19:44
For the record, I ended up ditching this method for a FULLTEXT search. — Tim Hallman, Sep 27 '13 at 15:48

Gurminder Singh · Accepted Answer · 2013-09-27T05:17:26.540

0

Try this

WHERE contact_first_name IN (".implode(', ', array_fill(0, count($search), '%s')).") OR contact_last_name IN (".implode(', ', array_fill(0, count($search), '%s')).")

UPDATE

The correct answer to this question is The query is correct, it's the dynamic parameters that are wrong which cause the query syntax to become wrong. It was actually in the comments section right under this answer, so merged it with the accepted answers!

edited Sep 27 '13 at 05:17

answered Sep 26 '13 at 10:27

Gurminder Singh

1,755
16
19

1

The query is correct, may be it's the dynamic parameters that are wrong which causes the query syntax to become wrong. – Gurminder Singh Sep 26 '13 at 10:40
Idk, I'm stumped. `var_dump(array_merge(array($sql), $search));` gives: `array(4) { [0]=> string(150) " SELECT * FROM wp_connections WHERE contact_first_name IN (%s, %s, %s) OR contact_last_name IN (%s, %s, %s) " [1]=> string(4) "mina" [2]=> string(4) "john" [3]=> string(5) "morse" }` – Tim Hallman Sep 26 '13 at 11:12
Can you print the _$sql_ and see what the actual query is that is being fired? – Gurminder Singh Sep 26 '13 at 11:20
That's it right above. The %s are replaced with "mina", "john", etc. – Tim Hallman Sep 26 '13 at 11:31
I'm accepting your answer @Gurminder Singh because your comment above is actually correct. It was a parameter issue after all. – Tim Hallman Sep 26 '13 at 22:30

How can I use multiple WHERE statements when using IN with array

1 Answers1

Linked