Jump to content


Photo

PHP/MySql: Show parent and childrens from a table


  • Please log in to reply
7 replies to this topic

#1 LBernoulli

LBernoulli

    Newbie

  • New Members
  • Pip
  • 2 posts

Posted 24 July 2013 - 11:56 AM

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, 24 July 2013 - 12:05 PM.


#2 Muddy_Funster

Muddy_Funster

    Advanced Member

  • Members
  • PipPipPip
  • 3,383 posts

Posted 24 July 2013 - 01:55 PM

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.


Please: "This doesn't work..." is not a question.  We're not the government: we don't have anything to view what your doing on your computer.  Help us help you by asking a good question.

 

"Things needed to ask a "good" question:

  • A description of the context of your issue (Optional but can be a huge help in certain circumstances)
  • What is the actual problem (Mandatory)
  • What does the code actually do (Mandatory)
  • What you think the code should do / What you want the code to do (Mandatory)
  • What things have you tried so far (Optional, but missing it out just wastes your time and ours)
  • The actual code as you are running it - minus any personal information like Database Login Credentials (Mandatory - don't just post pseudo, the vast majority of issues are syntax and not logic)
  • As much info about your development environment as you can give - even if it's just letting us know you are using a hosting provider instead of a local install (Optional, but some questions can not be answered without it.)

#3 Psycho

Psycho

    Advanced Member

  • Moderators
  • 11,491 posts
  • LocationCanada

Posted 24 July 2013 - 02:25 PM

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";
}

The quality of the responses received is directly proportional to the quality of the question asked.

I do not always test the code I provide, so there may be some syntax errors. In 99% of all cases I found the solution to your problem here: http://www.php.net

#4 Muddy_Funster

Muddy_Funster

    Advanced Member

  • Members
  • PipPipPip
  • 3,383 posts

Posted 24 July 2013 - 03:03 PM

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


Please: "This doesn't work..." is not a question.  We're not the government: we don't have anything to view what your doing on your computer.  Help us help you by asking a good question.

 

"Things needed to ask a "good" question:

  • A description of the context of your issue (Optional but can be a huge help in certain circumstances)
  • What is the actual problem (Mandatory)
  • What does the code actually do (Mandatory)
  • What you think the code should do / What you want the code to do (Mandatory)
  • What things have you tried so far (Optional, but missing it out just wastes your time and ours)
  • The actual code as you are running it - minus any personal information like Database Login Credentials (Mandatory - don't just post pseudo, the vast majority of issues are syntax and not logic)
  • As much info about your development environment as you can give - even if it's just letting us know you are using a hosting provider instead of a local install (Optional, but some questions can not be answered without it.)

#5 Psycho

Psycho

    Advanced Member

  • Moderators
  • 11,491 posts
  • LocationCanada

Posted 24 July 2013 - 03:13 PM

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.


The quality of the responses received is directly proportional to the quality of the question asked.

I do not always test the code I provide, so there may be some syntax errors. In 99% of all cases I found the solution to your problem here: http://www.php.net

#6 Muddy_Funster

Muddy_Funster

    Advanced Member

  • Members
  • PipPipPip
  • 3,383 posts

Posted 24 July 2013 - 03:28 PM

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


Please: "This doesn't work..." is not a question.  We're not the government: we don't have anything to view what your doing on your computer.  Help us help you by asking a good question.

 

"Things needed to ask a "good" question:

  • A description of the context of your issue (Optional but can be a huge help in certain circumstances)
  • What is the actual problem (Mandatory)
  • What does the code actually do (Mandatory)
  • What you think the code should do / What you want the code to do (Mandatory)
  • What things have you tried so far (Optional, but missing it out just wastes your time and ours)
  • The actual code as you are running it - minus any personal information like Database Login Credentials (Mandatory - don't just post pseudo, the vast majority of issues are syntax and not logic)
  • As much info about your development environment as you can give - even if it's just letting us know you are using a hosting provider instead of a local install (Optional, but some questions can not be answered without it.)

#7 LBernoulli

LBernoulli

    Newbie

  • New Members
  • Pip
  • 2 posts

Posted 29 August 2013 - 12:15 PM

And what about a mysqli solution?



#8 Psycho

Psycho

    Advanced Member

  • Moderators
  • 11,491 posts
  • LocationCanada

Posted 29 August 2013 - 01:16 PM

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.


The quality of the responses received is directly proportional to the quality of the question asked.

I do not always test the code I provide, so there may be some syntax errors. In 99% of all cases I found the solution to your problem here: http://www.php.net




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users