Help with parent/child lookup

[ROYW1000]

Hi

Can anyone help me with a query with parent and child so I can get the final sectionID from a database. I have tried so many different ways either trying excel vlookups or trying queries but cant get anything to work.

For example section 24 Collar should come out something like this perhaps in a table to seperate that sectionID from the actual category list.

24 Main Section>Bondage Gear>Collars

This would be made as the master sectionID is 24 and its parentID is 4 which is Bondage Gear which has a parentID of 1 which is Main Section which has a parentID of 0.

If anyoine know how this can be done it would be great.

Many thanks in advance.

Roy

[abduraooft]

If the number of levels are fixed, then you may use outer joins to cross that table on itself, like select T1.sectionID,T1.title, T1.parentID,
T2.sectionID,T2.title, T2.parentID from table_name as T1
..........
outer join table_name as T2 on T1.parent_id =T2.sectionID ........

[ROYW1000]

Hi

I have used the info from abduraooft and got nearly what I need and got it into the table format having read some other sites.

Please can anyone help me with the code to get the sectionID at the front of every section and also the > between the sectiosn.

For example:

8 Main Section>Condoms
94 Main Section>Relaxtion Zone>Kama Sutra

This is the code I have come up with so far


<?php
$hostname = "localhost";
$database = "sextoys";
$username = "sextoys";
$password = "sextoys";
mysql_connect($hostname, $username, $password) or die(mysql_error());
mysql_select_db($database) or die(mysql_error());
$query="SELECT t1.title AS lev1, t2.title as lev2, t3.title as lev3, t4.title as lev4
FROM sections AS t1
LEFT JOIN sections AS t2 ON t2.parent = t1.sectionID
LEFT JOIN sections AS t3 ON t3.parent = t2.sectionID
LEFT JOIN sections AS t4 ON t4.parent = t3.sectionID
WHERE t1.title = 'Main Section'";

$result=mysql_query($query);

$num=mysql_numrows($result);
mysql_close();
?>

<table border="0" cellspacing="2" cellpadding="2">
<tr>
<th><font face="Arial, Helvetica, sans-serif">SectionID</font></th>
<th><font face="Arial, Helvetica, sans-serif">Main</font></th>
<th><font face="Arial, Helvetica, sans-serif">Section 1</font></th>
<th><font face="Arial, Helvetica, sans-serif">Section 2</font></th>
</tr>
<?php
$i=0;
while ($i < $num) {

$t1=mysql_result($result,$i,"lev1");
$t2=mysql_result($result,$i,"lev2");
$t3=mysql_result($result,$i,"lev3");
$t4=mysql_result($result,$i,"lev4");
?>
<tr>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $t1; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $t2; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $t3; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $t4; ?></font></td>
</tr>
<?php
$i++;
}
?>


Thanks
Roy

[Old Pedant]

Actually, abduraooft's answer works so long as you know the *maximum* number of levels, if you are willing to use some really ugly coding.

If the number of levels is arbitrary and unbounded (or if the bound is more than, say, 3 or 4 levels), then it is time to redesign the table.

[ROYW1000]

Hi

It can go as deep as 4-5 levels but some levels may only be at level 1-2 where upon I still need to get the result out and some maybe as deep as 4-5.

am not the best coder is PHP/MYSQL so any help is much appreciated as it took me nearly all day to get this far.

Thanks
Roy

[Old Pedant]

To get the sectionid field as well, you just do the same as you did for the titles:
$query=
"SELECT t1.title AS lev1, t1.sectionid AS id1,
t2.title as lev2, t2.sectionid AS id2,
t3.title as lev3, t3.sectionid AS id3,
t4.title as lev4, t4.sectionid AS id4
FROM sections AS t1
LEFT JOIN sections AS t2 ON t2.parent = t1.sectionID
LEFT JOIN sections AS t3 ON t3.parent = t2.sectionID
LEFT JOIN sections AS t4 ON t4.parent = t3.sectionID
WHERE t1.title = 'Main Section'";


As for making the bread crumbs:

I'm not a PHP person, but basically what you want to do is something like this:

echo $lev1
if ( $lev2 != '' ) echo " > " . $lev2
if ( $lev3 != '' ) echo " > " . $lev3
if ( $lev4 != '' ) echo " > " . $lev4

Where you've already pulled the DB fields into variables (or use the field directly). And you might need to use an isempty function or similar instead of testing for blank string.

[abduraooft]

If the number of levels is arbitrary and unbounded (or if the bound is more than, say, 3 or 4 levels), then it is time to redesign the table. For making a breadcrumb in a system, I have used an xml file, along with my table, to save the parent-child relationships. The only problem is, I need to sync my xml file along with any change in my table data.

[ROYW1000]

Hi

Thanks for all you help I am now stuck on getting the actual sectionID to show as the code is only showing section 1 for them as its taking the main section and applying the one.

Therefore I assume I need some kind of if staement to say if $id4 is not blank show id4 and if its blabnk check id3 etc and show that. This is becuase the sections are hirearchy so Main Section=1 and Main Section>Toys=20 for example.

I have tried this bit of code but not getting anything to display.


<?php if (isset($id4)) {echo "$id4";} elseif (isset($id3)) {echo "$id3";} elseif (isset($id2)) {echo "$id2";} elseif (isset ($id1)) {echo "$id1";} ?>


Can anyone see what I am doing wrong.

Thanks
Roy (Novice at PHP)

[ROYW1000]

Hi

Thanks for all your help so far I am coming along slowly. I have got this code now to do exactly what I want it to do but its missing out some section. Basically from what I can tell the query is grabbing everything ok apart from a few section where it has subsections. For example section 4 which is Main Section>Bondage Gear this is not showing but its child sections are.

I also want it to show the section like this when this script is run 4 Main Section>Bondage Gear as well as all its children.

I assume to do this it will either need a nested query or another query and again I dont have a clue where to start on this.


<?php
$hostname = "localhost";
$database = "sextoys";
$username = "sextoys";
$password = "sextoys";
mysql_connect($hostname, $username, $password) or die(mysql_error());
mysql_select_db($database) or die(mysql_error());
$query="SELECT t1.title AS lev1, t1.sectionID AS id1,
t2.title as lev2, t2.sectionID AS id2,
t3.title as lev3, t3.sectionID AS id3,
t4.title as lev4, t4.sectionID AS id4
FROM jss_sections AS t1
LEFT JOIN sections AS t2 ON t2.parent = t1.sectionID
LEFT JOIN sections AS t3 ON t3.parent = t2.sectionID
LEFT JOIN sections AS t4 ON t4.parent = t3.sectionID
WHERE t1.title = 'Main Section'";

$result=mysql_query($query);

$num=mysql_numrows($result);
mysql_close();
?>
<style type="text/css">
.text {
font-family: Verdana, Geneva, sans-serif;
font-size: 12px;
}
</style>
<table border="0" cellspacing="2" cellpadding="2">
<tr>
<th class="text" align="left"><strong>SectionID</strong></th>
<th class="text" align="left"><strong>Section Mapper</strong></th>
</tr>
<?php
$i=0;
while ($i < $num) {

$i1=mysql_result($result,$i,"id1");
$i2=mysql_result($result,$i,"id2");
$i3=mysql_result($result,$i,"id3");
$i4=mysql_result($result,$i,"id4");
$t1=mysql_result($result,$i,"lev1");
$t2=mysql_result($result,$i,"lev2");
$t3=mysql_result($result,$i,"lev3");
$t4=mysql_result($result,$i,"lev4");
?>
<tr>
<td class="text" align="left"><?php if (isset($i4)) {echo "$i4";} elseif (isset($i3)) {echo "$i3";} elseif (isset($i2)) {echo "$i2";} elseif (isset($i1)) {echo "$i1";}?></font></td>
<td class="text" align="left"><?php echo $t1; if ( $t2 != '' ) echo ">" . $t2; if ( $t3 != '' ) echo ">" . $t3; if ( $t4 != '' ) echo ">" . $t4; ?></font></td>
</tr>
<?php
$i++;
}
?>


Many thanks again for all your help hopefully one day I can also be able to help people out.

Roy

[ROYW1000]

HI

The above script works ok now but only brings through 140 out of 156 records as its missing some child records. I have worked out a query I think that will bring out the other 16.

What I am not sure of is firstly can this query be added to the current query if so how and will the t1.t2 etc mess up the first query when I echo the result back out.
SELECT DISTINCT t1.title FROM jss_sections AS t1 LEFT JOIN jss_sections as t2 ON t1.sectionID = t2.parent WHERE t2.parent >0

If it cant be joined perhaps I could just add another query to the same page beneath the current one but it may look a bit odd.

Its been good to learn this stuff although I am very slow.
Thanks
Roy

[ROYW1000]

Hi

If its not possible to get the extra query into the current query please let me know as I have tried many times today without luck.

If I cant will just have to add an extra query into the script at the bottom and hope it all works.

Thanks for your help guys to get me this far.
Roy

[Old Pedant]

You don't say *WHY* those 16 children are missing.

Is it because they are yet another level "down"???

[ROYW1000]

Hi

For example batteries appear and has the following structure a sectionID of 3 and parent of 1.

Bondage Gear does not show and has a section ID of 4 and a parent of 1 but the only difference is that Bondage Gear also has subsecions so for example Main Section>Bondage Gear>Bondage kits is in the output and has the following structure.

section 22 is bondage kits with a parent of 4
section 4 is bondage gear with a parent of 1

So it seems like batteries work as has has no children
Bondage gear shows all the subsections but the main category is not showing as section 4 on it own but all its children are.

Once I realsied this I played around and got the little bit of code above to work and show me the missing 16 sections and it works on its own just not sure how to get that code into the current query.

The current query works a treat though just the missing sections to go.

Roy

[Old Pedant]

Sorry, I just don't see it. Your query in post #9 looks right to me. I can't see why it would miss ANY children.

[ROYW1000]

Hi

I also thought the query in post 9 would work but I enclose the section strcuture for you to look at. It may become clearer when you see the section structure for Main Section as that is sectionID 1 and parent id0. All the second level structures ie bondage gear is sectionID 4 with parentID 1.

I think it is because we are saying that lev1 is title and sectionID i think it need to be parent or something and then next levels changed to work with that.

If I run the query a few up on its own it seems to bring out the missing sections and some how I need to get something like that into the main query.

If the query is correct maybe something in the php is stopping it from showing them all. As its strange that Batteries show this sectionID of 3 and parent of 1 and section 4 Bondage Gear has a parent of 1 and does not show.

I found this artice thats how i got the idea of the second query http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

I just run a test in Navicat to see if tis the php or the query and the query only returns 140 results and not the 156 it should do.

Many thanks for looking.
Roy

[Old Pedant]

Nope. Looks like correct DB relationships to me. (I started to say "looks good to me" but quickly thought better of it.)

What are the 16 that got missed???

[ROYW1000]

Hi

These are the ones that got missed. Sorry have not got the section numbers but hopefully it helps. Even Main Section is missing on its own as well as Bondage Gear.

Main Section
Bondage Gear
Books and Mags
Branded Toys
Clothes
Relaxation Zone
Sex Toys
Speciality Items
Radical Vinyl
Latex
Leather
Rubber
Sexy Briefs
Personal Hygiene
Sex Dolls
VacuLock Sex System

Roy

[ROYW1000]

Hi

These are the 16 missing records and next to that is the sectionID and the parent

Main Section (1,0)
Bondage Gear (4,1)
Books and Mags (5,1)
Branded Toys (6,1)
Clothes (7,1)
Relaxation Zone (18,1)
Sex Toys (19,1)
Speciality Items (20,1)
Radical Vinyl (51,6)
Latex (77,7)
Leather (81,7)
Rubber (86,7)
Sexy Briefs 89,7)
Personal Hygiene (98,18)
Sex Dolls (132,19)
VacuLock Sex System (153,20)

I also enclose 2 exports one of the entire section structure and the other is hirearchy ouput soprted by sectionID so you can see which ones are missing and maybe help understand why this happens.

As I mentioned yesterday its not a php issue as have run the query direct from navicat and mysql and only get thr 140 results out of 156.

And this bit of code helped me find the missing 16 but not sure how or if you can join it all together and make one query that works.

SELECT DISTINCT t1.title FROM jss_sections AS t1 LEFT JOIN jss_sections as t2 ON t1.sectionID = t2.parent WHERE t2.parent >0

Thanks
Roy

[Old Pedant]

What are the data types of those fields in your table???

Most especially, are both sectionID and parentID INT fields???

If they aren't both INT, then run--don't walk--and change them.

[ROYW1000]

Hi

The fields are as follows

`sectionID` mediumint(9) NOT NULL auto_increment,
`parent` mediumint(9) NOT NULL default '0',

If some how we can get that 2nd query as the one I showed on the 19th January in it would work I have been playing around with Union and seeing what I can get out. So far I have got to this stage and I can get all 156 records out but have not really got much further.

select T1.sectionID,T1.title, T1.parent,
T2.sectionID,T2.title, T2.parent from jss_sections as T1
left join jss_sections as T2 on T1.parent = T2.sectionID

Roy

[Old Pedant]

OKAY! Finally understood it!

The ones that are missing are the ones that *DO* have CHILDREN!!!

And that makes sense: They are *ALREADY* appearing in the list of fields, so there's nothing pushing them to appear without their children.

So there are a couple of ways to fix this.

One way would be to add a "child" with a blank name for each parent.

The other way is to create a complicated UNION of the LEFT JOIN results with a set of 3 *other* INNER JOIN results.

But probably the best way to fix it is to alter the PHP code.

I am *NOT* a PHP person, but the logic would go something like this:

$query=
"SELECT t1.title AS lev1, t1.sectionid AS id1,
t2.title as lev2, t2.sectionid AS id2,
t3.title as lev3, t3.sectionid AS id3,
t4.title as lev4, t4.sectionid AS id4
FROM sections AS t1
LEFT JOIN sections AS t2 ON t2.parent = t1.sectionID
LEFT JOIN sections AS t3 ON t3.parent = t2.sectionID
LEFT JOIN sections AS t4 ON t4.parent = t3.sectionID
WHERE t1.title = 'Main Section'";

$id1 = -1;
while ( get a row into $row )
{
if ( $row['id1'] != $id1 )
{
echo $row['lev1'] . "<br/>\n";
$id2 = -1; $id3 = -1; $id4 = -1;
$id1 = $row['id1'];
}
if ( is_set($row['id2'] && $row['id2'] != $id2 )
{
echo $row['lev1'] . " >> " . $row['lev2'] . "<br/>\n";
$id3 = -1; $id4 = -1;
$id2 = $row['id2'];
}
if ( is_set($row['id3'] && $row['id3'] != $id3 )
{
echo $row['lev1'] . " >> " . $row[lev2'] . " >> " . $row['lev3'] . "<br/>\n";
$id4 = -1;
$id3 = $row['id3'];
}
if ( is_set($row['id4'] && $row['id4'] != $id4 )
{
echo $row['lev1'] . " >> " . $row['lev2'] . " >> " . $row['lev3'] . " >> " . $row['lev4'] . "<br/>\n";
$id4 = $row['id4'];
}
... fetch next row if that's not done by the while ...
}


I don't show putting the $idX values into the output (presumably, you really want each level to be a hyperlink the actual page, now? So you'd use the $idX values in the href's of the links?), but that should be the easy part.

Does this begin to make sense to you??

Pardon any PHP syntax/coding errors. Just trying to show concept here.

[Old Pedant]

And for anybody else following this...

Yes, I know that the code for level 4 could be changed from
if ( is_set($row['id4'] && $row['id4'] != $id4 )
{
echo $row['lev1'] . " >> " . $row['lev2'] . " >> " . $row['lev3'] . " >> " . $row['lev4'] . "<br/>\n";
$id4 = $row['id4'];
}

to simply
if ( is_set($row['id4'] )
{
echo $row['lev1'] . " >> " . $row['lev2'] . " >> " . $row['lev3'] . " >> " . $row['lev4'] . "<br/>\n";
}
And there's never really a reason to set $id4, at all. But if we code it as I showed, then it would be easy to add on a level 5, for example.

[ROYW1000]

Hi Old Pedant

Many thanks for the code and I understand sort of how you have got around this. However I know you are not a php coder but I have spent nearly all day trying to get the output and just getting nothing.

As soon as I add the second if statement in I see nothing. Running the first if statement shows me Main Section.

This is the code I have used, not sure if you can see anything I may have got wrong.


<?php
ini_set('display_errors',1);
error_reporting(E_ALL);
$hostname = "localhost";
$database = "sextoys";
$username = "sextoys";
$password = "sextoys";
mysql_connect($hostname, $username, $password) or die(mysql_error());
mysql_select_db($database) or die(mysql_error());
$query="SELECT t1.title AS lev1, t1.sectionID AS id1,
t2.title as lev2, t2.sectionID AS id2,
t3.title as lev3, t3.sectionID AS id3,
t4.title as lev4, t4.sectionID AS id4
FROM jss_sections AS t1
LEFT JOIN jss_sections AS t2 ON t2.parent = t1.sectionID
LEFT JOIN jss_sections AS t3 ON t3.parent = t2.sectionID
LEFT JOIN jss_sections AS t4 ON t4.parent = t3.sectionID
where t1.title='Main Section'";
$id1 = -1;
$result=mysql_query($query);
if (!$result) {
echo 'Could not run query: ' . mysql_error();
exit;
}

while ($row = mysql_fetch_array($result)) {
if ( $row['id1'] != $id1 )
{
echo $row['lev1'] . "<br/>\n";
$id2 = -1; $id3 = -1; $id4 = -1;
$id1 = $row['id1'];
}
if ( isset($row['id2'] && $row['id2'] != $id2 ))
{
echo $row['lev1'] . " >> " . $row['lev2'] . "<br/>\n";
$id3 = -1; $id4 = -1;
$id2 = $row['id2'];
}
}
mysql_close();
mysql_free_result($result);
?>


Thanks
Roy

[Old Pedant]

I'm afraid I don't know what to do here.

Only thing I could suggest is maybe some DEBUG code.


while ($row = mysql_fetch_array($result)) {
echo "DEBUG: $row['id1'] > $row['id2'] > $row['id3'] > $row['id4'] <br/>\n";
if ( $row['id1'] != $id1 )
... rest of code as shown ...

[ROYW1000]

Hi

Debug is showing me nothing either.

I dont know if you would know am I using the correct $result i.e

while ($row = mysql_fetch_array($result)) {

and do I need something like this or is the rest of the script taking care of the matches

<?php
$T=0;
while ($T < $num) {

$T1=mysql_result($result,$T,"T1");
$T2=mysql_result($result,$T,"T2");
$T3=mysql_result($result,$T,"T3");
$T4=mysql_result($result,$T,"T4");
?>

Sorry to be a pain but I know we got so close with the first query and you have spent a lot of time on it but this new code is out my depth and not sure now what I should be doing. I dont my researching when I half understand something but this is way over my head now and any help much appreciated.

Roy

[Old Pedant]

Better ask a PHP person.

The PHP docs for mysql_fetch_array would *seem* to say you are right:
Returns an array of strings that corresponds to the fetched row, or FALSE if there are no more rows. The type of returned array depends on how result_type is defined. By using MYSQL_BOTH (default), you'll get an array with both associative and number indices.

[ROYW1000]

Hi Old Pedant

Hey got it work and it works a treat. Many thanks for your hard work and spending the time to help out. I also learnt from you very quickly queries and some php so again thank you very much. Its much easier to learn from a live working example.

I also found that I could not see the error in your code a bracket was in the wrong place and I just got a blank screen. Lots of people said add this code to the top of the script and the errors will show but they did not. This was due to the fact that the errors would not show as the script would not pass so I find a neat bit of debug code that helped out now as well.

You can call the file from the browser see the errors when you get a blank white screen. Hopefully this can help others. Call the file debug.php and then use the commented examples for how to call your file.


<?php
error_reporting(E_ALL | E_STRICT);
ini_set("display_errors", 1);
include($_GET["file"]);
// http://domain.com/folder/debug.php?file=file_with_errors.php
//http://domain.com/folder/debug.php?file=subfolder/file_with_errors.php
// http://domain.com/folder/debug.php?file=../file_with_errors.php
?>


I also enlcose the final file so you can all see it. Thanks again for your help.


<?php
ini_set('display_errors',1);
error_reporting(E_ALL);
$hostname = "localhost";
$database = "sextoys";
$username = "sextoys";
$password = "sextoys";
mysql_connect($hostname, $username, $password) or die(mysql_error());
mysql_select_db($database) or die(mysql_error());
$query="SELECT t1.title AS lev1, t1.sectionID AS id1,
t2.title as lev2, t2.sectionID AS id2,
t3.title as lev3, t3.sectionID AS id3,
t4.title as lev4, t4.sectionID AS id4
FROM jss_sections AS t1
LEFT JOIN jss_sections AS t2 ON t2.parent = t1.sectionID
LEFT JOIN jss_sections AS t3 ON t3.parent = t2.sectionID
LEFT JOIN jss_sections AS t4 ON t4.parent = t3.sectionID
where t1.title='Main Section'";

$id1 = -1;
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
if ( $row['id1'] != $id1 )
{
echo $row['id1'] . " " . $row['lev1'] . "<br/>\n";
$id2 = -1; $id3 = -1; $id4 = -1; $id1 = $row['id1'];
}
if (isset($row['id2']) && $row['id2'] != $id2 )
{
echo $row ['id2'] . " " . $row['lev1'] . ">" . $row['lev2'] . "<br/>\n";
$id3 = -1; $id4 = -1;
$id2 = $row['id2'];
}
if (isset($row['id3']) && $row['id3'] != $id3 )
{
echo $row['id3'] . " " . $row['lev1'] . ">" . $row['lev2'] . ">" . $row['lev3'] . "<br/>\n";
$id4 = -1;
$id3 = $row['id3'];
}
if (isset($row['id4']) && $row['id4'] != $id4 )
{
echo $row['id4'] . " " . $row['lev1'] . ">" . $row['lev2'] . ">" . $row['lev3'] . ">" . $row['lev4'] . "<br/>\n";
$id4 = $row['id4'];
}
}
mysql_close();
?>


Old Pedant you are a star.

Roy

[Shadowpawn]

<Ignore - was supposed to be new thread>

We have a table of procedures. It is structured as parents, children, grandchildren. We use it for defining procedures, sub-procedures and sub-sub-procedures work instructions.


ParentID ID Sequence Name
NULL 100 1 Procedure 1.0.0
100 101 1 Procedure 1.1.0
101 102 1 Procedure 1.1.1
101 103 2 Procedure 1.1.2
NULL 104 2 Procedure 2.0.0
104 105 1 Procedure 2.1.0


I have to extract a list of all procedures in the 1.x.x branch. I know how to find the top most entry:

select ID from tbProcedure where ParentID = NULL and Sequence = 1

So in the simple example above, I have to retrieve ID's 100-103 for further processing. Also, the ID numbers are not sequential in the actual database. How do I get the rest of these Procedures? Any help would be greatly appreciated.

Thanks
Alan