Hi,
I'm currently learning the ropes with CSS, PHP, MYSQL, Javascript etc...
by redesigning my website as a database driven site.
Okay, so I'm skilled at perl, data manipulation and data analysis and
I've done a fair bit of (non CSS) HTML in the past and know what a
relational database is. However, I'm having problems trying to formulate
a particular MYSQL query (maybe I just don't know the lingo or maybe
I've designed my data incorrectly) and wonder if anyone can shed some
light for me.
Basically, I'm trying to put together a retailers list on my site that
is classified according to the types of products that they sell. The
product list is hierarchically structured (not a flat list) so e.g. one
portion of my retailer type structure looks something like this:
Telecoms:
Mobiles:
Contract
PAYG
SIM Free
Landline:
Services
Handsets
Broadband:
Services
Hardware
Now, I intend to have a menu system on my website which exactly mirrors
my retailer type structure that allows the user to look up a list of
retailers based on the type of product/service that they are after.
However, if the user selects a menu item that relates to an incompletely
specified type (e.g. "telecoms" or "telecoms.mobiles" as opposed to
"telecoms.mobiles.contract") I would like my site to list the superset
of companies that the user's selection relates to e.g. ALL telecoms
related companies or ALL mobile related companies.
Currently, my MYSQL table for retailers simply has a flat list of
(binary valued) ENUM keys, one for each fully-specified LEAF on my
retailer type tree. However I have named these variables in a consistent
manner thus
RMTelMobContract
RMTelMobPAYG
RMTelMobSimFree
RMTelLandService
RMTelLandHandset
RMTelBBService
RMTelBBHardware
is the subset of ENUM keys associated with the different types of
telecoms related retailers in the above example.
I was hoping to be able to say, "if I ask for RMTelMob companies, please
return any records where RMTelMob* is 'Y'" where RMTelMob* implies any
key that begins with "RMTelMob".
I know that I can just create extra ENUM keys for each branch in the
tree but I wondered if there was a way of effectively calculating this
on the fly instead, relying on the fact that I adhere to a consistent
naming structure - in essence it looks like I want to find ALL keys that
are extensions of the more general key and then select the record if at
least one of them is marked as true.
Alternatively, is there a better way of designing the data in this table
to make it (a) more consistent with the relational paradigm (b) easier
to query in this manner (I guess (b) would follow from (a) in any case)?
cheers,
RM