MASS WHERE/IN语句，如何处理空/返回的数据也匹配

我有一个问题，希望您能帮助我。

我正在使用WHERE/IN做一个大型SELECT语句，并为所有要使用的"搜索条件"传递一个$数组。

小例子. .(尽管最后$数组要大得多)

$harNumArray = (0100001943,0100001944,0100002392,0100007414,0100012110,0100015761,0100015835);
$harNumArray2 = implode(',', $harNumArray);
$results  = mysqli_query($mysqli, "SELECT har_id, guar_num FROM placements WHERE har_id IN ($harNumArray2)");
//**outputting the matches values/data from the search (select)**
while ($row = mysqli_fetch_assoc($results)) {
    //echo "HAR_ID: ". $row["har_id"]. "tGUAR_NUM: " . $row["guar_num"] . "rn<BR>";
    echo $row["har_id"]. "t" . $row["guar_num"] . "rn<BR>";
    fwrite($fh, $row["har_id"] . "t" . $row["guar_num"] . "rn");
}

这个工作得很好/很好(*感谢mstrong的提示)…"当"有匹配时……如果不匹配，则不返回任何内容

然而

. .例如，如果$harNumArray(索引0)中的第一个值在数据库中找不到…$row = mysqli_fetch_assoc($results loop…

只找到数字…以及从数据库中提取的与之匹配/相关的数据…

我希望它仍然是这样的:

0100001943^-//数据库中缺少/未找到编号(条目)，但仍然存在在第一位置使用原始搜索词)
0100001944 ^ 0100001944
0100002392 ^ 0100002392
0100007414 ^ 0100007414
0100012110 ^ 0100012110
0100015761 ^ 0100015761
0100015835 ^ 0100015835

或者像这样也可以(但上面的布局会更好):

-^-//数据库中缺少/未找到的数字(条目)
0100001944 ^ 0100001944
0100002392 ^ 0100002392
0100007414 ^ 0100007414
0100012110 ^ 0100012110
0100015761 ^ 0100015761
0100015835 ^ 0100015835

如果在WHERE/in SELECT中没有找到$harNumArray数组中的第一个数字…它不返回任何东西(这是有意义的).

然而

. .我"需要"解释不匹配的原因。并插入一个空白(或其他东西)…保持最终'list'(文本文件)的间距/顺序

我该怎么做呢?

谢谢!

编辑::

下面是代码的当前状态:(仍然没有返回匹配)

@Cal

//stack overflow approach:
    //format array data
    $harNumArray2 = "'" . implode("','", $harNumArray) . "'";    //single quotes
    //$harNumArray2 = implode(',', $harNumArray);  //no quotes
    //$harNumArray2 = '"' . implode('","', $harNumArray) . '"';  //double quotes
    //print_r("ARRAY CHECK: " . $harNumArray2);
    $results = mysqli_query($mysqli, "SELECT har_id, guar_num FROM placements WHERE har_id IN ($harNumArray2) ORDER BY har_id ASC") or die(mysql_error());
    //$results = mysqli_query($mysqli, "SELECT har_id, guar_num FROM placements WHERE har_id IN (" . $harNumArray2 . ") ORDER BY har_id ASC") or die(mysql_error());
    echo("<BR>");
    print_r("SELECT har_id, guar_num FROM placements WHERE har_id IN ($harNumArray2)");
    echo("<BR>");
    $rows = array();
    while($row = mysqli_fetch_assoc($results)) {
        $rows[$row['har_id']] = $row;
    }
    foreach ($harNumArray as $id){
        if (isset($rows[$id])){
            //... do something with $rows[$id]
            print_r($rows[$id] . "<BR>");
        }else{
            //... no match for $id
            print_r("....no match....");
            echo("<BR>");
        }
    }

仍然没有匹配返回…无论单引号，不引号或双引号…

难住了?

感谢更新2:

@Cal——

ok，我运行了你提供的代码…

这是我的输出:

Array ([har_id] => 000100007537 [guar_num] => 0000676798) Array ([har_id] => 000100007538 [guar_num] => 0000676798) Array ([har_id] => 000100007539 [guar_num] => 0000676798) Array ([har_id] => 000100007768 [guar_num] => 0000675266) Array ([har_id] => 000100007769 [guar_num] => 0000675266)

HAR_ID in DB = varchar(12)

GUAR_NUM in DB = varchar(12)

如果我在#results查询中留下OFF引号…我得到了数据的回报…(但无论如何……

执行查询:

$harNumArray = array(
    '0100001943',
    '0100001944',
    '0100002392',
    '0100007414',
    '0100012110',
);
$harNumArray2 = "'".implode("','", $harNumArray)."'";
$results = mysqli_query($mysqli, "SELECT har_id, guar_num FROM placements WHERE har_id IN ($harNumArray2)");

然后构建一个结果图:

$rows = array();
while ($row = mysqli_fetch_assoc($results)) {
    $rows[$row['har_id']] = $row;
}

然后你可以循环你的id检查匹配:

foreach ($harNumArray as $id){
    if (isset($rows[$id])){
        ... do something with $rows[$id]
    }else{
        ... no match for $id
    }
}

更新1:

听起来你的查询没有返回任何东西。运行以下代码:

$results = mysqli_query($mysqli, "SELECT har_id, guar_num FROM placements LIMIT 5");
while ($row = mysqli_fetch_assoc($results)) print_r($row);

然后用输出更新您的问题。听起来你的数据库并不完全像你想的那样。

相关内容

最新更新

热门标签：