How to make specific sql queries using Laravel?

Asked

Viewed 4,875 times

1

I need to make an appointment at the bank to see the birthday of the current month. A consultation of this type, see:

SELECT * FROM membros WHERE day(dataNasc) = day(CURRENT_DATE) and month(dataNasc) = month(CURRENT_DATE);

How could I make that appointment at Laravel?

I tried to make it that way but it’s coming back dates that aren’t from today (that in the case today 03/09/2017). For it comes back as 04/09 , 05/09, etc..

See how I’m trying :

$date = Membro::all();
foreach ($date as $d) {
    $explode = explode('-', $d->dataNasc);
}
$query = DB::table('membros')
           ->whereDay('dataNasc', $explode[2])
           ->whereMonth('dataNasc', $explode[1])
           ->get(['nome', 'imagem']);
dd($query);

It returns me 4 values. where only 3 values are according to the current date at the moment, but if I go to phpmyadmin and put the command

SELECT * FROM membros WHERE day(dataNasc) = day(CURRENT_DATE) and month(dataNasc) = month(CURRENT_DATE);

He returns to me only the 3 correct values of current date and current month? How can I resolve this?

database laravel-5 laravel-eloquent laravel-5.3

1 answer

4


How could I make that appointment at Laravel?

I will try to answer with some solutions:

SQL

SELECT * FROM membros 
   WHERE day(dataNasc) = day(CURRENT_DATE) 
     and month(dataNasc) = month(CURRENT_DATE);

1) With the current server date

\DB::table('membros')
    ->whereDay('dataNasc', date('d'))
    ->whereMonth('dataNasc', date('m'))
    ->get();

2) With the current date of the database

$where = 'day(dataNasc) = day(CURRENT_DATE)  and month(dataNasc) = month(CURRENT_DATE)';
\DB::table('membros')
    ->whereRaw($where)
    ->get();

1) With the current server date

Membro::whereDay('dataNasc', date('d'))
    ->whereMonth('dataNasc', date('m'))
    ->get();

2) With the current date of the database

$where = 'day(dataNasc) = day(CURRENT_DATE)  and month(dataNasc) = month(CURRENT_DATE)';
Membro::whereRaw($where)->get();

1) With the current date of the database

$sql = ' SELECT * FROM membros WHERE day(dataNasc) = day(CURRENT_DATE) ';
$sql .= ' and month(dataNasc) = month(CURRENT_DATE) '
\DB::select($sql);

that is, in this answer has 5 examples that has as output the same result, according to the question, just check which is best by the points: server where runs the application or database server, because, may have date and time difference.

References

Browser other questions tagged database laravel-5 laravel-eloquent laravel-5.3

You are not signed in. Login or sign up in order to post.