Page 1 of 1

[SOLVED] Search query question

Posted: Mon Jan 07, 2008 5:45 am
by rsmarsha
pickle | Please use

Code: Select all

,

Code: Select all

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.

So it would only pull results where

Code: Select all

a.name='Interface Type(s)' AND a.value='PATA'
AND

Code: Select all

a.name='Disk Capacity' AND a.value<=500 AND a.value>=250
Are both true.

Any ideas?


pickle | Please use[/syntax]

Code: Select all

,

Code: Select all

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]

Posted: Mon Jan 07, 2008 9:07 am
by rsmarsha
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. :(

Posted: Mon Jan 07, 2008 12:45 pm
by Kieran Huggins
If you're joining two rows from the attribute table, don't you need to join them separately?

Code: Select all

JOIN attributes AS a1 .... JOIN attributes AS a2
(Disclaimer: I may be completely full of <span style='color:blue' title='I&#39;m naughty, are you naughty?'>smurf</span>)

Posted: Tue Jan 08, 2008 2:21 am
by rsmarsha
Ah not seen it used in that way before. I'm assuming the where clause for attribute 1 would have to be where a1.... and then where a2.... and so on?

I have up to 5 attributes, this could get messy, hehe. I'll try a few things and get back to you if it works, thanks for the tip.

Any other suggestions welcomed, always good to learn new things. :)

Posted: Wed Jan 09, 2008 4:50 am
by rsmarsha
That seems to work fine. :)

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.

Thanks again for the tip.

Posted: Wed Jan 09, 2008 5:42 am
by Kieran Huggins
Awesome! I guess I'm not as full of <span style='color:blue' title='I&#39;m naughty, are you naughty?'>smurf</span> as I thought I was... 8)

Posted: Wed Jan 09, 2008 6:58 am
by rsmarsha
Going off topic. ;)

Smurfs give wisdom, new tip for the day. ;)