CODEWITHSUNDEEP
phpdatabaselaravel
Anita Intansia Hutagaol
Anita Intansia Hutagaol

Reputation: 131

syntax between inside syntax like in php(laravel)

i wanna make a query to get start date and end date using between, and start_date & end_date is an input from user so i use syntax like '%'.

here my code, but not working

    public function getData()
{
    $inputname = Input::get('searchname');
    $startdate = Input::get('tglmulai');
    $enddate   = Input::get('tglsiap');

    $name = DB::table('tr_visit')
        ->join('tm_child','tr_visit.Child_ID','=','tm_child.Child_ID')
        ->select('tm_child.Child_Name','Bmi_Score')
        ->where('tm_child.Child_Name', 'LIKE', '%'.$inputname.'%')
        ->whereBetween('Visit_Date', 'LIKE', '%'.$startdate.'%' and 'LIKE', '%'.$enddate.'%')
        ->get();

    return view('Laporan.Kehadiran.kehadiranview', compact('name'));

}

Upvotes: 1

Views: 62

Answers (1)

Daniel Brose
Daniel Brose

Reputation: 1403

Please always check the documentation for code examples using the methods you wanted.

Where Between

Notably, the whereBetween http://laravel.com/docs/4.2/queries#advanced-wheres

I havent used that particular method myself, but it looked off so i found the doco and saw this:

$users = DB::table('users')
                ->whereBetween('votes', array(1, 100))->get();

I cant run your code to be certain, but it seems like this might work for you:

public function getData()
{
    $inputname = Input::get('searchname');
    $startdate = Input::get('tglmulai');
    $enddate   = Input::get('tglsiap');

    $name = DB::table('tr_visit')
        ->join('tm_child','tr_visit.Child_ID','=','tm_child.Child_ID')
        ->select('tm_child.Child_Name','Bmi_Score')
        ->where('tm_child.Child_Name', 'LIKE', '%'.$inputname.'%')
        ->whereBetween('Visit_Date', array($startdate, $enddate))
        ->get();

    return view('Laporan.Kehadiran.kehadiranview', compact('name'));

}

This is assuming the ->select() and other methods are work without issue (take out the whereBetween and confirm its working without it first ofcourse).


Wildcards Note

You do realise that '%' is just a wildcard character for LIKE.

It doesn't have anything to do with user input, its just to match any values containing the substring between the '%'.

SQL Wildcards: http://www.w3schools.com/sql/sql_wildcards.asp


DateTime strings in SQL Between

In this situation, you should really be using a datepicker or similar in your html, or be sure to cast the input (which you havent specified the format of) to a format for sql to pick up on.

The SQL Between documentation shows the following example:

SQL Between: http://www.w3schools.com/sql/sql_between.asp

SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;

So a plain string should work, just be certain its of the same format as the database.

There is some on this (using direct sql) here: SQL query to select dates between two dates

How you achieve this format in the html (or client-side js) is one of many many approaches, but there are many easy to use javascript datepickers out there, even if not using jquery or other library.

Upvotes: 2

Related Questions