powerpants Posted February 27, 2009 Share Posted February 27, 2009 Hi everyone, I'm trying to build a little web app that returns images from my database based on sorting constraints selected by the user from some pull down html menus. Here's my table layout...... I've got a table called 'image' which contains relevant issue to images stored on the server. Each record in 'image' has attributes 'image_id,' (primary key) 'batch_id,' 'photographer_id,' and 'image_path,' among others. I have another table called 'person,' which includes primary key 'person_id.' I have a third table called 'image_person' wherein each record has two attributes: 'image_id' and 'person_id,' which are foreign key'd to the 'image' and 'person' tables, respectively. This table is how I keep track of the multiple people in each image. On the web interface, I want the user to be able to select images from a "Batch," "Photographer," and "Person" drop down menus. Obviously, this would be very easy if I only wanted to choose "Batch" and "Photographer," as they are found in the same table as 'image_id' and 'image_path,' but adding the "Person" part is kind of throwing me for a loop. Is there some way to first query the 'image_person' table with a 'person_id' retrieved from the drop down, and then use the resulting set of 'image_id's to immediately SELECT FROM image WHERE image_id=(the ones i got from image_person table) && batch_id=(from drop-down) && photographer_id=(from drop-down)? Or is there a better way of doing this? This is my first mySQL project, and I'm kind of learning as I go along, so feel free to assume that I know nothing when formulating your responses to me....... Thanks very much for your help, this is driving me crazy! Quote Link to comment https://forums.phpfreaks.com/topic/147208-solved-best-way-to-sort-based-on-multiple-tables/ Share on other sites More sharing options...
fenway Posted February 28, 2009 Share Posted February 28, 2009 TLDR... can you summarize? Quote Link to comment https://forums.phpfreaks.com/topic/147208-solved-best-way-to-sort-based-on-multiple-tables/#findComment-773307 Share on other sites More sharing options...
powerpants Posted March 1, 2009 Author Share Posted March 1, 2009 Turns out what I was describing was a JOIN. I JOIN'd the tables that I wanted to query based on common image_id's. I'm learning as I go along..... Quote Link to comment https://forums.phpfreaks.com/topic/147208-solved-best-way-to-sort-based-on-multiple-tables/#findComment-774214 Share on other sites More sharing options...
fenway Posted March 2, 2009 Share Posted March 2, 2009 So, solved? Quote Link to comment https://forums.phpfreaks.com/topic/147208-solved-best-way-to-sort-based-on-multiple-tables/#findComment-774410 Share on other sites More sharing options...
powerpants Posted March 2, 2009 Author Share Posted March 2, 2009 Oh, sorry, I forgot.... (: Quote Link to comment https://forums.phpfreaks.com/topic/147208-solved-best-way-to-sort-based-on-multiple-tables/#findComment-774432 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.