Jump to content

PHP/MySql: Show parent and childrens from a table


LBernoulli

Recommended Posts

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 by LBernoulli
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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";
}
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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... :P so nah nah na na nah :D

Link to comment
Share on other sites

  • 1 month later...
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.