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 - 06: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 - 07:05 AM.


#2 Muddy_Funster

Muddy_Funster

    Advanced Member

  • Members
  • PipPipPip
  • 2,993 posts

Posted 24 July 2013 - 08:55 AM

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.


A simple password hash :

function makePass($word=''){
  $dbSalt = '$2a$07$'.substr(hash('whirlpool',$word),0,22);
  $dbPass = crypt($word, $dbSalt);
 return substr($dbPass,12);
}



My SQL/PHP Blog

#3 Psycho

Psycho

    Advanced Member

  • Gurus
  • 10,964 posts
  • LocationCanada

Posted 24 July 2013 - 09:25 AM

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
  • 2,993 posts

Posted 24 July 2013 - 10:03 AM

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


A simple password hash :

function makePass($word=''){
  $dbSalt = '$2a$07$'.substr(hash('whirlpool',$word),0,22);
  $dbPass = crypt($word, $dbSalt);
 return substr($dbPass,12);
}



My SQL/PHP Blog

#5 Psycho

Psycho

    Advanced Member

  • Gurus
  • 10,964 posts
  • LocationCanada

Posted 24 July 2013 - 10:13 AM

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
  • 2,993 posts

Posted 24 July 2013 - 10:28 AM

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


A simple password hash :

function makePass($word=''){
  $dbSalt = '$2a$07$'.substr(hash('whirlpool',$word),0,22);
  $dbPass = crypt($word, $dbSalt);
 return substr($dbPass,12);
}



My SQL/PHP Blog

#7 LBernoulli

LBernoulli

    Newbie

  • New Members
  • Pip
  • 2 posts

Posted 29 August 2013 - 07:15 AM

And what about a mysqli solution?



#8 Psycho

Psycho

    Advanced Member

  • Gurus
  • 10,964 posts
  • LocationCanada

Posted 29 August 2013 - 08:16 AM

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

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com