[SOLVED] Search query question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rsmarsha
Forum Contributor
Posts: 242
Joined: Tue Feb 08, 2005 4:06 am
Location: Leeds, England

[SOLVED] Search query question

Post 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]
Last edited by rsmarsha on Wed Jan 09, 2008 4:50 am, edited 1 time in total.
rsmarsha
Forum Contributor
Posts: 242
Joined: Tue Feb 08, 2005 4:06 am
Location: Leeds, England

Post 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. :(
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post 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>)
rsmarsha
Forum Contributor
Posts: 242
Joined: Tue Feb 08, 2005 4:06 am
Location: Leeds, England

Post 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. :)
rsmarsha
Forum Contributor
Posts: 242
Joined: Tue Feb 08, 2005 4:06 am
Location: Leeds, England

Post 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.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post 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)
rsmarsha
Forum Contributor
Posts: 242
Joined: Tue Feb 08, 2005 4:06 am
Location: Leeds, England

Post by rsmarsha »

Going off topic. ;)

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