Find First of Each Foreach Loop Pass

Question

I'm trying to build a keyword search function that is dynamic so it is getting the column names passed into it as an array generated from a query along with the keywords from the form post. It works well for Any Keywords and For Exact Phrase (each using a different set of programming not shown here) but the part that needs to fetch All Keywords is giving me some problems. Basically I need to know when it has completed the first pass through the fields for the first keyword so that it can add an AND to the WHERE clause before it continues to the next pass for however many keywords there are.

I tried below to make it switch over to AND by using the percentage of values but can'y quite work out the math so need some assistance. For this test, there are 9 columns and only two keywords but either could vary.

// Fetch the keywords
$keyword = (isset($_POST['s_keyword'])) ? $_POST['s_keyword'] : "";

// Subtract one because ID column is not being used
$len = ((count(array_keys($queryField))-1) * count($keywords));

// Count basic fields to check, subtract one since ID is not being used
$lenbase = (count(array_keys($queryField))-1);
$i = - (count($keywords)); // Subtract number of keywords
foreach ($keywords as $keyword) :
    $keyword = str_replace("'", "''", trim($keyword));
    foreach (array_keys($queryField) as $column) :
        $i++;
        if ($column === 'ID') continue;
        if (strlen($Where) == 0) $Where .= " WHERE (";
        if ($len %$lenbase != 0) $Where .= "(`$column` LIKE '%" . $keyword . "%') AND ";
        if ($i != $len) $Where .= "(`$column` LIKE '%" . $keyword . "%') OR ";
        if ($i == $len) $Where .= "(`$column` LIKE '%" . $keyword . "%') ";
    endforeach;
endforeach;

it is retrieving the keywords from a form post

$keyword = (isset($_POST['s_keyword'])) ? $_POST['s_keyword'] : "";
$keywords = explode(" ", trim($keyword));

Without the if ($len %$lenbase != 0) etc conditional, it is giving this which fetches any keyword but not all (a space was added for clarity):

WHERE ((`StorageArea` LIKE '%Paso%') 
OR (`Size` LIKE '%Paso%') 
OR (`Winery` LIKE '%Paso%') 
OR (`Brand` LIKE '%Paso%') 
OR (`Appellation` LIKE '%Paso%') 
OR (`ReleaseYear` LIKE '%Paso%') 
OR (`Varietal` LIKE '%Paso%') 
OR (`Status` LIKE '%Paso%') 
OR (`CountryName` LIKE '%Paso%') 

OR (`StorageArea` LIKE '%Robles%') 
OR (`Size` LIKE '%Robles%') 
OR (`Winery` LIKE '%Robles%') 
OR (`Brand` LIKE '%Robles%') 
OR (`Appellation` LIKE '%Robles%') 
OR (`ReleaseYear` LIKE '%Robles%') 
OR (`Varietal` LIKE '%Robles%') 
OR (`Status` LIKE '%Robles%')
OR (`CountryName` LIKE '%Robles%'))

but I am looking for this:

WHERE ((`StorageArea` LIKE '%Paso%') 
OR (`Size` LIKE '%Paso%') 
OR (`Winery` LIKE '%Paso%') 
OR (`Brand` LIKE '%Paso%') 
OR (`Appellation` LIKE '%Paso%') 
OR (`ReleaseYear` LIKE '%Paso%') 
OR (`Varietal` LIKE '%Paso%') 
OR (`Status` LIKE '%Paso%') 
OR (`CountryName` LIKE '%Paso%') 

AND (`StorageArea` LIKE '%Robles%') 
OR (`Size` LIKE '%Robles%') 
OR (`Winery` LIKE '%Robles%') 
OR (`Brand` LIKE '%Robles%') 
OR (`Appellation` LIKE '%Robles%') 
OR (`ReleaseYear` LIKE '%Robles%') 
OR (`Varietal` LIKE '%Robles%') 
OR (`Status` LIKE '%Robles%') 
OR (`CountryName` LIKE '%Robles%'))

The input $queryField is an array that is generated dynamically from a query and after stripping it down to the keys, in this case it looks like this:

Array
(
    [0] => ID
    [1] => StorageArea
    [2] => Size
    [3] => Winery
    [4] => Brand
    [5] => Appellation
    [6] => ReleaseYear
    [7] => Varietal
    [8] => Status
    [9] => CountryName
)

Answer 1

You can simply make use of array_map and implode:

$keywords = ['Paso', 'Robles'];
$columns = ['ID', 'StorageArea', 'Size', 'Winery', 'Brand', 'Appellation', 'ReleaseYear', 'Varietal', 'Status', 'CountryName'];

$where = 'WHERE ' . implode(' AND ', array_map(function ($keyword) use ($columns) {
  $keyword = str_replace("'", "''", $keyword);

  return '(' . implode(' OR ', array_map(function ($column) use ($keyword) {
    return "`$column` LIKE '%$keyword%'";
  }, array_filter($columns, function ($column) {
    return $column !== 'ID';
  }))) . ')';
}, $keywords));

Basically:

• the inner array_map returns an array with all subconditions for a given keyword, after using array_filter to filter out the ID column,
• the inner implode joins them with OR,
• the outer array_map returns an array of all these joined ORs (one per keyword), after surrounding them with parehtneses,
• finally the outer implode joins those with AND, creating the final clause.

So, in other words, we go from:

1. [ <column1> LIKE '%<keyword1>%', <column2> LIKE '%<keyword1>%, ... ] (inner array_map's array result)
2. <column1> LIKE '%<keyword1>% OR <column2> LIKE '%<keyword1>% OR ... (inner join's string result)
3. [ (<column1> LIKE '%<keyword1>% OR <column2> LIKE '%<keyword1>% OR...), (<column1> LIKE '%<keyword2>% OR <column2> LIKE '%<keyword2>% OR...), ... ] (outer array_map's array result)
4. (<column1> LIKE '%<keyword1>% OR <column2> LIKE '%<keyword1>% OR...) AND (<column1> LIKE '%<keyword2>% OR <column2> LIKE '%<keyword2>% OR...) AND ... (outer join's string result).

Demo: https://3v4l.org/GqEXQ (with added \n chars to produce a clearer output)

Answer 2

I believe given the limited information as to the data structure that you should be putting your and part of the code immediately before the outer foreach closes. you will need to check that the loop has not finished because if it has you will need to omit the and and close the sql statement (I presume that's what it is).

foreach($arrayvalue as $array):
    foreach($array as $arr):
      // where where where
    endforeach;

    // and or close it here.
    if ($i !== $len) {
       // apply and
    } else {
       // apply closing data
    }

endforeach;