将 IN 与数组一起使用时如何使用多个 WHERE 语句 [英] How can I use multiple WHERE statements when using IN with array

问题描述

我有以下代码:

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($w​​pdb, '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"

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

推荐答案

试试这个

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

更新

这个问题的正确答案是查询是正确的，是动态参数错误导致查询语法出错.实际上是在这个答案下面的评论部分，所以将它与接受的答案合并！

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!

这篇关于将 IN 与数组一起使用时如何使用多个 WHERE 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！