Laravel 9.47. 0 SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax
I am changing the database use from postgresDB to mysqldb. As the title suggest, I never met an error like this. How can I fix this ?
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 'ilike ? limit 1' at line 1 (SQL: select * from activities where name ilike BADMINTON limit 1)
P.S : after doing some research, i found that 'ilike' is not supported in mysql. from what I read, people will use LOWER, and UPPER . is this the right way to do this ? Please help. This is my first day on job , the company uses postgres and i want to run it in my local environment where i usually use mysql. like a brick hit on my head. am f****d
The error message suggests that the SQL query is using the ilike operator, which is specific to PostgreSQL and not supported by MySQL. To fix this, you can replace ilike with like and use the LOWER or UPPER functions to make the comparison case-insensitive.
Here's an example of how you can modify the query:
// Original query
select * from activities where name ilike BADMINTON limit 1
// Modified query for MySQL
select * from activities where lower(name) like '%badminton%' limit 1
In the modified query, we're using the LOWER function to convert the name column to lowercase, and then using the LIKE operator to perform a case-insensitive search for the string "badminton". The % symbols are wildcards that match any characters before or after the search term.
You'll need to update any other queries that use ilike in a similar way.
