Home / Questions / 427089
Accepted
Kenny Barrera
Asked: 2022-02-10 13:25:08 +0800 CST 2022-02-10 13:25:08 +0800 CST

Illuminate/Database/QueryException with message 'SQLSTATE[42000]:

  • 772

I have this line of code in a relatively simple Laravel command

User::where('nda_approved', 0)->whereRaw('(mod(DATEDIFF(`created_at`,\''.Carbon::now().'\'),7) = 0')->get();

When doing a little test in Tinker, I get the following error

Illuminate/Database/QueryException with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 (SQL: select * from userswhere nda_approved= 0 and (mod(DATEDIFF( created_at,'2021-02-09 12:59:09' ),7) = 0)'

However, if I copy the query and paste it directly into Workbench (which is connected to the same database)

select * from `users` where `nda_approved` = 0 and (mod(DATEDIFF(`created_at`,'2021-02-09 12:59:09'),7) = 0)

Everything works fine, and I get the desired rows. Any idea why this happens?

mysql

1 Answers

  1. Best Answer

    The problem is related to the quotes, because as you notice in the error message query you have a single quote at the end that does not match another at the beginning:

    09 12:59:09'),7) = 0)'

    More the use of some diagonals ( which I assume are to try to escape something ), but they are not necessary

    So to respect and use the SQL query that you expose works, you can use it in this way at the Laravel level:

    $fechaActual = now();

User::where('nda_approved', 0)
    ->whereRaw("mod(DATEDIFF('created_at', '$fechaActual'),7) = 0")
    ->get();

    Lastly, you don't need to call the class Carbon( strictly ) to access its features, instead rely on the helper that gives you access to a now()Carbon instance .

    • 0