+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Query Builder "Where" with Array (/showthread.php?tid=76495)
Query Builder "Where" with Array - IvanBell - 05-18-2020
Hello everyone! I just started learning CodeIgniter 4 and will be very thankful if you help with my problem. In my database I have a table with various products. The table has columns "brand" and "country". I would like to display all the products and add checkboxes for brands and countries so that user can filter the displayed products. Here's my form:
Code:
<form method="post" action="<?=base_url('page/view/'.$currentCatalog);?>">
<h2>Brands</h2>
<?php foreach ($filters as $item) : ?>
<input type="checkbox" name="brand[]" value="<?=$item['brand'];?>"><?=$item['brand'];?><br>
<?php endforeach ?>
<h2>Countries</h2>
<?php foreach ($filters as $item) : ?>
<input type="checkbox" name="country[]" value="<?=$item['country'];?>"><?=$item['country'];?><br>
<?php endforeach ?>
<input type="submit">
</form>In my controller I get the post data and send it to the model (I am not showing you all the code, this $catalog is not important):
Code:
$postFilters = $this->request->getPost();
$data = [
'catalog' => $model->displayItems($catalog, $postFilters),
];After submitting the form $postFilters looks like this:
Array ( [brand] => Array ( [0] => someBrand) [country] => Array ( [0] => someCountry)
In my model I use this query:
Code:
$items = $this->where('catalog', $catalog)
->where($postFilters)
->findAll();
return $items;If one brand and/or one country are selected in the form, everything works fine. But if I select more than one country or brand, I get error:
mysqli_sql_exception #4078
Illegal parameter data types varchar and row for operation '='
Could you please help me solve this? "Brand" and "Country" will not be the only parameters in the form, I will add more, so my query has to be dynamic.
RE: Query Builder "Where" with Array - IvanBell - 05-18-2020
I believe the problem is that "where" query uses AND. So it basically says "select items where country is Germany AND country is Sweden". I need AND between different categories, but OR between specific options ("select items where brand is something OR something OR something AND country is something OR something"). And all of this has to be dynamic, based on the options selected in the form. Is it possible to achieve it in CodeIgniter 4?
RE: Query Builder "Where" with Array - InsiteFX - 05-18-2020
Try this one.
PHP Code:
$builder->orWhere()
RE: Query Builder "Where" with Array - IvanBell - 05-18-2020
Sorry, it doesn't change anything. My $postFilters contains brands and countries selected in the form. I need "OR" between brands and countries, not between $catalog and $postFilters
RE: Query Builder "Where" with Array - InsiteFX - 05-19-2020
You may need to use the db query method then an use pure sql syntax for your queries.
RE: Query Builder "Where" with Array - akinuri - 05-19-2020
If you have multiple values to compare, why are you not using whereIn()? Since you have multiple fields (brand and country), you'll have to add them to the query seperately. While where() concatenates multiple fields/values with " AND ", whereIn expects only one field. So you'll have to do multiple whereIn()s.
I haven't tested CI 4. My assumptions are based on my experience with CI 3.
As a side question, is there a general, common way to handle filtering in CI? If I have multiple (4+) fields to filter (using equals, NOT, IN, etc.), things get out of hand a little. Accessing the db object (and using corresponding methods) for each filter doesn't seem that practical. Especially if one filter modifies/cancels the previous one.
To get around this, I've created a helper select method. I create an array, something similar to this:
PHP Code:
[
"select" => "staff.id, users.email, users.display_name, users.image, users.type",
"from" => "staff",
"join" => [
["users", "users.id = staff.user_id"]
],
"order_by" => "users.display_name",
]
and pass this to the helper select method. This makes modifying the query much easier. I get to pass the query only when it's done. Though this still has problems when doing filtering. There's no easy way to map/store/represent all where filters using an array. I just improve the helper method as problems arise.
I'd like to know how the community deals with this.
RE: Query Builder "Where" with Array - IvanBell - 05-19-2020
Thanks for your replies. Since right now I have only 2 columns, my temporary solution is this:
Code:
$this->where('catalog', $category);
if (array_key_exists('brand', $postFilters)) {
$this->whereIn('brand', $postFilters['brand']);
}
if (array_key_exists('country', $postFilters)) {
$this->whereIn('country', $postFilters['country']);
}
return $this->paginate(4);RE: Query Builder "Where" with Array - IvanBell - 05-28-2020
I have been able to achieve desired result. It was easier than I thought actually
My code looks like this now:Code:
$this->where('catalog', $category);
unset($postFilters['page']);
foreach ($postFilters as $key => $value) {
$this->whereIn($key, $value);
}
return $this->paginate(4)At first I tried to use $postFilters as $key. But it didn't work because $key is an array. Then I found out that if I use $postFilters as $key => $value, the same $key becomes a string and can be used as a column name in my query.
I am also using pagination, and it appeared that page is also part of $postFilters, so I got error when tried to go to the second page. But unset($postFilters['page']) fixes that nicely.