Search by mutiple fields , SELECT IN (everything)

Question

I want to search some products and get results . This is my PHP code:

function Item_Search($AppID, $Keyword = '', $Wears)
{
    // expected $Wears = [1,0,1,0,1] , numbers can be diffrent from 0 to 1

    $WearNames = ['red', 'green', 'blue', 'yellow', 'black'];
    $FinalWear = [];

    foreach ($Wears as $i => $Wear) {
        if ($Wear == 1) {
            $FinalWear[] = $WearNames[$i];
        }
    }
    $FinalWear = json_encode($FinalWear);
    $FinalWear = str_replace(str_split('[]'), '', $FinalWear);

$ItemList = Query("SELECT * FROM items WHERE appid=$AppID 
AND name LIKE '%$Keyword%'
AND wear IN ($FinalWear)
");
}

This code works just fine, but there are some products without any color . In this case I want to say if all colors were 1 (true), then show products without color too.

So my question is, can I put something as $FinalWear in wear IN ($FinalWear) so I can get all the results?

When you say `without any color` does this mean that the column `wear` is `NULL` or an empty string `''`? — Nick, Nov 28 '19 at 21:31

score 1 · Accepted Answer · answered Nov 28 '19 at 21:42

You can take the sum of the elements in $Wear and if it is the same as the count, then all elements are set. You can then use this to add an OR condition to the wear test, checking for an empty string in wear when all elements in $Wear are set:

function Item_Search($AppID, $Keyword = '', $Wears)
{
    // expected $Wears = [1,0,1,0,1] , numbers can be different from 0 to 1

    $WearNames = ['red', 'green', 'blue', 'yellow', 'black'];
    $FinalWear = [];

    foreach ($Wears as $i => $Wear) {
        if ($Wear == 1) {
            $FinalWear[] = $WearNames[$i];
        }
    }
    $FinalWear = "'" . implode("','", $FinalWear) . "'";
    $EmptyWear = array_sum($Wears) == count($Wears) ? "OR wear = ''" : '';

    $ItemList = Query(<<<EOD
SELECT * 
FROM items 
WHERE appid=$AppID 
  AND name LIKE '%$Keyword%'
  AND (wear IN ($FinalWear) $EmptyWear)
EOD
);
}

Demo (of query output) on 3v4l.org

Note that if $AppID and $Keyword come from outside sources your query is vulnerable to SQL injection, and you should use a prepared statement to avoid that possibility. See this question.

If wear being empty means that it is actually NULL, change the line generating $EmptyWear to

 $EmptyWear = array_sum($Wears) == count($Wears) ? "OR wear IS NULL" : '';

this is exactly what I was looking for , thanks . and I have mysqli_escape_string in my code , but i wanted to make the question as small as possible . — Abolfazl Ghaemi, Nov 28 '19 at 21:49

Search by mutiple fields , SELECT IN (everything)

1 Answers1