So what i have is pretty simple. I have 1 table
CategorieMain
like
CatMainid
CatMainName
and 1 table:
CategorieSub
like
CatId
CatName
CatMainId
'
What i want is :
A list with all the CatMainName's and under the names all the CatSubNames.
CatMainId1
all CatSubNames with CatMainId1
CatMainId2
all CatSubNames with CatMainId2
Etc Etc
Currently i use php to get the data like
SELECT * from Categoriemain
and in the while loop i do
SELECT * FROM CategorieSub WHERE CatMain id = $row['CatMainId']
But this is very inefficient because now if i have 10 CatMainId's i do 10 query's (for each one a while)
What is the most efficient way to get a list like this, i was thinking about putting it in arrays or something but i couldn't get it working?
Use one query:
SELECT Categoriemain.*, CategorieSub.CatSubNames FROM Categoriemain
JOIN CategorieSub ON Categoriemain.CatMainid=CategorieSub.Catmainid
Edited, removed GROUP BY
With this query, you don't need the while loop.
I have also added the CatSubNames to the output list of the query fields.
SELECT * FROM Categoriemain
JOIN CategorieSub ON Categoriemain.CatMainid=CategorieSub.Catmainid
ORDER BY Categoriemain.CatMainid