MySQL IN statement using Laravel
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