Link to home
Start Free TrialLog in
Avatar of Crazy Horse
Crazy HorseFlag for South Africa

asked on

loop parent record followed by children, parent and children etc

I have seen a few questions like this and variations of this but I am still confused as how to go about doing this. I have different dealers in different regions and want to list them by region in one long list.

West Coast
dealer 1
dealer 2
dealer 3

East Coast
dealer 1
dealer 2
dealer 3

And so on...

I have a database table with a list of regions and another table with a list of dealers.

The dealer table has a column which contains the region id from the regions table so I can join the tables.

I tried this:

$stmt = $link->prepare("SELECT `region_name`, `dealer_name` FROM `dealer_region` as `dr` INNER JOIN `dealers` as `d` ON dr.`id` = d.`region_id` GROUP BY `region_name`");
$stmt->execute();
$result = $stmt->get_result();
$numRows = $result->num_rows;
if($numRows > 0) {
    while($row = $result->fetch_assoc()) {
        $dealer_name = $row['dealer_name'];
        $region_name = $row['region_name'];
        echo $region_name . "<br />" . $dealer_name . "<br />";
    }
}

$stmt->close();

Open in new window


But this only results in one dealer being shown under each region instead of all.
SOLUTION
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Exactly.  Thanks for posting example of what I meant.
Avatar of Crazy Horse

ASKER

Thank you!