SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Zealot clarnp49's Avatar
    Join Date
    Sep 2004
    Location
    Bowie MD
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Resursive, Single Query??? Parent Child Loop?? Directory folder id.... ugh

    Here is what I want to do but I'm having a hell of a time figuring out a direction to go.
    Mysql Table has id and parent id

    I don't want a tree, I don't want a menu I'm looking for a <a href=" " > statement filled with a single query no limit to levels of how deep the id and parent id can go.

    So here I want to generate a query that goes in normal english.
    select id from table_name check if it's has a parent then check to see what the parents id is and then check for that parents id and so on and so forth.

    so it would look like
    table example
    id parent id
    1 0(dnotes parent)
    2 1
    3 2
    4 1


    output would be for the folder id of 3
    <a href ="3/2/1">file</a>

    why this way because I'm storing file locations with no depth and the file table has a folder id, so I need to take that folder id and find the child folders back to parent.
    Why is it so confusing in my head and when I read about it, it only gets worse. And any example I find is for a tree view so I need to be able to assign this to an array to get the directory path at the end.
    If it works don't fix it. If it's broke fix it.
    He who knows all is the smartest.
    He who doesn't know anything isn't dumbest.

  2. #2
    SitePoint Zealot clarnp49's Avatar
    Join Date
    Sep 2004
    Location
    Bowie MD
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ive tried to get this to work but its just giving me errors of everything, I must not understand it enough yet.
    Error: Column 'parent_id' in where clause is ambiguous: SELECT p.id as parent_id, p.parent_id as parent_id, c1.id as child_id_1, c1.folder_title as child_name_1, c2.id as child_id_2, c2.folder_title as child_name_2, c3.id as child_id_3, c3.folder_title as child_name_3, c4.id as child_id_4, c4.folder_title as child_name_4 FROM folder_title p LEFT JOIN folder_title c1 ON c1.parent = p.id LEFT JOIN folder_title c2 ON c2.parent = c1.id LEFT JOIN folder_title c3 ON c3.parent = c2.id LEFT JOIN folder_title c4 ON c4.parent = c3.id WHERE parent_id=0

    Code:
    CREATE TABLE IF NOT EXISTS `folder_title` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `parent_id` int(11) DEFAULT '0',
      `folder_title` varchar(100) NOT NULL,
      `folder_description` text NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;
    
    --
    -- Dumping data for table `folder_title`
    --
    
    INSERT INTO `folder_title` (`id`, `parent_id`, `folder_title`, `folder_description`) VALUES
    (1, 0, 'Blah', 'Blah Blah'),
    (2, 0, 'Blah2', 'Blah Blah'),
    (3, 0, 'Blah3', 'Blah Blah'),
    (4, 0, 'Blah4', 'Blah Blah'),
    (5, 0, 'Blah 5', 'Blah Blah'),
    (6, 0, 'Blah6', 'Blah Blah'),
    (7, 2, 'Blah2-1', 'Blah Blah'),
    (8, 2, 'Blah2-2', 'Blah Blah'),
    (9, 2, 'Blah2-3', 'Blah Blah'),
    (10, 2, 'Blah2-4', 'Blah Blah');
    Code:
    SELECT 
        p.id as parent_id,
        p.parent_id as parent_id,
        c1.id as child_id_1,
        c1.folder_title as child_name_1,
        c2.id as child_id_2,
        c2.folder_title as child_name_2,
        c3.id as child_id_3,
        c3.folder_title as child_name_3,
        c4.id as child_id_4,
        c4.folder_title as child_name_4
    FROM 
        folder_title p
    LEFT JOIN folder_title c1
        ON c1.parent = p.id
    LEFT JOIN folder_title c2
        ON c2.parent = c1.id
    LEFT JOIN folder_title c3
        ON c3.parent = c2.id
    LEFT JOIN folder_title c4
        ON c4.parent = c3.id
    WHERE
        parent_id=0
    If it works don't fix it. If it's broke fix it.
    He who knows all is the smartest.
    He who doesn't know anything isn't dumbest.

  3. #3
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    16,481
    Mentioned
    164 Post(s)
    Tagged
    1 Thread(s)
    p.id as parent_id,
    p.parent_id as parent_id,

    Which is it and how would MyISAM know?

  4. #4
    SitePoint Zealot clarnp49's Avatar
    Join Date
    Sep 2004
    Location
    Bowie MD
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I tried to change p.id = p.parent_id to p.id=p.id and no success

    I also tried to just get rid of it as p.id is the parent_id

    I want this to be able to check the parent id and see if it's 0 if not find the next id that is the parent

    id 10 parent is 2
    id 2 parent is 0 - parent folder

    but if I keep going for example with more folders

    id 11 parent is 10
    id 10 parent is 2
    id 2 parent is 0 - end query

    or
    id 45 parent is 11
    id 11 parent is 10
    id 10 parent is 2
    id 2 parent is 0 - end query

    I understand I need a limit on the depth but I can't even get 1 level let alone 10 levels or 5 or how many I chose.

    Am I headed in the right direction? What do I need to change in the above query to get it to work? What am I comparing wrong? I saw it was assigned to parent_id but I tried to change the one and it still gave the same error.
    If it works don't fix it. If it's broke fix it.
    He who knows all is the smartest.
    He who doesn't know anything isn't dumbest.

  5. #5
    SitePoint Zealot clarnp49's Avatar
    Join Date
    Sep 2004
    Location
    Bowie MD
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Alright I got it this far and it displays information but I don't understand how to "output" the data now.

    Code:
    CREATE TABLE IF NOT EXISTS `folder_title` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `parent_id` int(11) DEFAULT '0',
      `folder_title` varchar(100) NOT NULL,
      `folder_description` text NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;
    
    --
    -- Dumping data for table `folder_title`
    --
    
    INSERT INTO `folder_title` (`id`, `parent_id`, `folder_title`, `folder_description`) VALUES
    (1, 0, 'Blah', 'Blah Blah'),
    (2, 0, 'Blah2', 'Blah Blah'),
    (3, 0, 'Blah3', 'Blah Blah'),
    (4, 0, 'Blah4', 'Blah Blah'),
    (5, 0, 'Blah 5', 'Blah Blah'),
    (6, 0, 'Blah6', 'Blah Blah'),
    (7, 2, 'Blah2-1', 'Blah Blah'),
    (8, 2, 'Blah2-2', 'Blah Blah'),
    (9, 2, 'Blah2-3', 'Blah Blah'),
    (10, 9, 'Blah2-4', 'Blah Blah'),
    (11, 10, 'Blah2-2', 'Blah Blah'),
    (12, 3, 'Blah2-3', 'Blah Blah'),
    (13, 11, 'Blah2-4', 'Blah Blah');
    Code:
    SELECT 
        p.id as id,
        p.parent_id as parent_id,
        c1.id as child_id_1,
        c1.folder_title as child_name_1,
        c2.id as child_id_2,
        c2.folder_title as child_name_2,
        c3.id as child_id_3,
        c3.folder_title as child_name_3,
        c4.id as child_id_4,
        c4.folder_title as child_name_4
    FROM 
        folder_title p
    LEFT JOIN folder_title c1
        ON c1.parent_id = p.id
    LEFT JOIN folder_title c2
        ON c2.parent_id = c1.id
    LEFT JOIN folder_title c3
        ON c3.parent_id = c2.id
    LEFT JOIN folder_title c4
        ON c4.parent_id = c3.id
    WHERE c3.id = 11
    With this select statement I can get the parent's id back to the root directory, tested on sqlfiddle and got my results correct but I want to put this into a php echo statement?
    echo '<ahref='.$pid.'/'.$c1.id.'/'.$c2.id>File Blah</a>';

    How do I go about getting this above information?
    If it works don't fix it. If it's broke fix it.
    He who knows all is the smartest.
    He who doesn't know anything isn't dumbest.

  6. #6
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    16,481
    Mentioned
    164 Post(s)
    Tagged
    1 Thread(s)
    The PHP code would depend on whether you're using mysqli_ PDO or something else (but please, NOT deprecated mysql_)

  7. #7
    SitePoint Zealot clarnp49's Avatar
    Join Date
    Sep 2004
    Location
    Bowie MD
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Im using mysqli

    I have tried this and it doesn't show anything. Blank page.
    Code:
    $query =mysqli_query($conn,'SELECT 
        p.id as id,
        p.parent_id as parent_id,
        c1.id as child_id_1,
        c1.folder_title as child_name_1,
        c2.id as child_id_2,
        c2.folder_title as child_name_2,
        c3.id as child_id_3,
        c3.folder_title as child_name_3,
        c4.id as child_id_4,
        c4.folder_title as child_name_4
    FROM 
        folder_title p
    LEFT JOIN folder_title c1
        ON c1.parent_id = p.id
    LEFT JOIN folder_title c2
        ON c2.parent_id = c1.id
    LEFT JOIN folder_title c3
        ON c3.parent_id = c2.id
    LEFT JOIN folder_title c4
        ON c4.parent_id = c3.id
    WHERE c3.id = 11');
    
    while ($row = mysqli_fetch_array($query)){
    
    echo $row['child_name_1'].'/'.$row['child_name_2'].'/'.$row['child_name_3'].'/'.$row['child_name_4'];
    }
    The problem is I can see correct results when I run the query in mysql but I can't see any results when I run it in php. Maybe I'm just not doing something right.
    If it works don't fix it. If it's broke fix it.
    He who knows all is the smartest.
    He who doesn't know anything isn't dumbest.

  8. #8
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    16,481
    Mentioned
    164 Post(s)
    Tagged
    1 Thread(s)
    Hmmm, field name problems? Do you get anything if you try
    HTML Code:
    while ($row = mysqli_fetch_array($query, MYSQLI_NUM)){
    echo $row[0].'/'.$row[1].'/'.$row[2].'/'.$row[3];
    }

  9. #9
    SitePoint Zealot clarnp49's Avatar
    Join Date
    Sep 2004
    Location
    Bowie MD
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mittineague View Post
    Hmmm, field name problems? Do you get anything if you try
    HTML Code:
    while ($row = mysqli_fetch_array($query, MYSQLI_NUM)){
    echo $row[0].'/'.$row[1].'/'.$row[2].'/'.$row[3];
    }
    Alright gaining on it but still weird results.

    It stops showing the parent id's after the second row and then either doesn't match or shows something from another parent id.

    example:
    if I change the WHERE statement to p.id=13
    I get 13/10// where the last two are null even though there is still 9/2 to be put on

    if I change the WHERE statement to p.id=9
    I get 9/2/11/blah2-2

    Is my join statement right for what I want to do? Do do I have something wrong there that is causing it to jump?
    If it works don't fix it. If it's broke fix it.
    He who knows all is the smartest.
    He who doesn't know anything isn't dumbest.

  10. #10
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    16,481
    Mentioned
    164 Post(s)
    Tagged
    1 Thread(s)
    That query is more than I can digest ATM, I'll move this to the database forum until that gets sorted.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Mittineague View Post
    That query is more than I can digest ATM, I'll move this to the database forum until that gets sorted.
    i doubt it's a database issue

    he did say "I can see correct results when I run the query in mysql but I can't see any results when I run it in php"

    also, my eyeball check finds no issues with the actual sql
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    16,481
    Mentioned
    164 Post(s)
    Tagged
    1 Thread(s)
    Thanks Rudy, If the query looks good to you I'm sure it is.

    Back to the PHP forum we go

    @clarnp49 ; please post the pertinent database code (without any sensitive info).

  13. #13
    SitePoint Zealot clarnp49's Avatar
    Join Date
    Sep 2004
    Location
    Bowie MD
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mittineague View Post
    Thanks Rudy, If the query looks good to you I'm sure it is.

    Back to the PHP forum we go

    @clarnp49 ; please post the pertinent database code (without any sensitive info).
    Database Connection PHP File
    Code:
    $dbhost = 'localhost';
    $dbuser = 'XXXXXX';
    $dbpass = 'XXXXX';
    $dbname = 'XXXXXX';
    
    $conn = new mysqli($dbhost, $dbuser, $dbpass, $dbname) or die  ('Error connecting to mysql');
    Code:
    CREATE TABLE IF NOT EXISTS `folder_title` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `parent_id` int(11) DEFAULT '0',
      `folder_title` varchar(100) NOT NULL,
      `folder_description` text NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;
    
    --
    -- Dumping data for table `folder_title`
    --
    
    INSERT INTO `folder_title` (`id`, `parent_id`, `folder_title`, `folder_description`) VALUES
    (1, 0, 'Blah', 'Blah Blah'),
    (2, 0, 'Blah2', 'Blah Blah'),
    (3, 0, 'Blah3', 'Blah Blah'),
    (4, 0, 'Blah4', 'Blah Blah'),
    (5, 0, 'Blah 5', 'Blah Blah'),
    (6, 0, 'Blah6', 'Blah Blah'),
    (7, 2, 'Blah2-1', 'Blah Blah'),
    (8, 2, 'Blah2-2', 'Blah Blah'),
    (9, 2, 'Blah2-3', 'Blah Blah'),
    (10, 9, 'Blah2-4', 'Blah Blah'),
    (11, 10, 'Blah2-2', 'Blah Blah'),
    (12, 11, 'Blah2-3', 'Blah Blah'),
    (13, 12, 'Blah2-4', 'Blah Blah');
    Display_folders.php
    Code:
    $query =mysqli_query($conn,'SELECT 
        p.id as id,
        p.parent_id as parent_id,
        c1.id as child_id_1,
        c1.folder_title as child_name_1,
        c2.id as child_id_2,
        c2.folder_title as child_name_2,
        c3.id as child_id_3,
        c3.folder_title as child_name_3,
        c4.id as child_id_4,
        c4.folder_title as child_name_4
    FROM 
        folder_title p
    LEFT JOIN folder_title c1
        ON c1.parent_id = p.id
    LEFT JOIN folder_title c2
        ON c2.parent_id = c1.id
    LEFT JOIN folder_title c3
        ON c3.parent_id = c2.id
    LEFT JOIN folder_title c4
        ON c4.parent_id = c3.id
    WHERE p.id = 11');
    
    while ($row = mysqli_fetch_array($query, MYSQLI_NUM)){
    echo $row[0].'/'.$row[1].'/'.$row[2].'/'.$row[3];
    }
    Output "should be"
    2/9/10/11 if it's backwards I'm not worried I can flip the array display.

    Your help is much appreciated! Thanks for passing it to a couple other eyes as well, this is frustrating to me right now.
    If it works don't fix it. If it's broke fix it.
    He who knows all is the smartest.
    He who doesn't know anything isn't dumbest.

  14. #14
    SitePoint Wizard lorenw's Avatar
    Join Date
    Feb 2005
    Location
    was rainy Oregon now sunny Florida
    Posts
    1,094
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    try this to see everything you are pulling from the database.

    PHP Code:
    //WHERE p.id = 11'
    while ($row mysqli_fetch_array($queryMYSQLI_ASSOC)){
    echo 
    $row[0].'/'.$row[1].'/'.$row[2].'/'.$row[3];
    echo
    '<pre>'print_r($row); echo '</pre>';

    This is what I'm getting. Easier with MYSQLI_ASSOC to see what is going on for debugging.
    Code:
    Array
    (
        [id] => 11 
        [parent_id] => 10 // looks correct
        [child_id_1] => 13 // looks correct
        [child_name_1] => Blah2-4// looks correct
        [child_id_2] => 
        [child_name_2] => 
        [child_id_3] => 
        [child_name_3] => 
        [child_id_4] => 
        [child_name_4] => 
    )
    What I lack in acuracy I make up for in misteaks

  15. #15
    SitePoint Evangelist bronze trophy
    Join Date
    Feb 2013
    Posts
    510
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)
    Thought I'd run your query to see what's up. Results are exactly as written in query. I changed output to show field and and expected result and output. It runs up to your last available table row (id 13) then no more records are available. This is the echo line used.
    PHP Code:
    echo 'Starting ID 11 : '.$row[0].'<br />Parent ID 10 : '.$row[1].'<br />child_id_1 12 : '.$row[2].'<br />child_name_1 Blah2-3 : '.$row[3].'<br />child_id_2 13 : '.$row[4].'<br />child_name_2 Blah2-4 : '.$row[5].'<br />child_id_3 : '.$row[6].'<br />child_name_3 : '.$row[7].'<br />child_id_4 : '.$row[8].'<br />child_name_4 : '.$row[9]; 
    The results:
    Code:
    Starting ID 11 : 11
    Parent ID 10 : 10
    child_id_1 12 : 12
    child_name_1 Blah2-3 : Blah2-3
    child_id_2 13 : 13
    child_name_2 Blah2-4 : Blah2-4
    child_id_3 :
    child_name_3 :
    child_id_4 :
    child_name_4 :
    Could it be you are wishing to go backwards through the records instead of forwards?
    Anyway, seems to work as written.

  16. #16
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    16,481
    Mentioned
    164 Post(s)
    Tagged
    1 Thread(s)
    I tried
    PHP Code:
    $query "SELECT 
        p.id as id,
        p.parent_id as parent_id,
        c1.id as child_id_1,
        c1.folder_title as child_name_1,
        c2.id as child_id_2,
        c2.folder_title as child_name_2,
        c3.id as child_id_3,
        c3.folder_title as child_name_3,
        c4.id as child_id_4,
        c4.folder_title as child_name_4
    FROM 
        folder_title p
    LEFT JOIN folder_title c1
        ON c1.parent_id = p.id
    LEFT JOIN folder_title c2
        ON c2.parent_id = c1.id
    LEFT JOIN folder_title c3
        ON c3.parent_id = c2.id
    LEFT JOIN folder_title c4
        ON c4.parent_id = c3.id
    WHERE p.id = 11"
    ;
    if (
    $result mysqli_query($conn$query)) {
        while (
    $row mysqli_fetch_assoc($result)) {
            foreach(
    $row as $key => $val) {
                echo 
    $key ' --> ' $val '<br/>';
            }
            echo 
    '<br/>';
        }

    and got
    id --> 11
    parent_id --> 10
    child_id_1 --> 12
    child_name_1 --> Blah2-3
    child_id_2 --> 13
    child_name_2 --> Blah2-4
    child_id_3 -->
    child_name_3 -->
    child_id_4 -->
    child_name_4 -->

  17. #17
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,051
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    I don't think the adjacency pattern fits your business model very well. The adjacency list pattern works well when you can pull the entire tree. So for things like a menu where all the items will be shown. However, if you would like to target specific nodes and pull their children to an infinite death your going to run into logical and performance problems. Given the requirements a nested set pattern would be more appropriate. You could probably even get away with using the enumeration pattern which is less flexible than nested set but some what easier to implement. Than there is also closure table pattern which you could look into. I think all your problems here spawn from not using the correct hierarchical relational database pattern for the business requirements at hand. So I would recommend changing it. Preferably to a nested set or perhaps a merge of adjacency list and path enumeration. I could even go as far as saying you should be using a database that supports hierarchical data such as Oracle. Though that is probably impractical given this specific project and the cost associated with using something like Oracle.
    The only code I hate more than my own is everyone else's.


Bookmarks

Posting Permissions

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