All In One Script



PHP,HTLM,CSS,Jquery,AJAX,Javascript and etc doubts and sample codes

  • Home
  • Javascript
  • PHP
  • CSS
  • SQL/MYSQL

MySQL IN statement using Laravel

by Blogger 2:58:00 AM laravel MYSQL SQL SQL IN Statement

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


READ MORE
SHARE :

Search This Blog

Followers

  • Popular
  • Recent
  • Comments
    How to get Real IP,ISP,Country,City and etc from Visitor using PHP
    How to efficiently iterate over each Entry in a Map?
    In Java, difference between default, public, protected, and private
    How to check if PHP array is associative or sequential?
    Length of a JavaScript object
    How to return the date part only from a SQL Server datetime datatype
    Does finally always execute in Java?
    What is the difference between “INNER JOIN” and “OUTER JOIN”?
    Get first key in a (possibly) associative array?
    Should I use field 'datetime' or 'timestamp'?

Instagram

About

Popular Posts

  • How to get Real IP,ISP,Country,City and etc from Visitor using PHP
    How to get Real IP,ISP,Country,City and etc from Visitor using PHP Php Get Real visiter's IP and ISP and Country and City and Countr...
  • How to efficiently iterate over each Entry in a Map?
    How to efficiently iterate over each Entry in a Map? If I have an object implementing the  Map  interface in Java and I wish to iterate...
  • In Java, difference between default, public, protected, and private
    In Java, difference between default, public, protected, and private In Java , are there clear rules on when to use each of access modifi...
  • How to check if PHP array is associative or sequential?
    How to check if PHP array is associative or sequential? PHP treats all arrays as associative, so there aren't any built in function...
  • Length of a JavaScript object
    Length of a JavaScript object If I have a JavaScript object, say var myObject = new Object (); myObject [ "firstname" ] ...
  • How to return the date part only from a SQL Server datetime datatype
    How to return the date part only from a SQL Server datetime datatype SELECT GETDATE () Returns:  2008-09-22 15:24:13.790 I want tha...
  • Does finally always execute in Java?
    Does finally always execute in Java? I have a try/catch block with  return s inside it. Will the finally block be called? For example...
  • What is the difference between “INNER JOIN” and “OUTER JOIN”?
    What is the difference between “INNER JOIN” and “OUTER JOIN”? Also how do  LEFT JOIN ,  RIGHT JOIN  and  FULL JOIN  fit in? Answ...
  • Get first key in a (possibly) associative array?
    Get first key in a (possibly) associative array? What's the best way to determine the first key in a possibly associative array? My...
  • Should I use field 'datetime' or 'timestamp'?
    Should I use field 'datetime' or 'timestamp'? Would you suggest using a datetime or a timestamp field, and why (using M...

statcounter



statcounter



Template Created By ThemeXpose & Blogger Templates