Restructure a query...

[RIRedinPA]

Hi

 

I have a web site that displays a production schedule with the data for the schedule stored in a MySQL database. It's pretty straight forward, user selects options from form, clicks button, query is constructed, database tapped, results returned and displayed.

 

The problem I am running into is that once the user gets the results they can filter those results by type (a button click). I have two type, named shop and cp. I store the initial query in a session variable because the user can also sort the results after they retrieve them - so if the initial query was SELECT * FROM schedtable WHERE status = 'production' I could then change that to SELECT * FROM schedtable WHERE status = 'production' ORDER BY duedate

Adding a filter to the results is not as easy. Take this query for example - SELECT * FROM scheduleTable WHERE status LIKE '%Production%' OR status LIKE '%Admin%'  OR status LIKE '%Live%'  OR status LIKE '%On Hold%'. When the user clicks on the 'shop' filter link I retrieve the query and initially tried to change it to SELECT * FROM scheduleTable WHERE status LIKE '%Production%' OR status LIKE '%Admin%'  OR status LIKE '%Live%'  OR status LIKE '%On Hold%' AND itemtype = 'shop' but that changed nothing, I was still seeing both the cp and shop results from the initial query...

 

I manually changed it to this:

 

SELECT * FROM scheduleTable WHERE itemtype = 'shop' AND status LIKE '%Production%' OR itemtype ='shop' AND status LIKE '%Admin%'  OR itemtype = 'shop' AND status LIKE '%Live%'  OR itemtype = 'shop' AND status LIKE '%On Hold%'

 

and that did get the results I wanted but to change the first query to this would take way too much programming. I tried the original query and added GROUP BY itemtype but that gave me one of each group. I'm sure there is a way to achieve what I want without having to break apart the original query and rebuild it.

 

Thanks

[fenway]

This seems like an order of precedence issue... you'll need to wrap your ORs in parens.