jecker Posted November 9, 2014 Share Posted November 9, 2014 I am currently working on a project and I am wonder if there is a better method for accomplishing the same result. I have three mysql tables Area (idArea, AreaName), Question (idQuestion, fkArea, QuestionName), and Responses (fkArea, fkQuestion, responseDate, userResponse). I would like to combine all three tables with the following results: group all responses by fkArea, responseDate, and fkQuestion, but I need to list every question for every date and every area. Currently, I am using several nested 'for loops', is there a better method? Quote Link to comment https://forums.phpfreaks.com/topic/292375-looking-for-a-better-way/ Share on other sites More sharing options...
Barand Posted November 9, 2014 Share Posted November 9, 2014 Use a single query with JOINs, then you only need a single loop to process the results SELECT a.AreaName , r.responseDate , q.QuestionName , r.userResponse FROM Area a INNER JOIN Question q ON a.idArea = q.fkArea LEFT JOIN Responses r ON q.idQuestion = r.fkQuestion ORDER BY a.AreaName, r.reponseDate, q.QuestionName I used a LEFT JOIN on reponses in case there are questions with no responses. Quote Link to comment https://forums.phpfreaks.com/topic/292375-looking-for-a-better-way/#findComment-1496178 Share on other sites More sharing options...
jecker Posted November 9, 2014 Author Share Posted November 9, 2014 Thank you for your response, but this does not produce the result I am looking for. I need to generate a page that list all of the questions and their corresponding responses, if available, and grouped by date. Example: If the user has selected Nov 7, 2014 through Nov 9, 2014 the list should display Nov 7, all of the questions, and the corresponding responses for that day, Nov 8, all of the questions, and the corresponding responses for that day, and Nov 9, all of the questions, and the corresponding responses for that day. Quote Link to comment https://forums.phpfreaks.com/topic/292375-looking-for-a-better-way/#findComment-1496181 Share on other sites More sharing options...
Barand Posted November 9, 2014 Share Posted November 9, 2014 My apologies for my shortcomings in the psychic telepathy department Quote Link to comment https://forums.phpfreaks.com/topic/292375-looking-for-a-better-way/#findComment-1496199 Share on other sites More sharing options...
NotionCommotion Posted November 27, 2014 Share Posted November 27, 2014 My apologies for my shortcomings in the psychic telepathy department You better work on that Quote Link to comment https://forums.phpfreaks.com/topic/292375-looking-for-a-better-way/#findComment-1497843 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.