Those damned checkboxes

Get Started
1

As always I have problems with dynamic checkboxes on a update page. There are three tables involved (properties, amenities and property_amenities). The amenities for each property, as you can guess, are stored in the table property_amenities


CREATE TABLE IF NOT EXISTS `property_amenities` (
  `property_id` smallint(4) unsigned NOT NULL default '0',
  `amenity_id` smallint(4) unsigned NOT NULL default '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I can’t get my head around it how to query and output this in the update page, since I need all amenity values from the amenities table (in order for the administrator to update) and I need the the values from property_amenities table since that are the ones that needs to be checked.

I tried it with two separate queries one for all amenities and one for a particular property


SELECT
    amenity_id
  , amenity_gr
FROM
    amenities
ORDER 
  BY
    amenity_gr

SELECT 
    A.amenity_id
  , A.amenity_gr
  , P.amenity_id
  , P.property_id        
FROM
    amenities A
INNER
  JOIN property_amenities P
  ON
    P.amenity_id = A.amenity_id
WHERE
    P.property_id = Url.property

and have a within the 


  <cfoutput query="getAmenities">
    <li><input type="checkbox" name="amenity_id" value="#amenity_id#" <cfif getPropertyAmenities.amenity_id EQ
getAmenities.amenity_id>checked</cfif>>#amenity_gr#</li>
  </cfoutput>

But this returns just one checkbox checked

Any suggestions?

2 
SELECT a.amenity_id
     , a.amenity_gr
     , p.amenity_id AS got
  FROM amenities AS a
[B][COLOR="Blue"]LEFT OUTER[/COLOR][/B]
  JOIN property_amenities AS p
    ON p.amenity_id = a.amenity_id
   [B][COLOR="blue"]AND[/COLOR][/B] r.property_id = #Url.property#
ORDER 
    BY a.amenity_gr

then <CFIF LEN(got)> will be 0 if the property does not have that amenity

3

… thanks a lot rudy :slight_smile: One last question about this, or better some advise how you would do this.

On the update_amenities page how should I handle the update? I was thinking of first deleting the old values and use an insert for the new ones or is this not a good idea?

4

no, that’s actually the easiest way to do it

:slight_smile: