Identify null values in Laravel and MySQL

Question

Identify null values in Laravel and MySQL

Navigation

#1 by (4 votes)

5

I need to make a query to update a data from the relation of two tables, where if the given date (variable $fecha_actualizar ) is the same for perfiles_evento and to check, update the stage in which it is to 4, I think the query is fine, but also if in perfiles_evento.fecha is null, I should buy if evento.asistencia = 1; I tried to do in 2 separate querys but executed in the same controller:

$query = DB::table('perfiles_evento')
->where('perfiles_evento.evento_id', $evento_id)
->where('perfiles_evento.fecha',$fecha_actualizar)
->join('check','perfiles_evento.evento_id','check.evento_id')
->where('checkins.fecha',$feha_actualizar)
->update(['perfiles_evento.stage' => 4]);

here is the first, the second is:

$query = DB::table('perfiles_evento')
->where('perfiles_evento.evento_id', $evento_id)
->where('perfiles_evento.fecha',NULL)
->join('check','perfiles_evento.evento_id','check.evento_id')
->where('checkins.asistencia',1)
->update(['perfiles_evento.stage' => 4]);

The problem is that when executing

select fecha from perfiles_evento where perfiles_evento.fecha = NULL;

in the mysql console, it brings me an empty table, but there are empty dates. so I do not know if the query is working correctly when executing it in the controller in laravel. Both values are "date" type.

mysql laravel

asked by Erick López Martinez 29.06.2018 в 16:32

source

1 answer

why do you give me this error Undefined variable:? Replace strings with replace JS

score 4 · Answer 1

QueryBuilder has a specific method for verifying null values:

whereNull

The whereNull method verifies that the value of the given column is    NULL

The whereNull method verifies that the value of the given column is NULL (null).

-View Additional Where Clauses in the documentation

If you write your query this should work:

$query = DB::table('perfiles_evento')
  ->where('perfiles_evento.evento_id', $evento_id)
  ->whereNull('perfiles_evento.fecha')
  ->join('check','perfiles_evento.evento_id','check.evento_id')
  ->where('checkins.asistencia',1)
  ->update(['perfiles_evento.stage' => 4]);

Regarding the way to verify if a column contains NULL directly in MySQL, this query:

select fecha from perfiles_evento where perfiles_evento.fecha = NULL;

will never bring data, because in MySQL the comparison of NULL with another value, including the same NULL , is never true . Therefore, to know if the value of a column is NULL the correct form would be:

select fecha from perfiles_evento where perfiles_evento.fecha IS NULL;

For more details on this you can see this answer and the link in it on the question of the nulls in MySQL.