hello I have two tables name university and industry. university will have many Industries and Industry will have many universities. So in order to implement this many to many relation I have created another table named as university_industry. It has ids of both university and industry as uni_id and ind_id . And I have a student table which has uni_id as the foreign id. So I want to get all those universities which is in student table but also with all the industries of those particular universities as well. This is the query I have done
SELECT university . * , industry . *
FROM university
JOIN university_industry ON university_industry.uni_id = university.uni_id
JOIN industry ON industry.ind_id = university_industry.ind_id
WHERE university.uni_id IN (select uni_id from student where uni_id IS NOT NULL)
The problem with this query is I get the results like this
Array
(
[0] => Array
(
[university] => Array
(
[uni_id] => 1
[uni_name] => NYC Uni
)
[industry] => Array
(
[ind_id] => 1
[ind_name] => Finance
)
)
[1] => Array
(
[university] => Array
(
[uni_id] => 1
[uni_name] => NYC Uni
)
[industry] => Array
(
[ind_id] => 2
[ind_name] => Accounting
)
)
)
I want the result something like this
(
[0] => Array
(
[University] => Array
(
[uni_id] => 1
[uni_name] => NYU
)
[Industry] => Array
(
[0] => Array
(
[ind_id] => 1
[ind_name] => Finance
[UniversityIndustry] => Array
(
[id] => 1
[uni_id] => 1
[ind_id] => 1
)
)
[1] => Array
(
[ind_id] => 2
[ind_name] => Accounting
[UniversityIndustry] => Array
(
[id] => 2
[uni_id] => 1
[ind_id] => 2
)
)
)
)
So like I need all the industries in single index of array. So like it should be like this
1.NYC Uni
->accounting
->finance
instead of
NYC Uni
->accounting
NYC Uni
->finance
I hope you understand my problem. I am working on Php and I have no idea that wether I can achieve this certain result doing php code or I can achieve directly from the query.
You cannot achieve this from a single query as it always returns the data in tabular format. You must have to convert your data set in the code according to the format that you need