CODEWITHSUNDEEP
phpmysqlsql
Abolfazl Ghaemi
Abolfazl Ghaemi

Reputation: 454

Search by mutiple fields , SELECT IN (everything)

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?

Upvotes: 1

Views: 47

Answers (1)

Nick
Nick

Reputation: 147146

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" : '';

Upvotes: 1

Related Questions