How to loop over (JSON) array in Slim

1

Hi Guys,

I am posting an JSON array to Slim. And with this array I would like to query the database. The issue is that I would like to make it more dynamic.

For now there are two columns to filter upon: ‘activity’ and ‘country’. I use Eloquent btw.

When I recieve to code my controller. It looks like this:
$data = $request->getParams();
$query = $data[‘activity’][0]; // first record of array
$querytwo = $data[‘activity’][1]; // second record of array
$querythree = $data[‘country’][0]; // first record of array

Then I use:
$products = Product::where(‘active’, true)
->whereIn(‘activity’, [ $query, $querytwo ])
->whereIn(‘country’, [ $querythree ])
->get();

So as you can see not really dynamic. In a procedural way you could use something like:
$query .= ', ’ . $querytwo;
SELECT * FROM table WHERE $query AND $querytwo;

I have used all kinds of methods (string implode, for, foreachloops etc.) - none of it seems to work. What would be a good approach for looping trough the array and executing the DB-call?

Thanks!

2

How about something like:

$data = $request->getParams();

$queryBuilder = Product::where('active', true);

if (isset($data['activity'])) {
    $queryBuilder->whereIn('activity', $data['activity']);
}
if (isset($data['country'])) {
    $queryBuilder->whereIn('country', $data['country']);
}

$products = $queryBuilder->get();
3

Thanks llvdl,

I am getting there… :wink:

$data can contains multiple Array’s. I tried:

$queryBuilder = Product::where('active', true);
foreach ($activity as $item)
{
$queryBuilder->where(function($query) use ($item)
{
if(count($item) > 1
{
foreach ($item as $key)
{
$query->orWhere('activity', $key)
}
} else {
$query->orWhere('activity', $item)
}
});
}

result = (more or less):
select * from 'products' where 'active'= ? and ('activity' =?) and ('activity' = ? )

If i change the ‘where’ clausule in the first foreacht loop:
$queryBuilder->where(function($query) use ($item)

result = (more or less):
select * from 'products' where 'active'= ? or ('activity' =?) or ('activity' = ? )

I would like to make the result:
`select * from ‘products’ where ‘active’= true and (‘activity’ = ? or ? or ? or ? or ?) and (‘country’ = ? or ?)

Perhaps I have to use ->whereIn or something else?

P.s. how can I make the code formatting the same as your post?

4

Hello,

You can format the code by starting with a line ```php, then your code and then a line with ```.

For example:

```php
$a = 1;
print $a;
```

Becomes:

$a = 1
print $a;

Regarding the SQL query. I think what you are looking for is the whereIn function, so you get a query like

SELECT * from `products` WHERE `active` = true AND `activity` IN (?) AND `country` IN (?)

The list you give with whereIn can have an arbitrary size (although it can probably not be empty).

If you prefer a query with grouped or statements instead, like the following:

select * from 'products' where 'active'= true and ('activity' = ? or ? or ? or ? or ?) and ('country' = ? or ?)

you may want to try parameter grouping, to group the or conditions. Something like:

$queryBuilder = Product::where('active', true);
if (isset($data['activity'])) {
    $queryBuilder->where(function($query) use($data) {
        foreach($data['activity'] as $activity) {
            $query->orWhere('activity', '=', $activity);
        }
    });
}
$products = $queryBuilder->get();

Hope that helps,

Lennaert

5

Hi Lennaert,

Thanks for you help so far! The data array can contain multiple items. I tried to use a foreach loop for activities and one for country’s:

if(isset($data))
{
	$queryBuilder->where(function($query) use ($data)
	{
		foreach ($data['activity'] as $value)
		{
			$query->orWhere('activity', '=', $value);
		}
		foreach ($data['country'] as $value)
		{
			$query->orWhere('country', '=', $value);
		}
	});
}

OR

if(isset($data))
{
	$queryBuilder->where(function($query) use ($data)
	{
		foreach ($data['activity'] as $value)
		{
			$query->orWhere('activity', '=', $value);
		}		
	});
       
       $queryBuilder->where(function($query) use ($data)
	{
		foreach ($data['country'] as $value)
		{
			$query->orWhere('country', '=', $value);
		}		
	});
}

I does not matter where I put a second foreach-loop: I will get an ‘Invalid argument supplied for foreach()’.

I also tried (with the same error) the following (for a more dynamic use):

if(isset($data))
{
	$queryBuilder->where(function($query) use ($data)
	{
		foreach ($data as $key => $value)
		{
			foreach ($value as $item)
			{
				$query->orWhere($key, '=', $item);
			}
		}								
	});
}

How is it possible to make more then one ‘queryBuilders’, what am I missing here?

6

Check if $data[‘activity’] and $data[‘country’] are both arrays containing strings. If, for example, $data[‘country’] is a string instead of an array, PHP will issue a warning because it can’t iterate over a string using foreach.

The dynamic version you added will then work too. You probably want to place the first foreach outside the $queryBuilder->where() statement, or you end up with all conditions ORed. e.g.

$queryBuilder = Product::where('active', 1);
foreach ($data as $key => $value) {
    // make sure $value is an array!        
    $queryBuilder->where(function($query) use ($data, $key, $value) {
        foreach ($value as $item) {
            $query->orWhere($key, '=', $item);
        }
    });
}
$products = $queryBuilder->get();
1 Like
7

Works like a charm/train (don’t know the expression ;)), thanks!

1 Like