mySql & Coldfusion: WHERE LIKE not working

I have a select in a result page the WHERE LIKE isn’t working? This is the query I have:

<cfquery name="getProducts" datasource="#application.dsn">
    SELECT p.product_id, p.artist, p.title, p.publisher, p.release_date, p.condition, p.price, p.category_id, c.category, p.genre_id, g.genre
    FROM products p
    LEFT JOIN categories c ON p.category_id = c.category_id
    LEFT JOIN genres g ON p.genre_id = g.genre_id
   WHERE (artist LIKE '%#Form.txtSearch#%'
    OR title LIKE '%#Form.txtSearch#%' 
    OR category LIKE '%#Form.txtSearch#%' 
    OR genre LIKE '%#Form.txtSearch#%')

All the mentioned fields are indexed. What am I doing wrong?

Hi rudi, Yes the query is working now. It gives me the desired results when searching. Thank you

so it’s okay now? or is the query still not working?

i’m sorry, “isn’t working” is not a valid mysql error message

perhaps you can describe what the query is actually doing?

My wrong :injured: Should have been


since I use method method=“get”

FYI, MySQL “like” searches often don’t work if you don’t have an index on the column you’re searching.

I don’t know why, and I don’t know how inconsistent it is, but I’ve always been able to fix this problem by adding an index to the column I’m searching.

Like I answered to rudi, it was my wrong. I used:

LIKE '%#Form.txtSearch#%'

instead of

LIKE '%#Url.txtSearch#%'

I had the method of my form changed from post to get, so that’s why. But you’re right it is ever a bad idea to make certain main fields a key as well.