Selecting parents and childs

jaheba

I am making a database with the following tables

<Road>
Road ID
Road Name

<Family>
family ID
familyname
housenumber

<Person>
Person ID
family ID
Name
Age

I want to show a list with the familys that live on a road and for each family, witch persons who belongs to the family

eks :

Road >

Family 1, housenumber 1 >>

Hans 28 years
Per 21 Years

Family 2, housenumber 2 >>

gitte 30 yrs
preben 15 yrs

And so on.....

I have this code so far, but it prints the same 2 persons in each family :-((

Code :

$db_name = "sitastest";
$table_name = "p_sort";
$connection = @mysql_connect("localhost","root","root") or die("Couldn't Connect.");
$db = @mysql_select_db($db_name, $connection) or die("Couldn't select database.");

$sql ="SELECT So_ID,La_Navn,Da_Navn,So_Besk,So_Img FROM $table_name WHERE Kat_ID = \"$param1\" AND Fam_ID = \"$param2\" order by La_Navn ASC";
$total_result = @($sql, $connection) or die("Error #". mysql_errno() . ": " . mysql_error());
$total_found = @mysql_num_rows($total_result);

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

 $So_ID1=$row['So_ID'];
 $La_Navn=$row['La_Navn'];
 $Da_Navn=$row['Da_Navn'];
 $So_Besk=$row['So_Besk'];
 $So_Img=$row['So_Img'];

echo $So_ID1;
$PL_list = mysql_query("SELECT * FROM p_planter WHERE PL_Sort = $So_ID1 ");
while ($row2 = mysql_fetch_array($PL_list)) {

 $PL_ID=$row2['PL_ID'];
 $PL_sort=$row2['PL_sort'];
 $PL_Form=$row2['PL_Form'];
 $PL_cm=$row2['PL_cm'];
 $Pris_1=$row2['Pris_1'];
 $Pris_2=$row2['Pris_2'];

$Vis_planter = $display_block_plant .= "<table border=1 width=100%><tr><td>$PL_Form</td><td>$PL_cm</td><td>$Pris_1</td><td>$Pris_2</td></tr></table>";

}

$display_block .= "<tr><td>$So_Img</td><td width=35%>$La_Navn - $Da_Navn $So_Besk</td><td Valign=top>$Vis_planter</td></tr><tr><td colspan=3></td></tr>";

}

trooper

OK i'll be honest here i haven't actually looked closely at your code.

However here is one tip that you should ALWAYS consider.

When using databases always design them before you get to the coding part. That way it becomes easier to spot potential issues before you hit them

Now back to your script. Whenever i want to create a parent/ child relationship i always put in a a field called "parent" that way you can always put in the record id of the item that the current record belongs to.

e.g countries and cities

If record id 14 is England then when i put in london i would put in 14 in the parent field for the record that contains london. That define (an albeit easy) relationship between london and England.

Now i know its not rocket science but it is easy to follow and simple.

After all why make things more difficult for yourself?

HTH

jaheba

The design and the tables i described in my first posting was just to illustrate, not real tables... I did use the method you described with parents/child ID's so that far everything is okay...

My problem is that the outer While-loop returns the correct output (eg list of familys) but the inner while-loop only returns the first record in the array and then re-use it under all the "family" listings...:-((

Just to test, i replaced the variable ($Vis_planter) that i use to print the child-output with the Childs "parent-ID" ($PL_Sort) and then i get the correct ID-value under each family... Maybe i just cant see the trees for the forest...

AliceG

Look at this.
http://www.devshed.com/Server_Side/PHP/ExtensibleMenuClass/page1.html

It's an example for Menue building and the table for the menue- categories has the father/child relationship.

Alice