Skip to main content
C
cdsql
Participant
March 8, 2010
Question

dreamweaver recordset query help!

  • March 8, 2010
  • 2 replies
  • 1329 views

HI im building a search page on dreamweaver based on bars clubs and pubs. At the moment i have 3 checkboxes labelled Bar, Club and pub with list/menu. I want the checkboxes so that when 1 is clicked eg Club it will come up with all the clubs in the database. However at the moment when i click that it comes up with the right amount of records but with clubs, bars and pubs.

Also i want it so that if i select Clubs and Pubs in the checkboxes, or all 3 checkboxes selected, it will come up with all the Clubs and Pubs in the database and so forth.

This is the SQL query in the recordset i created to get the values from the search page. I think this is wherte its going wrong


SELECT *
FROM `places`
WHERE `Town` LIKE %area% AND `MusicGenre` LIKE %music% AND (`DisabledAcess` = 'Yes' OR da1 ='0' ) AND (`Club/Bar/Pub` = 'Club' OR club1 = '0') AND (`Club/Bar/Pub` = 'Pub' OR pub1 = '0') AND (`Club/Bar/Pub` = 'Bar' OR bar1 = '0')
ORDER BY `Name` ASC


Help Please!

This topic has been closed for replies.

2 replies

D
Dan_Bracuk
Inspiring
March 24, 2010

First, your business logic has to provide for the user not checking any of the boxes.  But that's a separate issue so I'll say no more on the topic.

On your form, give all the checkboxes the same name, but different values.

In your query, your where clause will include something like

and somefield in (#form.checkboxfield#)

S
Scott_thornton
Known Participant
March 24, 2010

Checkbox's won't exist on the submitted page unless they are ticked.

so i might do something like: ( untested )

SELECT

   *
FROM `places`
WHERE

`Town` LIKE %area%

AND `MusicGenre` LIKE %music%

AND (`DisabledAcess` = 'Yes' OR da1 ='0' )

<cfif isdefined("form.club)" OR isdefined("form.pub)" OR isdefined("form.bar)">

AND

(

   <cfif isdefined("form.club)">

           `Club/Bar/Pub` = 'Club'  OR

   </CFIF>

   <cfif isdefined("form.PUB)">

           `Club/Bar/Pub` = 'pub'  OR

   </CFIF>

   <cfif isdefined("form.BAR)">

           `Club/Bar/Pub` = 'bar'  OR

   </CFIF>

   1 = 2

)

</cfif>

ORDER BY

`Name` ASC

PS. Its a bad idea to name a database field something like `Club/Bar/Pub` ( but i assume that just an example, and not real)

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices