I have problem when trying to bind the data in to query. I'm use php framework Laravel 5.7 version.
Trying to bind data this way:
DB::raw("(
select 10 * FLOOR(SUM(TIME_TO_SEC(TIMEDIFF(end_time, start_time)) - 3600 ) * (drivers.salary_per_hour / 3600) / 10)
from assignments
where assignments.driver_id = drivers.id
and assignments.driving_day between STR_TO_DATE('?', '%Y-%m-%d') and STR_TO_DATE('?', '%Y-%m-%d') ) as salary",[
$dates['from'],
$dates['to']
])
and receive null as result.
$dates array values:
['from' => '2019-03-01', 'to' => '2019-03-31']
But if I'm doing it this way it work fine, but there is SQL injection, and I'm think it will be better if I'm bind the data.
$drivers = Driver::select([
'id', 'name', 'surname', 'phone', 'driver_status','driver_status', 'updated_at', 'updated_at', 'photo',
'salary_per_hour',
DB::raw("(
select 10 * FLOOR(SUM(TIME_TO_SEC(TIMEDIFF(end_time, start_time)) - 3600 ) * (drivers.salary_per_hour / 3600) / 10)
from assignments
where assignments.driver_id = drivers.id
and assignments.driving_day between STR_TO_DATE('".$dates['from']."', '%Y-%m-%d') and STR_TO_DATE('".$dates['to']."', '%Y-%m-%d') ) as salary"),
DB::raw("(
select SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(end_time, start_time)) - 3600 ))
from assignments
where assignments.driver_id = drivers.id
and assignments.driving_day between STR_TO_DATE('".$dates['from']."', '%Y-%m-%d') and STR_TO_DATE('".$dates['to']."', '%Y-%m-%d') ) as worked_hours"),
])->whereHas('assigments', function ($query) use ($dates) {
$query->whereBetween('driving_day', [$dates['from'], $dates['to']]);
});
$table = $drivers->paginate(15);
What's wrong in my data binding?
via Chebli Mohamed
Aucun commentaire:
Enregistrer un commentaire