js864 js864 - 25 days ago 8
MySQL Question

SQL statement using contains invalid

Trying to get a SQL query to return messages that contain an inputted string. The query works without that WHERE CONTAINS part of the SQL statement, but either returns nothing or returns a SQL invalid error when trying to get the WHERE CONTAINS part to work. Any ideas on how to get this to work?

"SELECT user_username, text, posted_at FROM Messages WHERE text CONTAINS ? ORDER BY posted_at DESC;"


Other syntax I've tried:

"SELECT user_username, text, posted_at FROM Messages WHERE CONTAINS(text, ?) ORDER BY posted_at DESC;"

"SELECT user_username, text, posted_at FROM Messages WHERE text LIKE '%?%' ORDER BY posted_at DESC;"

Answer

The last should work if you phrase it as:

SELECT user_username, text, posted_at
FROM Messages
WHERE text LIKE CONCAT('%', ?, '%')
ORDER BY posted_at DESC