r_a_s_robin Posted October 15, 2008 Share Posted October 15, 2008 I'm having troubles ordering my table as a folder tree structure. Who can help me out here? (Assume i have the newest Mysql version. To keep the example easy to look at, the messages just say 'bla bla') I have the following table: IDMessagechild_from ------------------ 0bla blaNULL 1bla blaNULL 2bla bla0 3bla bla1 4bla bla1 5bla bla0 6bla blaNULL 7bla bla3 8bla bla4 9bla bla8 I'd like to order the table in such a way, that it would become like this: IDMessagechild_from ------------------ 0bla blaNULL 2bla bla0 5bla bla0 1bla blaNULL 3bla bla1 7bla bla3 4bla bla1 8bla bla4 9bla bla8 6bla blaNULL How do i do that with just one Query?? the goal is to create something like this: 0 bla bla -- 2 bla bla -- 5 blabla 1 bla bla -- 3 bla bla ---- 7 bla bla -- 4 bla bla ----8 bla bla ------9 blabla 6 bla bla Link to comment https://forums.phpfreaks.com/topic/128619-order-by-problem-like-to-order-it-as-a-folder-tree-structure/ Share on other sites More sharing options...
Mark1inLA Posted October 15, 2008 Share Posted October 15, 2008 I'm not sure if this is possible in MySQL alone... the way I'd approach this problem is by creating a recursive function in PHP that checks to see: A. if the message is a top level category. B. if there are children to that category. Just recently, I read about this very subject in a book that shows how to handle this situation. When i get home and if this problem is unresolved, ill share the example. Link to comment https://forums.phpfreaks.com/topic/128619-order-by-problem-like-to-order-it-as-a-folder-tree-structure/#findComment-666589 Share on other sites More sharing options...
r_a_s_robin Posted October 15, 2008 Author Share Posted October 15, 2008 Thanks for your reply. I think i know how to manage this with php though. (2 for loops). I'm really looking for a solution with mysql Link to comment https://forums.phpfreaks.com/topic/128619-order-by-problem-like-to-order-it-as-a-folder-tree-structure/#findComment-666595 Share on other sites More sharing options...
fenway Posted October 16, 2008 Share Posted October 16, 2008 With a few JOINs, that shouldn't be too hard... but what are you going to order by? Don't say UID... Link to comment https://forums.phpfreaks.com/topic/128619-order-by-problem-like-to-order-it-as-a-folder-tree-structure/#findComment-667123 Share on other sites More sharing options...
r_a_s_robin Posted October 16, 2008 Author Share Posted October 16, 2008 well, actually i'm ordering by something that isn't really there. As you can see i started by taking the lowest ID without a 'child_from'. Then there is the lowest ID row with child_from='0' (this happens to be ID=2), Then, because there is no child_from='2', it searches for child_from='0' again, etc etc Because i'm ordering by something that isn't there, i've been thinking about (and trying to make) a additional column with AS in the mysql statement. But i haven't succeeded so far and i'm not sure it's the way to do it. Link to comment https://forums.phpfreaks.com/topic/128619-order-by-problem-like-to-order-it-as-a-folder-tree-structure/#findComment-667157 Share on other sites More sharing options...
fenway Posted October 16, 2008 Share Posted October 16, 2008 Yeah, that's almost impossible to do... usually, I have a sortorder within each level of category... so a simple order by clauses suffices. Link to comment https://forums.phpfreaks.com/topic/128619-order-by-problem-like-to-order-it-as-a-folder-tree-structure/#findComment-667225 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.