LARAVEL Update data using array

Question

i wanna update my data using array, but its error 'cause im using "like" query while finding the data. This is the code.

$pin = 10590;
$datetime = "2017-08-29 15:05:00";
$date = explode(' ', $datetime);
Attendance::where('user_id', $pin)->where('datetime_in', 'like', '%' . $date[0] . '%')->update([
   'datetime_out' => $datetime,
   'updated_at' => date('Y-m-d')
]);

any solutions ?? Thanks.

There should not be an error. can you add detailed output of debugger? — Buglinjo, Aug 29 '17 at 18:38
@Buglinjo the server is down right now so i cant show the output, but if i run the code in mysql, the error is like this `Incorrect datetime value: '%2017-08-29%' for column 'datetime_in'` — skadevz, Aug 29 '17 at 19:04
You should be using like filter on varchar or text not datetime. On datetime simply use = — Buglinjo, Aug 29 '17 at 19:05
@Buglinjo like this? `UPDATE attendance SET datetime_out="2017-08-29 15:06:00" WHERE user_id = 201037917001 AND datetime_in = "2017-08-29";` — skadevz, Aug 29 '17 at 19:10
Like this: `Attendance::where('user_id', $pin)->where('datetime_in', '=', $date[0])->update([ 'datetime_out' => $datetime, 'updated_at' => date('Y-m-d') ]);` — Buglinjo, Aug 29 '17 at 19:13

score 0 · Accepted Answer · answered Aug 29 '17 at 19:54

You cannot compare a datetime column using like.

Comparing using =, as suggested in the comments will also not yield the desired result, interpolating 2017-08-29 to 2017-08-29 00:00:00 (see SO Answer).

1. Cast column to DATE

Assuming you're using MySQL, you can cast the type of the queried column to DATE like this:

$query->whereRaw('DATE(datetime_in) = ?', [$day])…

PostgreSQL:

$query->whereRaw('datetime_in::date = ?', [$day])…

2. WHERE BETWEEN

Alternatively, you can query using whereBetween and specify start end end of day as time by using Carbon.

use Carbon\Carbon;

$startOfDay = new Carbon($datetime)->startOfDay();
$endOfDay = new Carbon($datetime)->endOfDay();

$query->whereBetween('datetime_in', [$startOfDay, $endOfDay])…

LARAVEL Update data using array

1 Answers1