I have a MySQL table named tasks, like this:
| task_text | task_age | task_keywords |
|---|---|---|
| task text | (not important) | serene,peacful,relaxing |
I need to build a query where a user selects three keywords as input, and I need to get the task_text where the task_keywords matches the three keywords the user has selected (for example, “serene”, “peaceful”, “relaxing”).
This is my sql statement:
SELECT task_text FROM tasks WHERE task_age < ? AND task_keywords IN (?,?,?);
I am using prepared statements and the last three ? are the three keywords the user has provided.
However, when I run this code, it returns that there are no matching records in the table. This is incorrect, because it should return one record.
How can I fix this?
7
The schema design in broken. With the current design, as far as the database knows the task_keywords column in the table has ONE keyword that happens to contain a couple commas.
It’s rarely a good idea to put delimited data in a column, especially if that column will be used with WHERE or ON predicates. The best solution here is fixing this broken schema by adding another table for the keywords, where each keyword has it’s own row. Alternatively, if there are always exactly three keywords this could be three separate columns in the existing table.
0