Am 16.06.2010 02:58, schrieb Jan Reiter:
Hi folks!
I'm kind of ashamed to ask a question, as I haven't followed this list very
much lately.
This isn't exactly a PHP question, but since mysql is the most popular
database engine used with php, I figured someone here might have an idea.
I have 2 tables. Table A containing 2 fields. A user ID and a picture ID =>
A(uid,pid) and another table B, containing 3 fields. The picture ID, an
attribute ID and a value for that attribute => B(pid,aid,value).
Table B contains several rows for a single PID with various AIDs and values.
Each AID is unique to a PID. (e.g. AID = 1 always holding the value for the
image size and AID = 3 always holding a value for the image type)
The goal is now to join table A on table B using pid, and selecting the rows
based on MULTIPLE attributes.
So the result should only contain rows for images, that relate to an
attribute ID = 1 (size) that is bigger than 100 AND!!!!!!! an attribute ID =
5 that equals 'jpg'.
I know that there is an easy solution to this, doing it in one query and I
have the feeling, that I can almost touch it with my fingertips in my mind,
but I can't go that final step, if you know what I mean. AND THAT DRIVES ME
CRAZY!!!!!!
I appreciate your thoughts on this.
Regards,
Jan
Hi Jan,
i think the problem here is the architecture of your database and not
the sql queries. Sure, there are a whay to solve this, but i think it is
better (for the software maintainment and further work in your code),
that you take 5 minutes and redesign your tables. In my opinion, your
attribute,value pair table, should be a extern table with relation to
table b. As for know, i dont know what you want to do, but i think, when
the user have more than one picture with more attributes each picture,
your query will be slow. By increasing the attributes count, you will
show, the queries and the maintainment of code will be hard.
I have some links here but i am sure, you will find more in google ;-)
Regards
Carlos
Links:
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html
http://en.wikipedia.org/wiki/Entity-attribute-value_model
http://weblogs.sqlteam.com/davidm/articles/12117.aspx
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
