Mysql query to Laravel query Like %% Problem

2020-05-23 mysql laravel search
SELECT tmsid, RIGHT(tmsid,6) AS max_number 
FROM tms_tables 
WHERE tmsid = (SELECT MAX(tmsid) 
               FROM tms_tables 
               WHERE tmsid LIKE '%1001%' )

How to convert this to Laravel?

enter image description here

If I do like that, I get an error

A non-numeric value encountered

DB::select(DB::raw('SELECT tmsid, RIGHT(tmsid,6) 
    as max_number FROM tms_tables WHERE 
      tmsid=(SELECT MAX(tmsid) FROM tms_tables where tmsid LIKE '%1001%' )'));

Answers

Since you have wrapped your statement inside ('), you cannot use '%100%'. So wrap your statement using ("). Try below code

DB::select(DB::raw("SELECT tmsid, RIGHT(tmsid,6) as max_number FROM tms_tables WHERE tmsid=(SELECT MAX(tmsid) FROM tms_tables where tmsid LIKE '%1001%' )"));

try to used like that

$tmsid = 1001;

DB::select(DB::raw("SELECT tmsid, RIGHT(tmsid,6) as max_number FROM tms_tables WHERE tmsid=(SELECT MAX(tmsid) FROM tms_tables where tmsid LIKE :tmid)"),['tmid' => '%'.$tmsid.'%']);

You are using single quotes to both enclose the entire query string as well as enclose the field in the query. You should use the query builder to its full capacity instead and let laravel deal with things like preparing statements:

DB::table('tms_tables')
    ->select('tmsid', DB::raw('RIGHT(tmsid, 6) as max_number'))
    ->where('tmsid', function ($query) {
         $query->selectRaw('MAX(tmsid')
               ->from('tms_tables as tms_tables_inner')
               ->where('tmsid', 'LIKE', '%1001%');
     });

This should create a functionally equivalent query to what you have

Related