SitePoint Sponsor

User Tag List

Results 1 to 17 of 17

Hybrid View

  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,420
    Mentioned
    160 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,420
    Mentioned
    160 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,420
    Mentioned
    160 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 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


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
  •