MASS WHERE/IN statement, how to handle empty/retur

问题:

I have a question I hope you can help me with..

I am doing a large SELECT statement using WHERE/IN and passing in an $array for all the 'search terms' to be used.

quick example.. (although in the end the $array is much larger)

$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"] . "\r\n<BR>";
    echo $row["har_id"]. "\t" . $row["guar_num"] . "\r\n<BR>";
    fwrite($fh, $row["har_id"] . "\t" . $row["guar_num"] . "\r\n");
} 

and this works fine/great (*thanks msturdy for tip)... 'when' there IS a match.. if there is not a match..nothing is returned.

However.. if for example the first value in the $harNumArray (index 0) was NOT found in the database... nothing is returned in the $row = mysqli_fetch_assoc($results loop...

ONLY found numbers.. and their matching/associated data that was pulled form the DB with it..

I’d like it to be like this still:

• 0100001943^- //missing/hot found number (entry) in the DB but still using original search term in first position)
• 0100001944^0123467894
• 0100002392^0011122234
• 0100007414^0002130567
• 0100012110^0000045432
• 0100015761^0001000045
• 0100015835^0998775233

Or like this would be acceptable (but above layout would be better):

• -^- //missing/hot found number (entry) in the DB
• 0100001944^0123467894
• 0100002392^0011122234
• 0100007414^0002130567
• 0100012110^0000045432
• 0100015761^0001000045
• 0100015835^0998775233

If the first number in the $harNumArray array is not found in the WHERE/IN SELECT.. it doesnt return anything (which makes sense)..

however.. I 'need' to account for the non-matches.. and insert a blank (or something).. to keep the spacing/order of the final 'list' (text file)

How can I go about doing this?

thanks!

EDIT::

here is current state of code: (still returns no matches)

@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>");
        }
    }

still no match is returned... no matter single quotes, no quotes or double quotes..

stumped?

thanks

UPDATE II:

@Cal-

ok I ran the code you supplied..

this was my output:

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)

if I leave OFF the quotes in the #results query.. I get a return on data.. (but no matter what.. no matches found)

回答1:

Perform your query:

$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)");

Then build a map of your results:

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

Then you can loop over your IDs checking for matches:

foreach ($harNumArray as $id){

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

UPDATE 1:

It sounds like your query is not returning anything. Run this code:

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

And then update your question with the output. It sounds like your database does not contain quite what you think.