syntax between inside syntax like in php(laravel)

Question

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'));

}

In standard SQL, I don't think using between and Like work to gather. — ArtisticPhoenix
– ArtisticPhoenix, Commented Jul 29, 2015 at 4:30

Community · Accepted Answer · 2017-05-23 11:58:00Z

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.

Collectives™ on Stack Overflow

syntax between inside syntax like in php(laravel)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related