[SOLVED] Looping select statement

[crims0nluv]

Hi all,

 

I have been trying to solve this, but I couldn't seem to get it right. Hope someone can help with my script.  Seems like i'm not getting the logic right. I tried to do while loop in it, but it will be infinite loop

 

Below is the question and my scripts.  Thanks  

 

there are two tables,

Names(ID INT primary key, Name varchar(255))

Relationships(NameID INT, Parent_NameID INT)

Linked via Names.ID=Relationships.NameID and where top-most name has a Parent_NameID=0

Show a nested list of names including Level, NameID and Name, where Level indicates the nest level(or depth) from the top as indicated in the expected output below.

 

Sample :

ID     Name

1      John

2      Mary

3      Doe

4      Jane

5      Peter

 

Relationships table content

NameID    Parent_NameID

1              0

2              1

3              2

4              1

5              2

 

Expected output

The sequence of the output, in which child elements appear immediately beneath their respective parent elements

 

Level ID Name

0           1 John

1 2 Mary

2 5 Peter

2 3 Doe

1 4 Jane

 

 

$query=mysql_query("SELECT * FROM Names a, Relationships b WHERE a.ID=b.NameID") or die(mysql_error());

$row=mysql_fetch_array($query);

$count=mysql_query("SELECT count(*) as num FROM Relationships");

$count_rel=mysql_fetch_array($count);

$num= $count_rel['num'];

$level=0;

for($i=0; $i<$num; $i++) {

$parent=mysql_query($cmd="SELECT * FROM Relationships WHERE NameID='".$row['Parent_NameID']."'") or die(mysql_error());

$row2=mysql_fetch_array($parent);

 if($row2['Parent_NameID']!='0') {

   $level=$level+1;

 }

echo $level;

}

 

[Barand]

a recursive function should do it

<?php
     function printNames($parent, $level=0)
     {
         $sql = "SELECT n.ID, m.name 
         		FROM names n
         		INNER JOIN  relationshops r ON n.id = r.NameID
         		WHERE Parent_NameID = $parent";
         while (list($id, $name) = mysql_fetch_row($res))
         {
         	 $indent = str_repeat('---', $level);
         	 echo "$indent $name <br/>";
         	printNames($id, $level+1);
         }
     }
     
     printNames(0);    // call the function
?>

[crims0nluv]

Hi Barand,

 

Thanks so much for your reply =)

I tried the query you gave me, however, the output is wrong. Reversing will give me the level from 0-4, but some might be 2nd level, some might be 3rd, and there are duplication. For example, Peter and Doe both are in level 2. I couldn't manage to find out which level they are at. Thanks!!

[Barand]

OK, I corrected the obvious errors and added $level to the output

<?php
include 'db2.php';
     function printNames($parent, $level=0)
     {
         $sql = "SELECT n.ID, n.name 
         		FROM names n
         		INNER JOIN  relationships r ON n.id = r.NameID
         		WHERE Parent_NameID = $parent";
         $res = mysql_query($sql) or die (mysql_error());
         while (list($id, $name) = mysql_fetch_row($res))
         {
         	 $indent = str_repeat('---', $level);
         	 echo "$indent $name $level<br/>";
         	printNames($id, $level+1);
         }
     }
     
     printNames(0);    // call the function
?>

-->
John 0
--- Mary 1
------ Doe 2
------ Peter 2
--- Jane 1

[crims0nluv]

Oh man, The output is there all the while. I missed the level at the str_repeat. So sorry and million of thanks. Greatly appreciated =)

[asimqazi]

Hi,

  Barand i m stucked in the same problem, can i get the solution in a pure SQL Querry form ?

 

One more question please,

what does "Show a nested list of names including Level, NameID and Name, where Level indicates the nest level(or depth) from the top" means, what is the answer to this statement, or i we can ask that

 

John 0

--- Mary 1

------ Doe 2

------ Peter 2

--- Jane 1

 

this means the nested list of names ?

 

sorry for my dumb question, i m a bit beginer.

 

SUNNY.

[Barand]

One more question please,

what does "Show a nested list of names including Level, NameID and Name, where Level indicates the nest level(or depth) from the top" means, what is the answer to this statement, or i we can ask that

 

John 0

--- Mary 1

------ Doe 2

------ Peter 2

--- Jane 1

 

this means the nested list of names ?

 

Yes, the sequence of the output, in which child elements appear immediately beneath their respective parent elements