PHP/MySql: Show parent and childrens from a table

[LBernoulli]

Hi everybody, and thank you in advance. I'm new to this forum and to PHP/MySql.
 
I've a table:

CREATE TABLE IF NOT EXISTS `Test` ( 
`ID` INT NOT NULL AUTO_INCREMENT , 
`Parent` VARCHAR(45) NOT NULL , 
`Child` VARCHAR(45) NOT NULL , 
PRIMARY KEY (`ID`) ) 
ENGINE = InnoDB; 


INSERT INTO `Test` (`ID`,`Parent`,`Child`) VALUES ('1', 'X', 'a'); 
INSERT INTO `Test` (`ID`,`Parent`,`Child`) VALUES ('2', 'X', 'b'); 
INSERT INTO `Test` (`ID`,`Parent`,`Child`) VALUES ('3', 'X', 'c'); 
INSERT INTO `Test` (`ID`,`Parent`,`Child`) VALUES ('4', 'Y', 'a'); 
INSERT INTO `Test` (`ID`,`Parent`,`Child`) VALUES ('5', 'Y', 'b'); 
INSERT INTO `Test` (`ID`,`Parent`,`Child`) VALUES ('6', 'Z', 'a'); 
INSERT INTO `Test` (`ID`,`Parent`,`Child`) VALUES ('7', 'W', 'a'); 
INSERT INTO `Test` (`ID`,`Parent`,`Child`) VALUES ('8', 'W', 'b'); 
INSERT INTO `Test` (`ID`,`Parent`,`Child`) VALUES ('9', 'W', 'c'); 

The idea is to print, using PHP (in a HTML page), this (for every parent every child into the table):

X

a

b

c

 

Y

a

b

 

Z

a

 

W

a

b

c

 

Do you have an idea/suggestion to make that?

Thanks

Edited July 24, 2013 by LBernoulli

[Muddy_Funster]

Assuming that this is a single level Parent/Child relationship, and that the parents will never be children themselves, then something like the following:

<?php
$results = array();
$dsn = "mysql:host=<hostname>;dbname=<dbname>";
$con = new PDO($dsn, "dbUserName", "dbPassword");
$sql = "SELECT `parent`, `child` FROM Test";
$recordset = $con->query($sql);
foreach($recordset as $row){
if(!array_key_exists($results, $row['parent'])){
$results[$row['parent']] = array($row['child'];
}
else{
array_push($results[$row['parent']], $row['child']);
}
}
foreach($results as $parent =>$children){
echo "<span style=\"font-weight:bold;\">$parent</span><br>";
foreach ($children as $child){
echo $child."<br>";
}
echo "<br>";
}
?>

This is off the cuff and untested, but it's not too far from the mark.

[Psycho]

I think an improved approach would be to order the results using the query - then there will be less code needed to create the output:

 

 

$query = "SELECT Parent, Child
          FROM Test
          ORDER BY Parent, Child";
$result = mysql_query($query);

$current_parent = false;
while($row = mysql_fetch_assoc($result);
{
    if($current_parent != $row['Parent'])
    {
        $current_parent = $row['Parent'];
        echo "<span style=\"font-weight:bold;margin-top:12px;\">{$current_parent}</span><br>\n";
    }
    echo "{$row['Child']}<br>\n";
}

[Muddy_Funster]

I don't think changing the use of PDO to mysql_* is strictly an improvment

[Psycho]

I don't think changing the use of PDO to mysql_* is strictly an improvment

 

Agreed, it definitely is not. But, based upon the OP being "new" I didn't want to cloud the solution with PDO. I don't want to encourage using mysql_, but it is still the most understood DB process of PHP use and most typical in current tutorials.

[Muddy_Funster]

Agreed, it definitely is not. But, based upon the OP being "new" I didn't want to cloud the solution with PDO. I don't want to encourage using mysql_, but it is still the most understood DB process of PHP use and most typical in current tutorials.

+1, I just like it to be made obvious

 

On another nit-picky note - the sample ordering of the OP shows order by ID, not order by Parent, as they have parent X at the top and parent W at the bottom... so nah nah na na nah

[LBernoulli]

And what about a mysqli solution?

[Psycho]

A forum post is not an appropriate medium for teaching you a new methodology for DB handling. There are plenty of tutorials out there to get you started.