221

I'm using the below code to pull some results from the database with Laravel 5.

BookingDates::where('email', Input::get('email'))->orWhere('name', 'like', Input::get('name'))->get()

However, the orWhereLike doesn't seem to be matching any results. What does that code produce in terms of MySQL statements?

I'm trying to achieve something like the following:

select * from booking_dates where email='my@email.com' or name like '%John%'
Mukyuu
  • 6,436
  • 8
  • 40
  • 59
V4n1ll4
  • 5,973
  • 14
  • 53
  • 92

7 Answers7

500

If you want to see what is run in the database use dd(DB::getQueryLog()) to see what queries were run.

Try this

BookingDates::where('email', Input::get('email'))
    ->orWhere('name', 'like', '%' . Input::get('name') . '%')->get();
Pawel Bieszczad
  • 12,925
  • 3
  • 37
  • 40
  • 47
    is this query sql injection protected? – partho Jul 23 '17 at 10:56
  • 31
    @partho Yes. Laravel screens the whole string that you pass as the third argument of the `where` method. – Finesse Aug 18 '17 at 01:45
  • 15
    While injection protected you might want to check for unexpected % in user input. E.g., LIKE "%John%" and LIKE "John%" perform differently (you might only intend the latter). Also consider empty input, and then of "%" alone, which also might lead to unintended results from the above code. – Ian Fleeton Jun 10 '18 at 17:48
  • 6
    Agreed with Ian. Laravel only does partial escaping. There's still a lot of mischief possible if you don't properly escape the LIKE. Here's how: https://stackoverflow.com/a/42028380/329062 – Greg Sep 12 '18 at 13:19
  • 2
    I added `preg_replace("/[^A-Za-z0-9 ]/", '', $search);` because I don't need special chars, and it protects it from `%` injection too – Heichou Sep 07 '20 at 12:19
33

I have scopes for this, hope it help somebody. https://laravel.com/docs/master/eloquent#local-scopes

public function scopeWhereLike($query, $column, $value)
{
    return $query->where($column, 'like', '%'.$value.'%');
}

public function scopeOrWhereLike($query, $column, $value)
{
    return $query->orWhere($column, 'like', '%'.$value.'%');
}

Usage:

$result = BookingDates::whereLike('email', $email)->orWhereLike('name', $name)->get();
Oleh Diachenko
  • 612
  • 1
  • 7
  • 9
19
$data = DB::table('borrowers')
        ->join('loans', 'borrowers.id', '=', 'loans.borrower_id')
        ->select('borrowers.*', 'loans.*')   
        ->where('loan_officers', 'like', '%' . $officerId . '%')
        ->where('loans.maturity_date', '<', date("Y-m-d"))
        ->get();
sadiq rashid
  • 468
  • 5
  • 8
15

I think this is better, following the good practices of passing parameters to the query:

BookingDates::whereRaw('email = ? or name like ?', [$request->email,"%{$request->name}%"])->get();

Better:

BookingDates::where('email',$request->email)
    ->orWhere('name','like',"%{$request->name}%")->get();

You can see it in the documentation, Laravel 5.5.

You can also use the Laravel scout and make it easier with search. Here is the documentation.

JaredDmz
  • 153
  • 2
  • 7
  • 1
    Raw is **never** better. You should sacrifice functionality if it requires `raw` to achieve it, or approach it differently. – zanderwar May 13 '22 at 09:55
10

the query which is mentioned below worked for me maybe it will be helpful for someone.

 $platform = DB::table('idgbPlatforms')->where('name', 'LIKE',"%{$requestedplatform}%")->first();
M Mamoon Khan
  • 169
  • 2
  • 9
-1

If you wish to use it on controller you can do something like:

$generatequery = 'select * from blogs where is_active = 1 and blog_name like '%'.$blogs.'%' order by updated_at desc, id desc';

$blogslists = DB::select($generatequery);

Ankush_Sharma
  • 1
-2

If you are using Postgres, The key word ILIKE can be used instead of LIKE to make the match case-insensitive according to the active locale. This is not in the SQL standard but is a PostgreSQL extension.

this worked for me.

User::where('name, 'ILIKE', $search)->get();

postgres documentation

manzede
  • 49
  • 5
  • 1
    I do not get the connection between `What does that code produce in terms of [SQL] statements?` and above reply - what question does this answer? – greybeard Jun 05 '21 at 09:03
  • then using this isn't safe – Dendi Handian Aug 31 '21 at 06:48
  • LIKE is case-insensitive, what collation are you using for it to not be insensitive, definitely not utf8mb4... – zanderwar May 13 '22 at 09:57
  • @zanderwar WRONG !!! - in Postgresql LIKE is case-sensitive and you **have to** use ILIKE to make it case-insensitive. _The key word ILIKE can be used instead of LIKE to make the match case-insensitive according to the active locale. This is not in the SQL standard but is a PostgreSQL extension._ [docs](https://www.postgresql.org/docs/current/functions-matching.html) – dmikam Jan 04 '23 at 10:47