I have a very basic one table scenario;
tblFamiles
entryID : Int
name: Text
parentID: Int
Lets add the following rows;
entryID : name : parentID
1 : Grandmother Jones : 0
2 : Grandmother Thompson : 0
3 : Mother Jones : 1
4 : Mother Thompson : 2
5 : 1st Daughter Jones : 3
6 : 2nd Daughter Jones : 3
7 : 1st Daughter Thompson : 4
Here we have three generations of two familes stored, the Jones family and the Thompson family (as an exampe). I would like to query this table but order the results by the parentID (but not just like plain old ORDER BY 'parentID' DESC, so that they are in relative order. I would like an output like this;
SELECT (SOME MAGIC) FROM `tblFamiles`;
entryID : name : parentID
1 : Grandmother Jones : 0
3 : Mother Jones : 1
5 : 1st Daughter Jones : 3
6 : 2nd Daughter Jones : 3
2 : Grandmother Thompson : 0
4 : Mother Thompson : 2
7 : 1st Daughter Thompson : 4
Logically, the only way I can see how to do this is to loop through all entryIDs and then for each entryID; loop through all other records checking their parentID fields against the current entryID, and bringing those records to the top of the result set, under the current row. But I can't see how to do that in MySQL.
UPDATE
I have used families as an example above, but what I am after is a method of storing nested entries and getting them in a single query, for efficiency really. I could just make multiple SELECT queries but that would be it ugly;
(Pseudo)
SELECT entryID, name WHERE parentID = 0 LIMIT 0,1;
print name;
Sub query:
SELECT entryID, name WHERE parentID = $above-entryID
print name;
(Keep looping through this till the second query returns no results,
then go back to the first query and move onto the next entryID)
2nd UPDATE
You can forget the name column even exists, I just used that as an example, all that matters here is entryID and parentID as these are the two columns that link and control everything. There could be twenty extra columns as well as name, but they all revolve around entryID and parentID, it's just linked or nested (which term ever is more appropriate) IDs.
