Ello again! Another question, this time about MYSQL (I really oughta find somewhere I can ask all these PHP, MYSQL and Javascript questions, LOL).
I'm working with a database table which looks like this:
rowid attribute value reference
---------------------------------------------------
1 make Peugeot 3
2 model 106 3
3 aircon no 3
4 make Citroen 7
5 model Saxo 7
6 aircon yes 7
It allows you to add other attributes later (say you wanted to update the site so it can sell boats too. You just add more attributes). These have a 'reference' value which points to the original listing ID. So these 6 rows point to just 2 cars, a Peugeot 106 (car #3) and a Citroen Saxo (car #7).
However, it's proving to be a nightmare when doing queries. For instance, I want to list off every model which is made by Citroen.
I would have tried doing this kind of logic in a search query:
Select the value of all rows
where attribute is 'model' and
where reference is one of the references of all rows whose attributes are 'make' and values are 'Citroen'.
I know that's a bad explanation, but it's basically two queries.
Find the reference # of every car of make 'Citroen'
Find the model of every reference # cited above.
This is my SQL so far:
SELECT `value`
FROM `field_data`
WHERE
`attribute` = 'model'
& `reference` = (
SELECT `reference` FROM `field_data`
WHERE `attribute` = 'make'
& `value` = 'Citroen'
)
Uhm. Help? It's making this link that's bothering me
Is there any reason why your table is set out like this? Just it would make more sense (and would be much easier to query) if you just had a table for vehicles such as:
id make model aircon
-------------------------------------
1 Peugeout 106 no
2 Citroen Saxo yes
Unless you've been told to set it out like you have done - then just ignore me
indeed. youre basically complicating the table and will end up doing more work to retrieve information. unless theres some specific reason that can only be accomplished by this format. if so im curious as to what.
you can add a column called "vehicletype" for reference, for site expansion. if you want to sell boats in the future then every new vehicle that is a boat is just called "boat" under the "vehicletype" column.
id vehicletype make model aircon
-------------------------------------
1 car Peugeout 106 no
2 car Citroen Saxo yes
edit: actually i see where you are going. using references you arent limited to a set table of values, but can create an infinite number of sub values for new vehicles.
Yeah, it's not my design choice; my friend/client is using a CMS which works this way. It actually is an effective way of doing it if (in the case of a CMS) you want it to be as customisable as possible. But I can foresee him having trouble if the site gets popular, cos the queries you have to use can get pretty huge.
Advertisement
Post Reply
Edited by an Administrator.
