Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002
    Location
    Ohio
    Posts
    3,139

    Suggestions on getting nested data out of mysql 5

    Ok, so now my next project is creating a type of nested menu that displays data in a specific order, however I'm having difficulty getting it to work correctly. At the moment, I'm using a single database table in the task and I will likely be splitting it into a data table, and a category table.

    Currently it works fine when using just a single nested element, but when an item has more than 2 levels, it no longer works.

    First off, I'll start off by saying this is for a RPG and its a database of planetary systems, galaxies and so-called "superstrings" (or clusters of galaxies). I already have a number of static HTML pages of the data that I want to add into the database.

    Here's what I need to do. I want to have the "superstrings" listed in alphabetical order, then below a list of the galaxies in alphabetical order. And below the galaxy listing, yet another alphabetical list of the systems. Now here's where it gets complicated. Below the system list, I want to have a list of the planets in order of closest to the sun to the furthest. This data returns fine. However whenever I have any other bodies (space stations, planetoid, moon, etc) I want the bodiesy listed below the planet closest to furthest. This doesnt work with my current setup.

    Within the table, I have the following fields

    superstring name (bodyName)
    galaxy name (bodyGalaxy)
    system name (bodySystem)
    body name (bodyName)
    distance from its sun in AUs (bodyOrbit)
    parent's name (the body it orbits) (bodyParent)

    Each record has the data for everything in it. This table is not relational to any other table, so Earth's entry looks like... "Andromeda superstring, Milky Way Galaxy, Sol System, Earth"

    I've struggled with the mysql group by command, but I just cant seem to get the data out of mysql the right order. I'd much rather do this with mysql if at all efficiently possible since i think mysql would be better at hand for this.

    Now for some more links to my data set.
    The actual data: http://www.2thextreme.org/modules.ph...ta&table=space I have an exclusion setup where this data is visible, the other functions on that script are disabled.
    A map of the troublesome system: http://www.2thextreme.org/ADS_Node.gif
    Here is a larger page, and this is ultimately the order I want the data in, but in a format where if/when I add and delete stuff it automatically updates: http://mgcjerry.net/Isicander_Project/Mapping/

    Ultimately, the data will be displayed in a table very much like this where if a user clicks a link, it will load a page of all the data: http://www.2thextreme.org/modules.ph...ion=viewByRace
    A larger scope of the galaxies and superstrings... I don't have all the star systems digitized yet.
    http://www.2thextreme.org/Isicander_...erstrings.html

    I'm thinking I will have to at least create another table strictly for the categorization of the data, but its that extra layer that has me baffled and I'm just ultimately stupid for not being able to get this working correctly.

    So does anyone have a better way of handling this categorization? Thanks for any help.

    Heres an example of what I want:
    Code:
    Andromeda
        - Selena Galaxy
            + Adroth Wormhole
            + ADS Cygni System
                - ADS Cygni (star)
                - Jadel
                - Quizorthia
                - Noksaj/Meszaro
                + ADS Antares (star)
                    - Magmad <---------]
                    - Phayon <---------] These choke and show up in the wrong place
                    - Legas Minor <----] They show up in order with everything else
                
                - Helfin
                + ADS Ozar (star)
                    - Baumgart <-------]
                    - Celtzar <--------] These choke and show up in the wrong place
                    - Calitar <--------] They show up in order with everything else
                    
                - Flesholm
    
        - Milky Way Galaxy
            - Sol System <-- this system one works fine as-is
                - Sol (star)
                - Mercury
                - Venus
                - Earth
                - Mars
                - Jupiter
                - Saturn
                - Uranus
                - Neptune
                - Pluto
                - Athena
    
        - Grayban Galaxy
            + Fenal System
            
        - Ordon Galaxy
            + Cell System
    
    superstring
        galaxy
            system
                planet
                planet
                star
                    planet
                    planet
                        moon
                        space station
    
            system
                planet
                planet
                    space station
    
    etc
    etc
    etc
    Using the data above, here's what it shows.

    Code:
    Andromeda
        - Selena Galaxy
            + Adroth Wormhole
            + ADS Cygni System
                - ADS Cygni (star)
                - Magmad <--------- wrong place
                - Phayon <--------- wrong place
                - Jadel
                - Quizorthia
                - Noksaj/Meszaro
                - ADS Antares (star)
                - Celtzar <--------- wrong place
                - Calitar <--------- wrong place
                - Legas Minor <--------- wrong place
                - Helfin
                - ADS Ozar (star)
                - Baumgart  <--------- wrong place
                - Flesholm
    Last edited by MGCJerry; 09-23-2009 at 12:59 AM.

  2. #2
    Join Date
    Jan 2002
    Location
    Ohio
    Posts
    3,139
    After snatching someone else's code that only uses one query, I managed to get it to work only using one table.

    http://www.2thextreme.org/modules.ph...G&file=Testing

    As easy as that was, I'll go back into my corner now

  3. #3
    Join Date
    Aug 2005
    Location
    UK
    Posts
    654
    You've build that from one table and one query? I'm guessing a complicating sub query?

    Can you share the query? I was looking forward to peoples' responses.

  4. #4
    Join Date
    Jan 2002
    Location
    Ohio
    Posts
    3,139
    Sure...

    SELECT pid, bodyParent, bodyName, bodyType, bodyLink FROM space ORDER BY bodyOrbit, bodyName








    Most of the work ended up being in a php function. Its not perfect, but it works for my purposes. This is not my exact code, but its the base I worked off. This code snippet still utlizes a separate sql class, and choke() is a custom error reporting function.

    bodyParent fields with "0" (zeros) in them are considered "root" bodies.


    PHP Code:
        $sql "YOUR QUERY"
        
    $result $db->sql_query($sql)
            or 
    choke("Database Query"__LINE____FILE__$sql$db->sql_error($sql), "Database Query:");

        while (
    $listItem $db->sql_fetchrow($result)) {
            
    $listData['items'][$listItem['bodyName']] = $listItem;
            
    $listData['parents'][$listItem['bodyParent']][] = $listItem['bodyName'];
        }

        function 
    getListChildren($parentId$listData) {
            global 
    $linkURL;
            if (isset(
    $listData['parents'][$parentId])) {
                
    $output "<ul>";
                foreach (
    $listData['parents'][$parentId] as $itemId) {
                    
    $output .= "<li><a href=\"".$listData['items'][$itemId]['bodyLink']."\">" $listData['items'][$itemId]['bodyName']."</a>";
                    
    $output .= getListChildren($itemId$listData);
                    
    $output .= "</li>";
                }
                
    $output .= "</ul>";
            }
            return 
    $output;
        }

        echo 
    getListChildren(0$listData); 

Similar Threads

  1. Need some suggestions on a Mysql/data issue.
    By AdamD in forum Programming Discussion
    Replies: 8
    Last Post: 07-19-2009, 05:52 PM
  2. Suggestions for displaying MySQL data on external site...
    By jon31 in forum Programming Discussion
    Replies: 8
    Last Post: 04-18-2008, 04:16 AM
  3. Replies: 7
    Last Post: 11-04-2006, 06:31 PM
  4. Data Center Suggestions
    By wmghori in forum Running a Web Hosting Business
    Replies: 17
    Last Post: 03-17-2006, 04:20 PM
  5. nested mysql queries using C
    By Slidey in forum Programming Discussion
    Replies: 2
    Last Post: 01-20-2004, 07:24 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •