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

PHP
1

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
[TABLE=“width: 200”]
[TR]
[TD]id[/TD]
[TD]parent id[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0(dnotes parent)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
[/TABLE]

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.

2

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

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');

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
3

p.id as parent_id,
p.parent_id as parent_id,

Which is it and how would MyISAM know?

4

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.

5

Alright I got it this far and it displays information but I don’t understand how to “output” the data now.

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');

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?

6

The PHP code would depend on whether you’re using mysqli_ PDO or something else (but please, NOT deprecated mysql_)

7

Im using mysqli

I have tried this and it doesn’t show anything. Blank page.


$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.

8

Hmmm, field name problems? Do you get anything if you try

while ($row = mysqli_fetch_array($query, MYSQLI_NUM)){
echo $row[0].'/'.$row[1].'/'.$row[2].'/'.$row[3];
}
9

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?

10

That query is more than I can digest ATM, I’ll move this to the database forum until that gets sorted.

11

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

12

Thanks Rudy, If the query looks good to you I’m sure it is.

Back to the PHP forum we go :weee:

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

13

Database Connection PHP File

$dbhost = 'localhost';
$dbuser = 'XXXXXX';
$dbpass = 'XXXXX';
$dbname = 'XXXXXX';

$conn = new mysqli($dbhost, $dbuser, $dbpass, $dbname) or die  ('Error connecting to mysql');


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

$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.

14

try this to see everything you are pulling from the database.


//WHERE p.id = 11'
while ($row = mysqli_fetch_array($query, MYSQLI_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.


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] => 
)
15

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.

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:

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

I tried

$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

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.