将 IN 与数组一起使用时如何使用多个 WHERE 语句

本文介绍了将 IN 与数组一起使用时如何使用多个 WHERE 语句的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有以下代码:

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;
}

其中 $string 在传递给函数时看起来像 ?s=search+these+terms

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

我的问题，如何使用多个 WHERE 语句?我简单地尝试过:

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')).")

它只返回contact_last_name.我错过了什么?

it only returns contact_last_name. What am I missing?

好的，所以我很确定问题出在这里:

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

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

但是，由于睡眠不足，我无法理解为什么它没有将数组与两个 WHERE 子句合并.

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 这应该不起作用吗?如果我使用单个 WHERE 子句，效果很好，但是当我使用 OR 和另一个子句时，它什么都不返回，这毫无意义，因为这是查询:

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 我不认为 prepare() 是问题所在.考虑一下(不起作用):

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;

即使完全去掉prepare，结果还是一样.我错过了什么?如果我删除 OR 条件并只检查一个值，它工作正常，但 OR 会终止查询.

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 原来这是解决方案:

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

我错过了第二个 $search 变量... *还在抓挠头

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

the

将 IN 与数组一起使用时如何使用多个 WHERE 语句

问题描述

推荐答案