I have a table user with normal user detail fields as name, country , city etc. Further on submit this form I have a set of question about user interest in particular technology. The user can answer zero , one or more than one answer. I am saving these answer in user_answers table with column - id , user_id , question_id and answer.
user table structure
id - int - auto increment
name - varchar
country - varchar
city - varchar
user_answer table structure
id - int - auto increment
user_id - int
question_id - int
answer - varchar
Now I have a task of export all user to excel sheet with the answer given by the user. For which I am trying to get user data with its given answer using minimum sql query. The data should be in array format below :
$data = Array ( [0] => Array ( [id] => 1 , [name] => abc , [city] => xyz ,
answer => Array( [0] => Array ( [question_id] => 1 , [answer] => aaa)
[1] => Array ( [question_id] => 3 , [answer] => bbb)
[2] => Array ( [question_id] => 4 , [answer] => ccc)
))
[1] => Array ( [id] => 2 , [name] => def, [city] => zxy,
answer => Array( [0] => Array ( [question_id] => 1 , [answer] => vvv)
[1] => Array ( [question_id] => 2 , [answer] => bbb)
))
[2] => Array ( [id] => 3 , [name] => ghi, [city] => xyz ,
answer => Array( [0] => Array ( [question_id] => 1 , [answer] => rrr)
[1] => Array ( [question_id] => 2 , [answer] => sss)
[2] => Array ( [question_id] => 3 , [answer] => ggg)
)) )
Can any one help me to achieve this result with minimum sql query. I have one option to first find all user then using another mysql query inside foreach to find the answer given by user corresponding to its user_id and push into the previous array result. But with this case a lot of query were executing for large number of users. So can any-one suggest any better solution.
