BenWeston Posted February 27, 2014 Share Posted February 27, 2014 (edited) Hi all I have an InnoDB database with three tables – users, treatments and treatments_offered. The users table, predictably, stores user information with an ID for each. The treatments table stores a list of treatments with an ID for each and the treatments_offered table just stores the ID of a user and an ID of a treatment that they offer. Some users may only offer one treatment, some may offer 20+. Foreign keys are in place to make sure all this stays in sync. My question is what is the quickest MySQL query I can run (via PHP but I'm not sure that actually matters), to select and list all the treatments provided by a user of ID, say, 3? Is there one query I can run or do I have to run several separate ones like this: $treatment_ids = mysql_fetch_array(mysql_query("SELECT treatment_id FROM treatments_offered WHERE user_id = '$user_id';"), MYSQL_ASSOC); $treatments = array(); foreach ($treatment_ids as $treatment_id) { $temp = mysql_fetch_array(mysql_query("SELECT treatment FROM treatments WHERE id = '$treatment_id';"), MYSQL_ASSOC); $treatments[] = $temp['treatment']; } That seems VERY inefficient to me – I think I'm missing something! Edited February 27, 2014 by BenWeston Quote Link to comment https://forums.phpfreaks.com/topic/286578-most-efficient-way-to-select-from-several-tables/ Share on other sites More sharing options...
mac_gyver Posted February 27, 2014 Share Posted February 27, 2014 you would need to use a JOIN'ed query to retrieve related information using one query. Quote Link to comment https://forums.phpfreaks.com/topic/286578-most-efficient-way-to-select-from-several-tables/#findComment-1470912 Share on other sites More sharing options...
BenWeston Posted February 27, 2014 Author Share Posted February 27, 2014 (edited) Great, thanks! So, perhaps, something like this (if I'm interpreting the JOIN documentation properly)? SELECT treatment FROM treatments JOIN treatments_offered ON (treatments_offered.treatment_id = treatments.id) WHERE treatments_offered.user_id = '$user_id'; ...as the users table will actually be redundant, seeing as the user's ID has a foreign key to user_id in treatments_offered? Edited February 27, 2014 by BenWeston Quote Link to comment https://forums.phpfreaks.com/topic/286578-most-efficient-way-to-select-from-several-tables/#findComment-1470913 Share on other sites More sharing options...
Solution BenWeston Posted February 27, 2014 Author Solution Share Posted February 27, 2014 For the benefit of anyone else Googling, I can confirm the above SQL query DOES work. Very helpful article on JOINs at the following link: http://www.sitepoint.com/understanding-sql-joins-mysql-database/ Quote Link to comment https://forums.phpfreaks.com/topic/286578-most-efficient-way-to-select-from-several-tables/#findComment-1470917 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.