[thelist] echoing col field names based on field values (php/mySQL)

[Matt Warden]

On 6/29/05, Jeremy Weiss <jweiss at cox-internet.com> wrote:
> I've got a database with a couple tables in it. One of the tables contains
> basic info on condos. Another table is for all the 'features' the condo has.
> The PK field is that of the condo table. The reason I stuck the features in
> a separate table is b/c there's a good chance for more features to be added
> on a continuing basis and it seemed like a good way to automate things so
> that I'm not having to rewrite the queries later.  The fields are things
> such as:
> Refrigerator
> Microwave
> Cable TV
> etc...
> 
> The possible values for these fields is '0' and '1'. What I want to happen

Jeremy,

Are you able to change the design? You're heading down a road less
traveled, and less traveled for a reason.

Consider this type of a design:

CONDO
------------------------
condo_id          (PK)
...etc...

FEATURE
------------------------
feature_id        (PK)
featurename

CONDO_FEATURE
------------------------
condo_id          (PK)
feature_id        (PK)

Or, if that's a little too complex for you, you can simplify it, at
the expense of possibly ending up with feature that are the same but
typed differently (e.g. "Cable TV" and "CableTV" and "Cable
Television" -- this makes it hard to query all condos that have cable
television), depending on how your application is setup:


CONDO
------------------------
condo_id          (PK)
...etc...

CONDOFEATURE
------------------------
condo_id          (PK)
featurename   (PK)


Now, instead of a 0 indicating no feature and a 1 indicating a
feature, you have a matching tuple in the CONDO_FEATURE or
CONDOFEATURE (pardon my personal naming conventions) indicating the
feature for that condo and the lack thereof indicating the lack
thereof (kind of makes sense, eh?).

I personally have a contraption set up to crack a ruler on my knuckles
when I try to design a data model that would require altering the
table structure when new data (of the same type as is already
represented in the database) is added. I can send you the schematics,
if you would like.

Hope this helps. Let us/me know if you need any assistance in
formulating any join queries between the condos and their respective
features.

-- 
Matt Warden
Miami University
Oxford, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.