Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    New Coder
    Join Date
    Aug 2010
    Posts
    89
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Post Returning XML from mySQL to Populate a Tree Structure

    I have a image table in my db.

    I want my script to check that that a user is registered, if so return the image categories they are allowed access to in XML, can this be done?

    e.g XML like this to populate a data Tree

    Code:
    <folder label="Sports">
     <folder label="Football">
          <jpg label="Kits"/>
       </folder> 
       <folder label="Cricket">
        <jpg label="Bats" />
       </folder>
    <folder label="Nature">
       <folder label="Animals">
          <jpg label="Birds"/>
       </folder>
    </folder>
    </folder>

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Short answer, yes.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #3
    New Coder
    Join Date
    Aug 2010
    Posts
    89
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Post

    Can anyone explain how I return the XML for a generic number of menu items then?

  • #4
    Super Moderator
    Join Date
    Feb 2009
    Location
    England
    Posts
    539
    Thanks
    8
    Thanked 63 Times in 54 Posts
    To be able to give you vaguely understandable code, we need to know roughly how your tables are laid out, but basically you'll probably be using recursive calls:

    PHP Code:
    function getXML($parent) {
        
    $rows get_database_rows_with_parent($parent);
        
    $result '<folder>';
        foreach(
    $rows as $row) {
            if (
    row_is_folder) {
                
    $result .= getXML(row_parent);
            } else {
                
    $result .= '<jpg label="'.file_label.'" />';
            }
        }
        
    $result .= '</folder>';
        return 
    $result;

    Or something like that.
    lamped.co.uk :: Design, Development & Hosting
    marcgray.co.uk :: Technical blog

  • #5
    New Coder
    Join Date
    Aug 2010
    Posts
    89
    Thanks
    3
    Thanked 0 Times in 0 Posts
    So in my db how would i store the structure?

    store the file_label, and whether it is a folder or a file?

  • #6
    Super Moderator
    Join Date
    Feb 2009
    Location
    England
    Posts
    539
    Thanks
    8
    Thanked 63 Times in 54 Posts
    Quote Originally Posted by flexillu View Post
    So in my db how would i store the structure?

    store the file_label, and whether it is a folder or a file?
    Assuming label is different to file name:

    id int
    name varchar
    label varchar
    parentid int
    type enum(folder, file)

    parentid specifies which folder the current file or folder resides in. Setting parentid to a record with type = file would be invalid.
    lamped.co.uk :: Design, Development & Hosting
    marcgray.co.uk :: Technical blog

  • #7
    New Coder
    Join Date
    Aug 2010
    Posts
    89
    Thanks
    3
    Thanked 0 Times in 0 Posts
    hmmm i nearly get it, so close!

    If i call the function with the parent "root" it gets stuck in a loop, because root is a folder but has no parent.

    What parameter would i pass to the function to start it off.

    By the way thanks for oyur help, your awesome. Been stuck on this for so long its untrue.

  • #8
    Super Moderator
    Join Date
    Feb 2009
    Location
    England
    Posts
    539
    Thanks
    8
    Thanked 63 Times in 54 Posts
    Quote Originally Posted by flexillu View Post
    hmmm i nearly get it, so close!

    If i call the function with the parent "root" it gets stuck in a loop, because root is a folder but has no parent.

    What parameter would i pass to the function to start it off.

    By the way thanks for oyur help, your awesome. Been stuck on this for so long its untrue.
    Not a problem

    Typically I use -1 as the parentid for root elements in a structure such as this, so you'd call it with -1.
    lamped.co.uk :: Design, Development & Hosting
    marcgray.co.uk :: Technical blog

  • #9
    New Coder
    Join Date
    Aug 2010
    Posts
    89
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Still can't get my head round this sorry.

    If i call the function with -1 or any parent id i get the following error

    Warning: Invalid argument supplied for foreach() in C:\wamp\www\xmlphp.php on line 22

    My Code
    PHP Code:
    <?php
            $hostname_conn 
    "****";
        
    $username_conn "****";
        
    $password_conn "****";

        
    $conn mysql_connect($hostname_conn$username_conn$password_conn);
       
       
    //connect to the database
       
       
       
    mysql_select_db("*****");
       
       
    getXML(-1);
       
       function 
    getXML($parent) {
        
    //$rows = get_database_rows_with_parent($parent);
        
    $rows mysql_query("SELECT * FROM menuitems WHERE parent_id = $parent");
        
    $result '<folder>';
        echo 
    $parent;
        foreach(
    $rows as $row) {
            if (
    file_type == 'folder') {
                
    $result .= getXML(row_parent);
            } else {
                
    $result .= '<jpg label="'.file_label.'" />';
            }
        }
        
    $result .= '</folder>';
        return 
    $result;
        }

    ?>
    At the moment in my db i have

    id, file_label, parent_id, file_type

    3, root, -1, folder
    5,Sports,3,folder
    6,Football,5,file

    Where am i going wrong?
    Last edited by flexillu; 12-01-2010 at 02:17 PM. Reason: changed a bit

  • #10
    Super Moderator
    Join Date
    Feb 2009
    Location
    England
    Posts
    539
    Thanks
    8
    Thanked 63 Times in 54 Posts
    PHP Code:
        $res mysql_query("SELECT * FROM menuitems WHERE parent_id = $parent");
        
    $result '<folder>';
        echo 
    $parent;
        while(
    $row mysql_fetch_assoc($res)) { 
    Pseudo code is pseudo code and not to be taken too literally
    lamped.co.uk :: Design, Development & Hosting
    marcgray.co.uk :: Technical blog

  • #11
    New Coder
    Join Date
    Aug 2010
    Posts
    89
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Still no clue.

    This is my first php script so i really need you to spell out what i need to do if that's ok. Here is what i've got so far, not getting any output.

    PHP Code:
    <?php
            $hostname_conn 
    "888";
        
    $username_conn "888";
        
    $password_conn "888";

        
    $conn mysql_connect($hostname_conn$username_conn$password_conn);
       
       
    //connect to the database
       
       
       
    mysql_select_db("testimages2");
       
       
    getXML(-1);
       
     
        
        function 
    getXML($parent) {
        
    $res mysql_query("SELECT * FROM menuitems WHERE parent_id = $parent");
        
    //$rows= mysql_fetch_assoc($rows);
        
    $result '<folder>';
        
        
        while(
    $row mysql_fetch_assoc($res)){
        
            if (
    "folder_type" == 'folder') {
                
    $result .= getXML(row_parent);
            
            } else {
                
    $result .= '<jpg label="'."file_label".'" />';
            
        }
        }
        
    $result .= '</folder>';
        
        print 
    $result;
        }
        
       
    ?>

  • #12
    Super Moderator
    Join Date
    Feb 2009
    Location
    England
    Posts
    539
    Thanks
    8
    Thanked 63 Times in 54 Posts
    PHP Code:
    <?php
            $hostname_conn 
    "888";
        
    $username_conn "888";
        
    $password_conn "888";

        
    $conn mysql_connect($hostname_conn$username_conn$password_conn);
       
       
    //connect to the database
       
       
       
    mysql_select_db("testimages2");
       
       print 
    getXML(-1); // print out the end result of getXML, not in the middle of it
       
     
        
        
    function getXML($parent) {
        
    $res mysql_query("SELECT * FROM menuitems WHERE parent_id = $parent");
        
    //$rows= mysql_fetch_assoc($rows);
        
    $result '<folder>';
        
        
        while(
    $row mysql_fetch_assoc($res)){
        
            if (
    $row["folder_type"] == 'folder') { // fixed to include $row[]
                
    $result .= getXML($row['parent_id']); // you need the parent_id from the row you just got...
            
            
    } else {
                
    $result .= '<jpg label="'.$row['file_label'].'" />'// Fixed to include file_label from row
            
        
    }
        }
        
    $result .= '</folder>';
        
        return 
    $result// return was essential
        
    }
        
       
    ?>
    That should get you a lot closer to your goal
    lamped.co.uk :: Design, Development & Hosting
    marcgray.co.uk :: Technical blog

  • #13
    New Coder
    Join Date
    Aug 2010
    Posts
    89
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thank you again!

    Im getting a fatal error on line 18

    Allowed memory size of 134217728 bytes exhausted (tried to allocate 568 bytes).

    Seems calling it with parent_id -1 doesn't work, it just gets stuck in a loop because the parent of the root is itsself.

    This is what i have in my table:

    id, file_label, parent_id, file_type

    3 root -1 folder
    5 Sports 3 folder
    6 Football 5 file
    7 Cricket 5 file

  • #14
    Super Moderator
    Join Date
    Feb 2009
    Location
    England
    Posts
    539
    Thanks
    8
    Thanked 63 Times in 54 Posts
    Just so we're absolutely clear, yeah, I did something monumentally stupid. Go me!

    PHP Code:
    <?php
            $hostname_conn 
    "888";
        
    $username_conn "888";
        
    $password_conn "888";

        
    $conn mysql_connect($hostname_conn$username_conn$password_conn);
       
       
    //connect to the database
       
       
       
    mysql_select_db("testimages2");
       
       print 
    getXML(-1); // print out the end result of getXML, not in the middle of it
       
     
        
        
    function getXML($parent) {
        
    $res mysql_query("SELECT * FROM menuitems WHERE parent_id = $parent");
        
    //$rows= mysql_fetch_assoc($rows);
        
    $result '<folder>';
        
        
        while(
    $row mysql_fetch_assoc($res)){
        
            if (
    $row["folder_type"] == 'folder') { // fixed to include $row[]
                
    $result .= getXML($row['id']); // **** Ooopsie here ****        
            
    } else {
                
    $result .= '<jpg label="'.$row['file_label'].'" />'// Fixed to include file_label from row
            
        
    }
        }
        
    $result .= '</folder>';
        
        return 
    $result// return was essential
        
    }
        
       
    ?>
    lamped.co.uk :: Design, Development & Hosting
    marcgray.co.uk :: Technical blog

  • #15
    New Coder
    Join Date
    Aug 2010
    Posts
    89
    Thanks
    3
    Thanked 0 Times in 0 Posts
    not printing anything out at all now?

    even if i try to print out this

    PHP Code:
    $result '<folder>';
        print 
    $result


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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