how to filter results from 4 dynamic list menus depandant on how many of them are selected

Report · Oct 21, 2009

I have a search page with a form with 4 list menus and 1 submit that post the results to the results page. I can create a record set that either retrieves the correct data from my database if a selection is made from all four menus Or i can create the recordset if only 3 menus have a selection or the same for 2 menus and 1 menu. However i want the user to be able to make a selection from either 1, 2, 3 or all 4 of the menus and the exact data be retrieved. At present if i try to combine the recordset using AND and ORs the results are not specific enough, for example the 4 menus are Location, Type, Price & Style if a user selects from all 4 i only want to retrieve data that matches all 4 criteria, but at the same time if the user selects only 2 of the menus the i want it to retrieve data that matches specifically those 2 variables. I´m not actually sure if i should be creating a more advanced sql query of if its the php side of things that i need to look at. This is my first dynamic site so please be aware i´m still a learner where php and sql is concerned. Please can anyone help?

Report · Oct 21, 2009

Hiya,

I'm just doing my first dynamic site too, and am at a similar level to yourself.

Can you give us more info re the site. What software, eg Dreamweaver etc are you using, and is your server using PHP or ASP etc?

For what I've used, I amended the SQL side of things in the recordset in Dreamweaver. That way, you can test the SQL as you're setting up the recordset.

Let me know how you're going on anyway

Cheers

Andy

Report · Oct 21, 2009

Hey there,

Thanks for replying,

I too am using Dreamweaver recordset, my local server is XAMPP ( apache php mysql), i´have pasted my sql recordset below to give an idea of what i´m trying to do, however this does not work as i´m trying to select the exact data based on 4 menus PRICE TYPE LOCATION and BEDS, and also want the search to work if the user only selects options from either 1, 2, 3 or 4 of the menus, with the code as it is if the user select only two options from 2 of the menus the results don´t just find (for example) all results for location AND price they find all results for the location varibale OR the price variable rather than a match for both, if you see what i mean?

Any suggestions?

SELECT trueprice,`desc`, `propid`, `bathrooms`, `photo1`, locationtable.loc, typetable.style, bedtable.`number`

FROM detailstable JOIN locationtable ON detailstable.location=locationtable.locid JOIN typetable ON detailstable.type=typetable.typeid JOIN pricetable ON detailstable.price=pricetable.priceid JOIN bedtable ON detailstable.beds=bedtable.bedid

WHERE (location=varloc AND price = varprice AND type=vartype AND beds=varbed ) OR (price=varprice AND location=varloc AND type=vartype) OR (price=varprice AND location=varloc AND beds=varbed) OR (price=varprice AND beds=varbed AND type=vartype) OR ( location=varloc AND type=vartype AND beds=varbed) OR (price=varprice AND location=varloc) OR (price=varprice AND type=vartype) OR (price=varprice AND beds=varbed) OR (type=vartype AND location=varloc) OR (type=vartype AND beds=beds) OR (location=varloc AND beds=varbed) OR (price = varprice OR beds=varbed OR type=vartype OR location=varloc)

ORDER BY detailstable.trueprice ASC

Look forward to receiving your thoughts,

Linda

Date: Wed, 21 Oct 2009 14:36:33 -0600
From: forums@adobe.com
To: linda.barker7@hotmail.com
Subject: how to filter results from 4 dynamic list menus depandant on how many of them are selected
Hiya,

I'm just doing my first dynamic site too, and am at a similar level to yourself.

Can you give us more info re the site. What software, eg Dreamweaver etc are you using, and is your server using PHP or ASP etc?

For what I've used, I amended the SQL side of things in the recordset in Dreamweaver. That way, you can test the SQL as you're setting up the recordset.

Let me know how you're going on anyway

Cheers

Andy

>

Report · Oct 22, 2009

How is your database set up? Have you got 4 tables (PRICE, TYPE, LOCATION and BEDS) and one recordset in Dreamweaver, or have you got a recordset for each table?

Can you give me an example of how your database tables etc are set up and I'll try and set a similar one up and test it myself? From what I can see, the recordset info looks ok, but without an actual recordset to test, I can't work it out. Like I say, I'm a newbie to all this as well!

Cheers

Andy

Report · Oct 22, 2009

Hi Andy,

I really appreciate your taking time to help me, my tables are set up as follows:

I have 4 small tables as follows:

bedtable - has 2 colums: Column 1 is bedid (the id & primary key), Column 2 is number

typetable - has 2 colums : Column 1 is typeid (the id & primary key), Column 2 is style

locationtable - has 2 columns : Column 1 is locid (the id & primary key), Column 2 is loc

pricetable - has 2 columns : Column 1 is priceid (the id & primary key), Column 2 is price_ range

I then have detailstable: 8 Columns :

Column1 : propid (primary key & id)

Column2 : location - populated by the same data as the locid Col in locationtable(a join is created in my sql statement to link this column to the locid col in locationtable - my location table has ids for all the locations so that i don´t have to imput the whole word over and over in the details table and locid are the values i pass from the location menu), i have used the same idea in the next 3 columns.

Column3 : type - populated by the same data as typeid (same as above joined to typeid in typetable are typeid are the values passed from listmenu)

Column 4 : price - populated by same data as priceid (same as above)

Column 5 : beds - populated by same data as bedid (same as above)

Column 6: desc this a description to display on the results page dependant on criteria selected

Column 7: trueprice - the exact price of the properrty to display on the results page as price menu is a price Range

Column 8 : bathrooms - reflects number of bathrooms to display on results page

My menus as you have already noted are :

Location, Type, Price & Beds,

i can get results no problems but i only want the results to reflect which menus have been selected by the user and only to find properties with match ALL the options selected by the user not ANY - which is pretty much what i get now.

Have you sucessfully done something similar to this yourself? How amny recordsets did you use? I only have the one as you know?

so kind of you to help,

Linda

Report · Oct 22, 2009

Hiya Linda,

I'm doing a football site, and have a table for Matches (including Match ID, Opposition, Venue etc), a table for Players (Player ID, Player Name, Position) and a Stats Input Table which has the ID number from the Matches table, the ID number from the Players table, fields for Goals scored, Red cards, Yellow cards etc, plus a unique ID number for each row. I've got 3 record sets, one for each table.

I'm just telling you this as it seems a similar set up to mine, not just to bore you with what I'm doing!

Is the 'Exact Price' and 'Bathrooms' info supposed to show for each property once the results have been displayed? Ie, do you want this information to show for each property on the results page, regardless of what someone has chosen?

I'll try and set something up now. From what I can gather, are your results not being specific enough, e.g. it is showing properties which match anything that the user has selected, rather than showing A+B+C+D etc (if that makes sense?!)

Which version of Dreamweaver are you using?

Report · Oct 22, 2009

Hi Andy,

I am so pleased that you understand exactly what i am trying to do, yes i understand your tables and we are doing a similar search.

Yes the bathrooms and trueprice are just to go on the results page regardless of what people have choosen, also i have a description field and other fields that will also be displayed regardsless of what people have choosen, the only 4 search options are price (given as a range as in upto 100,000, between 100,000 and 250,000 etc...) , type, location and beds (number of bedrooms).

And again YES, my results are not specific enough, yes i do want A+B+C+D, but if a user does not select for example an option from the beds menu (D) that means that they want A+B+C and any number of bedrooms, or if they don´t select a location (A) that means they want all matches B+C+D in any location. You are so correct in saying that at the moment it retrieves properties based on ANY of the options selected by the User.

I´m using Dreamwaever CS3.

Thanks again for trying to help, i was up till 3am last night! and have been doing this 1 thing for weeks now.

I will look forward to hearing from you

Linda

Report · Oct 22, 2009

It's always the way! I spent about 3 days work out out to put a flash swf in a collapsible panel, then realised I could just insert an animated gif instead!

From what I've done so far..(this may not be right!), I think that you should have a separate recordset for each table, and your 'Results' page (or whatever it will be called) will pull Properties from the 'Details' table/recordset, depending on which values someone has input from the drop down menus.

I'm setting some things up at the moment, but I won't be able to do much more tonight. Is this for a personal project, or have you got a particular deadline? There's a good book at work that I used for most of my site, but I'll have to wait until tomorrow to get it, if you're ok hanging on?

Cheers

Andy

Report · Oct 22, 2009

Andy,

You are so kind helping me, i did try doing 4 recordsets but couldn´t make that work, sometimes you get so into something you just cannot see it.

I don´t have a deadline, this is a personal project, i have an Internet Cafe in Spain so spend much of my day on computers and am hoping to get into property promotion as an extra income due to recession an all, and as you are spending your time helping me then please take all the time you need. I really just hope that at some point i can return the favour. I saw a new post of yours earlier that i was unable to help with but you did post a link to your site which i had a look at, good job so far i have to say.

Thanks

Linda

Date: Thu, 22 Oct 2009 12:58:49 -0600
From: forums@adobe.com
To: linda.barker7@hotmail.com
Subject: how to filter results from 4 dynamic list menus depandant on how many of them are selected
It's always the way! I spent about 3 days work out out to put a flash swf in a collapsible panel, then realised I could just insert an animated gif instead!

From what I've done so far..(this may not be right!), I think that you should have a separate recordset for each table, and your 'Results' page (or whatever it will be called) will pull Properties from the 'Details' table/recordset, depending on which values someone has input from the drop down menus.

I'm setting some things up at the moment, but I won't be able to do much more tonight. Is this for a personal project, or have you got a particular deadline? There's a good book at work that I used for most of my site, but I'll have to wait until tomorrow to get it, if you're ok hanging on?

Cheers

Andy

>