Im having the user filling checkboxes in my view for each year so that they can query based on selected years. The query is looking for a pH value between a minimum and maximum value. As you can see there can be a variety of different years selected.
View
@foreach($years as $year)
{{ Form::checkbox('selectedYears[]', $year->year) }} {{ $year->year }}
@endforeach
In my controller i then have something like this:
Controller
$implodeYears = implode(', ',$input['selectedYears']);
DB::select('SELECT samples.*, costumers.*
FROM samples
LEFT JOIN costumers
ON samples.costumer_id = costumers.id
WHERE
pH BETWEEN ? AND ?
AND YEAR IN ( ? )
Order BY Year DESC, samples.costumer_id DESC
', array(
$minph,
$maxph,
$implodeYears
));
I checked my variable, and it seems to be fine:
dd($implodeYears);
string(34) "2008, 2009, 2010, 2011, 2012, 2013"
The problem is that the query only returns the first year in the string. So i get that there is something wrong with this being a string. When i query like this it works fine:
Static years in controller
DB::select('SELECT samples.*, costumers.*
FROM samples
LEFT JOIN costumers
ON samples.costumer_id = costumers.id
WHERE
pH BETWEEN ? AND ?
AND YEAR IN (2008, 2010, 2012)
Order BY Year DESC, samples.costumer_id DESC
', array(
$minph,
$maxph
));
I have had this query working fine when i previously worked without Laravel. Thats the weird thing, and i get the exakt same thing in that version when var_dumping the imploded variable. Result below.
Old Version with a standard MySQL query using var_dump
string(89) "1997,1998,1999,2000,2001,2002,2003,2004,2005,2006,2007,2008,2009,2010,2011,2012,2013,2014"
Is there someone who can give me a hint? Im shure there's a pretty good explanation for this. But i have googled for some time, and can't find it.
Thanks, Simon
Best Answer :
I strongly recommend you do not do something like that. The reason your code is currently returning only the first year is a combination of prepared statement misunderstanding, and typecasting.
When you do what you are doing there, the where part of the SQL query fired off to the server is equivalent to:
YEAR IN ("2008, 2009, 2010...")
As your field is most likely an integer, MySQL tries to understand/make sense of what you sent it, and transforms to this:
YEAR IN (2008)
This explains your first value.
How to fix it
The short version is properly use the Laravel query builder. The following will work:
DB::table('samples')
->select('samples.*', 'costumers.*')
->join('costumers', 'samples.costumer_id', '=', 'costumers.id')
->whereIn("samples.year", $input['selectedYears'])
->where("samples.pH", ">=", $minph)
->where("samples.pH", "<=", $maxph)
->get();
Pay very close attention; if the year list is empty, this will fail (but then again, so will yours)
source : http://stackoverflow.com/questions/28836024/mysql-in-statement-using-laravel?rq=1
