On Tue, Jun 15, 2010 at 8:58 PM, Jan Reiter <the-fallen@xxxxxxx> wrote: > 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) This is known as an EAV (Entity-Attribute-Value) design. It is usually (some would say always) a very bad idea to implement this in a relational database. and this is no exception. > 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!!!!!! The easy solution is to redesign the tables. There are a lot of reasons why this design is usually a very bad idea. For starters, what should be a simple query is anything but simple, as you have just discovered. What's more, there is no simple way (if any way at all) for your design to prevent an image from having a mime-type of 20174 or a size of 'jpg'. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
