Syntax error or access violation - Laravel

Question

I am trying to select data from the database, unfortunelety I am getting below error :

"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 'to,amount,from,date,status,provider from api_transactions where to=?' at line 1 (SQL: SELECT merchant_name,to,amount,from,date,status,provider from api_transactions where to=00000) in file /home/nosi/myProjects/paylesotho/vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 664 "

Below is what I did inside the controller:

public function TransactionDetails ($merchant_id){
        $client_data = DB::select('SELECT merchant_name,to,amount,from,date,status,provider from api_transactions where to=?', [$merchant_id]);
        return response()->json($client_data);
    }

[When to use single quotes, double quotes, and backticks in MySQL](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql) — brombeer, May 10 '23 at 08:48
If you use laravel, then why do you use a simple raw query rather than the built-in ORM to get the data? I understand the use of raw sql for a very complicated report with subqueries functions and aggregations. — Shadow, May 10 '23 at 09:27

Refilon · Accepted Answer · 2023-05-10T08:49:55.573

1

to is a reserved keyword in mysql. You should wrap to in backticks.

$client_data = DB::select('SELECT `merchant_name`,`to`,`amount`,`from`,`date`,`status`,`provider` from `api_transactions` WHERE `to`=?', [$merchant_id]);

edited May 10 '23 at 08:49

answered May 10 '23 at 08:38

Refilon

3,334
1
27
51

Wouldn't you need backticks for the first `to` too? – brombeer May 10 '23 at 08:47
I would recommend surrounding column_names with backticks always. But the error is at the where clause. – Refilon May 10 '23 at 08:48
There is a canonical duplicate for these type of questions, please use that in the future rather than answering such questions every time. – Shadow May 10 '23 at 09:25
Btw, from is also a key word... – Shadow May 10 '23 at 09:28
@Shadow maybe so, but that was not in the error so that's why I did not specifiy that in the answer. – Refilon May 13 '23 at 09:44

Syntax error or access violation - Laravel

1 Answers1