Laravel query error

Question

laravel return this error on execute this line:

    $select = trim($request->select);
    $where = trim($request->where);
    $d = trim($request->d);
    $order = trim(stripslashes($request->order));
    $limit = isSet($request->limit) ? " LIMIT ".trim($request->limit) : '';
    $forUser = trim(stripslashes($request->userId));
    $campaignId = trim(stripslashes($request->campaignId));
    $userRole = trim(stripslashes($request->userRole));

$events = DB::select('SELECT *, DATE_FORMAT(timestamp, ?) selector FROM events WHERE DATE_FORMAT(timestamp, ?) = ? AND campaignId = ? ORDER BY ? ASC ?', [$select, $where, $d, $campaignId, $order, $limit])->get();

Error:

[2018-05-21 19:09:22] local.ERROR: exception 'PDOException' 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 MariaDB server version for the right syntax to use near '?' at line 1' in C:\xampp\htdocs\spotlike_laravel\trunk\vendor\doctrine\dbal\lib\Doctrine\DBAL\Driver\PDOConnection.php:77

Any ideas? :(

Do you know if all those variables that you are passing have some data? For debugging reason, I will try to execute the SQL outside of Laravel if I could so you can isolate the problem. — itsdarrylnorris
– itsdarrylnorris, Commented May 21, 2018 at 19:25
You seem to miss a LIMIT keyword in your query when I look at your variables and the question marks in the query. — Namoshek
– Namoshek, Commented May 21, 2018 at 19:33
I found the query, but when I run it in phpmyadmin it does not return errors, the query look like this: SELECT *, DATE_FORMAT(timestamp, "%Y-%c-%e") selector FROM events WHERE DATE_FORMAT(timestamp, "%Y-%c") = "2018-5" AND campaignId = "194" ORDER BY timestamp, id ASC — Alexis Cervetto
– Alexis Cervetto, Commented May 21, 2018 at 19:37
Posted this as an answer, but didn't work! Will post as a comment to potentially help someone in the future: Try putting the timestamp field name in backticks. IIRC timestamp is a reserved word. Also, as a side note, I don't know if you can use bound parameters for the order by and limit parameters, because PDO will wrap the parameter values in quotes, resulting in something like: ORDER BY 'some_field' ASC 'LIMIT 30' — Phil Cross
– Phil Cross, Commented May 21, 2018 at 19:49
To get the query and bindings, try this: $events = DB::select('SELECT *, DATE_FORMAT(timestamp, ?) selector FROM events WHERE DATE_FORMAT(timestamp, ?) = ? AND campaignId = ? ORDER BY ? ASC ?', [$select, $where, $d, $campaignId, $order, $limit]); dd($event->toSql(), $event->getBindings()); That will dd the query and the parameters that are passed. Might shed a little bit more light on the problem. — Phil Cross
– Phil Cross, Commented May 21, 2018 at 19:51

Alexis Cervetto · Accepted Answer · 2018-05-21 19:56:33Z

1

Problem solved, the variable "$limit" was empty, then on the concatenate of $limit generate a blank space on the query. Thanks darol and Phil!

answered May 21, 2018 at 19:56

Alexis Cervetto

651 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Laravel query error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related