PHP: Find the last item in a nested foreach loop?

I'm working with an API to retrieve records based on user input.

There are filters and filter groups that will be concatenated, however, I do not want the last AND or OR in the nested for each loop to be concatenated to the string.

I would like to have an if statement that looks for the last item in the nested foreach loop and concatenates a different string to the end, like this:

if($i == $lastItem) {
    $conditions .= 'WHERE ' . $type . ' = '. "'". $tag . "'";
} 
else {
    $conditions .= 'WHERE ' . $type . ' = '. "'". $tag . "'" . ' ' . $condition . " ";
}

What is the best practice for finding the last item of a nested for each loop using PHP?

Here is the reference code:

$conditions = "";
$filters = "";
foreach($request->filters as $key=>$filter) {
            foreach($filter as $item) {
                if($item['tag']) {
                    $type = $item['code'];
                    $tag = $item['tag'];
                    $condition = $item['condition'];
                    $conditions .= 'WHERE ' . $type . ' = '. "'". $tag . "'" . ' ' . $condition . " ";
                }
            }
          $groupCondition = $request->conditions[$key]['condition'];
          $filters .= '('.$conditions.') ' . $groupCondition . " ";
        }

Here is an example of a request, the two objects are combined in the foreach loop:

filter: {
  0 {
    0 {
     code: 'gl_code',
     condition: 'OR',
     tag: '10-140-4700-0401'
    }
  1 {
    0 {
     code: 'ty_letter_no',
     condition: 'AND',
     tag: 'AM123'
    },
    1 {
     code: 'gl_code',
     condition: 'OR',
     tag: '10-140-4700-0401'
    }   
}

groupConditions: {
   0 {
     condition: 'OR'
     }
   1 {
     condition: 'AND'
     }
}

Here an example of the current output:

"(WHERE ty_letter_no = 'AM123' AND )
 OR 
(WHERE ty_letter_no = 'AM123' AND WHERE solicit_code = '19-10NL' AND WHERE ty_letter_no = 'AU' AND ) 
AND 
(WHERE ty_letter_no = 'AM123' AND WHERE solicit_code = '19-10NL' AND WHERE ty_letter_no = 'AU' AND WHERE solicit_code = '19-04HRGOLF' AND ) 
AND "

I would like it to output:

"(WHERE ty_letter_no = 'AM123') 
OR 
(WHERE ty_letter_no = 'AM123' AND WHERE solicit_code = '19-10NL' AND WHERE ty_letter_no = 'AU')
AND 
(WHERE ty_letter_no = 'AM123' AND WHERE solicit_code = '19-10NL' AND WHERE ty_letter_no = 'AU' AND WHERE solicit_code = '19-04HRGOLF')"

Solution

I shall work under these assumptions:

the request comes from a "LEGO model" builder, so that the condition entry is always present, but sometimes not significant (specifically, when it is the last of its group).
the resulting code has to be a valid SQL condition.
the string values do not need escaping and have been proofed against SQL injection.

If this is so, then you can use a limited state machine to achieve your result:

$completeCondition = '';
$groupjoin         = '';
foreach ($request->filter as $index => $conditions) {
   $conditionjoin    = '';
   $partialCondition = '';
   foreach ($conditions as $triplet) {
       $partialCondition .= "{$conditionjoin}{$triplet->code} = '{$triplet->tag}'";
       $conditionjoin = " {$triplet->condition} ";
   }
   $completeCondition .= "{$groupjoin}({$partialCondition})";
   $groupjoin = " {$request->groupConditions[$index]->condition} ";
}
if (!empty($completeCondition)) {
    $completeCondition = " WHERE {$completeCondition}";
}

Using this version of your request,

$request = json_decode('{
"filter": [
    [
       { "code": "gl_code", "condition": "OR", "tag": "10-140-4700-0401" }
    ],
    [
       { "code": "ty_letter_no", "condition": "AND", "tag": "AM123" },
       { "code": "gl_code", "condition": "OR", "tag": "10-140-4700-0401" }
    ]
],
"groupConditions": [ { "condition": "OR" }, { "condition": "AND" } ]
}');

the result is the following, valid SQL:

WHERE (gl_code = '10-140-4700-0401') 
   OR (ty_letter_no = 'AM123' AND gl_code = '10-140-4700-0401')

(if the destination language is not SQL then the code can be changed slightly, of course).

a more refined result (PDO support)

Normally you do not want to include the request strings in your SQL code as is, because this allows the user to arbitrarily alter the SQL code you will execute. For example if I were to send along a tag of

'||SLEEP(60)||'

the code above would happily encode it as, say, gl_code = ''||SLEEP(60)||'', which is a valid SQL request and will be executed, halting your thread for sixty seconds. If I know you're using MySQL, I can perform some tricks with the LOCK() function and try to exhaust the internal metadata memory. If you're really unlucky and composite queries have not been disabled (they usually are!), then I very much fear I can own your SQL server. Even if they are, there are several dirty tricks that can be done with LEFT JOINs, UNIONs and SELECT 'code' INTO DUMPFILE '/var/www/nasty.php', and not all installations are hardened against all of them.

To avoid this we use PDO and SQL parameterization. This requires sending the query in two parts, a prepared query like

`...AND gl_code = :value1`

and a binding list containing ...':value1' => 'AL-1234-56'....

$completeCondition = '';
$groupjoin         = '';
$bindingList       = array();

foreach ($request->filter as $index => $conditions) {
   $conditionjoin    = '';
   $partialCondition = '';
   foreach ($conditions as $triplet) {
       $bind = ':b' . count($bindingList);
       $partialCondition .= "{$conditionjoin}{$triplet->code} = {$bind}";
       $conditionjoin = " {$triplet->condition} ";
       $bindingList[$bind] = $triplet->tag;
   }
   $completeCondition .= "{$groupjoin}({$partialCondition})";
   $groupjoin = " {$request->groupConditions[$index]->condition} ";
}
if (!empty($completeCondition)) {
    $completeCondition = " WHERE {$completeCondition}";
}

// Now we could safely do (supposing $pdo is my PDO DB object)
$stmt = $pdo->prepare($completeCondition);
$stmt->execute($bindingList);
while ($row = $stmt->fetch()) {
    // Do something with the row.
}