The Daily Click ::. Forums ::. Non-Klik Coding Help ::. MYSQL Question
 

Post Reply  Post Oekaki 
 

Posted By Message

DeadmanDines

Best Article Writer

Registered
  27/04/2006
Points
  4758
1st November, 2008 at 00:05:35 -

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

 
191 / 9999 * 7 + 191 * 7

DeadmanDines

Best Article Writer

Registered
  27/04/2006
Points
  4758
3rd November, 2008 at 11:18:56 -

Nevermind, I've found it. The MYSQL functions I needed were IN() or ANY().

 
191 / 9999 * 7 + 191 * 7

Flava



Registered
  30/07/2002
Points
  684

Has Donated, Thank You!Code MonkeyVIP MemberThe Cake is a LieThe Spinster
3rd November, 2008 at 12:05:22 -

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




Image Edited by an Administrator.

 
This is a signature. Have this one on me.

Cecilectomy

noPE

Registered
  19/03/2005
Points
  305

Has Donated, Thank You!VIP MemberWeekly Picture Me This Winner!Cardboard BoxGhostbuster!Pokemon Ball!ComputerBox RedSanta HatSnowman
I am an April Fool
3rd November, 2008 at 12:24:29 -

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.

Image Edited by the Author.

 
n/a

DeadmanDines

Best Article Writer

Registered
  27/04/2006
Points
  4758
4th November, 2008 at 12:39:01 -

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.

But at least it's finally all coming together!

 
191 / 9999 * 7 + 191 * 7
   

Post Reply



 



Advertisement

Worth A Click