and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
I have a query searching multiple tables based on search input. The largest of the tables searched is one by Openrange, a 3rd party product info supplier. The way the information they send is laid out, is causing me problems when searching more than one attribute. Due to the nature of the way this info is used in other parts of the site it's not really possible to layout the info in the database in a different manner.
The query is :
[syntax="sql"]
SELECT SQL_CALC_FOUND_ROWS p.productID,p.name,p.shortdescription,p.price1,p.scLevel,p.thumbnail,p.code FROM jss_products AS p,jss_products_tree AS t LEFT JOIN attributes AS a ON p.extrafield2=a.productid WHERE p.productID=t.productID AND t.sectionID='3909' AND p.visible='Y' AND p.price1>0 AND ((a.name='Interface Type(s)' AND a.value='PATA') OR (a.name='Disk Capacity' AND a.value<=500 AND a.value>=250) ) GROUP BY p.productID ORDER BY p.price1 ASC LIMIT 0,20
As you can see there are 2 search attributes from the attributes table being searched. It obviously doesn't work as I found out after a bit of a braindead moment. It pulls results where one a.name OR the 2nd a.name are true.
What i want to do it pull out where only both are true.
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Last edited by rsmarsha on Wed Jan 09, 2008 4:50 am, edited 1 time in total.
I've tried a few things now and can't think of a way round the problem.
The database table is large and the amount of attributes for each product varies, hence the reason it's inserted in name/value pair format.
If anyone can offer me a solution or tip on how to get the above query working It would be much appreciated. The query needs to work for a max of 5 attributes. Creating it dynamically is not a problem, it's just getting it to only show results where each attribute check is true, that's the current stumbling block.
I have the join clause and the where clause options generated based on the number of search attributes passed via the form. So only the amount of tables needed, are joined and then searched on. Each page can have between 1 and 5 search options, so it's a good match.