2

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')"
7
  • The code you are obtaining should be SQL? It has several issues. Can you include an example of the request you get? i.e. "{ "filters": { "key1": [ { "code": "ty_letter_no", "tag": "AU" } ], "key2": [...] }, "conditions": { "key1": { "condition": "or" } }", and what should it output? Commented Dec 28, 2020 at 23:44
  • 1
    I've added an example of a request. Commented Dec 28, 2020 at 23:58
  • I cannot quite understand why you have a groupCondition with "OR", and also a "condition" inside the filter. Frankly, while your requirement is straightforward (I have coded several such filters myself), the structure of this request object is not. Is the final code to be valid SQL, or what else? In your example request, both OR seem to be lost. Is this the expected result? Commented Dec 29, 2020 at 0:07
  • 1
    📎: "It looks like you're writing your own ORM. Have you considered using one that's already written, tested, and widely supported like Atlas, Doctrine, or Eloquent?" Commented Dec 29, 2020 at 1:54
  • 1
    WARNING: When using mysqli you should be using parameterized queries and bind_param to add any data to your query. DO NOT use string interpolation or concatenation to accomplish this because you have created a severe SQL injection bug. NEVER put $_POST, $_GET or data of any kind directly into a query, it can be very harmful if someone seeks to exploit your mistake. Commented Dec 29, 2020 at 1:54

3 Answers 3

2

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.
}
Sign up to request clarification or add additional context in comments.

Comments

2

Just an idea: you can always understand if the current iteration is the first (by using a flag).

In this way, you can:

  • skip the first iteration
  • save at each iteration the current item, to be used at the next iteration
  • Update the query at the Nth iteration appending information related to the (N-1)th iteration
  • Outside the loop append the information related to the last iteration applying the modifications you need

Something like

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

if(isset($previousItem))
{
    $type = $previousItem['code'];
    $tag = $previousItem['tag'];
    $conditions .= 'WHERE ' . $type . ' = '. "'". $tag . "'"
}

You could try adapting this solution to your needs.

Comments

0

To strictly answer your question, can you see if you're working on the last iteration by checking the current iteration (in your case, the key) against the length (count()) of the array. You can avoid doing something on that iteration by making sure that if( $iteration < $length - 1 ), then you're not in the last iteration. If you want to check that you are in the last, if( $iteration == $length - 1 ).

To address the larger scope of the question:

When programmatically building queries, I've often found it easier to make use of the WHERE 1=1 to start, since it allows you to implode() an array using AND as a glue (and some DBMS will not even parse the 1=1).

$filters = "";
$length  = count($request->filters);

foreach($request->filters as $key => $filter) {
    $conditions = array( "WHERE 1=1" );
    
    foreach($filter as $item){
        $tag  = $item['tag'];
        $type = $item['code'];
        $condition = $item['condition'];
        
        $conditions[] = "{$type} = '{$tag}'";
    }
    
    // Build the conditions
    $filters .= sprintf( '(%s)', implode(' AND ', $conditions ) );
    
    // If this isn't the last item, add the group conditions
    $filters .= ( $key < $length-1 ) ? " {$request->conditions[$key]['condition']} " : '';
}

The last line checks to make sure it's not the last item (by making sure the current index is less than the $length-1 before appending that last condition. Using the above, you'd end up with a filter like:

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

If the service you're sending that to doesn't like the 1=1 (it shouldn't have any effect at all, but if it does) you can do $filters = str_replace( '1=1 AND ', '', $filters ); at the end.

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

Also note that this doesn't output strictly valid SQL, just the format you requested (I'm assuming the service wants the filters in this format for their own reasons)

Here's a quick example

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.