How to compare Timestamp field with Date values. (MYSQL)

I want to get the data between 2 dates. My query is working in the mysql. but for some reason not working in the laravel DB raw query.

My this query where data is static works in the laravel DB RAW and i get the data.


$profit_data = DB::select("SELECT 
job.name,
sum(job_payments.payments) AS total_pay, 
From job_payments
LEFT JOIN job on job.id =  job_payments.job_id
WHERE 
job.code= 2 AND job.status = 'ended' 
AND (DATE(job .start_date) BETWEEN '2021-08-17' AND '2021-08-17' ) 
GROUP BY 
job.status");

My this query doesn't work when i put the $variables instead of static date. I get no error, but the result i get is empty when i use variables for dates.


$start_date = $request->start_date;
$end_date = $request->end_date;
        
$start_date = date('Y-m-d', strtotime($start_date));
$end_date = date('Y-m-d', strtotime($end_date));

$profit_data = DB::select("SELECT 
job.name,
sum(job_payments.payments) AS total_pay, 
From job_payments
LEFT JOIN job on job.id =  job_payments.job_id
WHERE 
job.code= 2 AND job.status = 'ended' 
AND (DATE(job.start_date) BETWEEN $start_date AND $end_date ) 
GROUP BY 
job.status");

Snapey

4 years ago

Level 122

Show your DB query

princeparaste

4 years ago

Level 1

$profit_data = DB::select("SELECT 
job.name,
sum(job_payments.payments) AS total_pay, 
From job_payments
LEFT JOIN job on job.id =  job_payments.job_id
WHERE 
job.code= 2 AND job.status = 'ended' 
AND (DATE(job.start_date) BETWEEN $start_date AND $end_date ) 
GROUP BY 
job.status");

salmon

4 years ago

Level 6

With single quotes it becomes a string constant. Try to use double quotes. i.e. $start_date = "2021-08-17";

princeparaste

4 years ago

Level 1

I am getting dates from the datepicker, then i convert them to mysql format to match.

$start_date = $request->start_date;
$end_date = $request->end_date;
        
$start_date = date('Y-m-d', strtotime($start_date));
$end_date = date('Y-m-d', strtotime($end_date));

jlrdw

4 years ago

Level 75

Try running each through a function like:

  $tdate = new \DateTime($request->start_date);
  $ndate = $tdate->format('Y-m-d');
  return $ndate;

A quick function:

    public static function fixDate($fdate) {
         $tdate = new \DateTime($fdate);
         $ndate = $tdate->format('Y-m-d');
         return $ndate;
    }

Adjust as needed

Just make a quick helper. Or use like that twice replacing your values as needed. Or use carbon.

1 like

salmon

4 years ago

Level 6

DB::select() has an option that allows you to pass an array of parameters that is inherently safe from SQL injection.

example

$profit_data = DB::select("SELECT 
job.name,
sum(job_payments.payments) AS total_pay, 
From job_payments
LEFT JOIN job on job.id =  job_payments.job_id
WHERE 
job.code= 2 AND job.status = 'ended' 
AND (DATE(job.start_date) BETWEEN :startDate  AND :endDate) 
GROUP BY 
job.status", array('startDate' => $start_date, 'endDate' => $end_date));

Snapey

4 years ago

Level 122

$profit_data = DB::select("SELECT job.name, sum(job_payments.payments) AS total_pay, 
       From job_payments LEFT JOIN job on job.id =  job_payments.job_id")
        ->where('job.code',2)
        ->where('job.status','ended') 
        ->whereDate('job.start_date' '>=',$start_date)
        ->whereDate('job.start_date','<',$end_date ) 
        ->groupBy('job.status")
        ->get();

1 like

princeparaste

4 years ago

Level 1

Thanx @snapey for converting it to Eloquent query, but this is not my original query. Its is similar to this with lots of joins and where, I didn't post the same because of privacy issue. But yeah converting the the raw query to Eloquent solved my problem.

Thanks @everyone for helping me out

Please or to participate in this conversation.