hide repeated fields from different rows.

[turpentyne]

I've just run into a problem, when pulling location data using a link table.

There can be two entries in the link table, like so:

item_id | contentent | country | state
-------------------------------------
    123 |     1      |   2     |  20  
    123 |     1      |   2     |  21

When I echo these out (using the name, not the id), I'll get

location:

     north america... USA... missouri
     north america... USA... montana

I want to echo out my results as:

north america... USA... missouri, montana

then it can loop through if it is another continent. Same thing for countries. I might have

north america... USA... missouri, montana 
                    Mexico... Tamaulipas, oaxaca

Does anybody know how I might do this?

Right now, I have this:

<?php

 require ('databaseconnection.php');


$id = $_GET['id'];


   $result = mysql_query("SELECT DISTINCT continents.Name AS continent1,continent_regions.Name,country.Name AS country1, states.Name AS state1, plant_locations_link.plant_id
FROM plant_locations_link
JOIN continents ON plant_locations_link.continents = continents.ID
JOIN continent_regions ON plant_locations_link.continent_regions = continent_regions.ID
JOIN country ON plant_locations_link.country = country.ID
JOIN states ON plant_locations_link.state = states.ID
WHERE plant_locations_link.plant_id = $id");

if (!$result) {
    die(mysql_error());
}

        while ($row = mysql_fetch_assoc($result)) { 
?>



 <?php if ($row['continent1']!=""){ ?>
<?php echo $row['continent1']; ?><?php } ?>

 <?php if ($row['continentR1']!=""){ ?> ... 
<?php echo $row['continentR1']; ?><?php } ?> 

 <?php if ($row['country1']!=""){ ?>...
<?php echo $row['country1']; ?><?php } ?> 

    <?php if ($row['state1']!=""){ ?>...
<?php echo $row['state1']; ?><?php echo "</br>"; } ?> 


 <?php

 }
 ?>

[Atli]

You need to organize the data in your PHP code after you fetch it, before you try to echo it. Get it into a format that makes sense first and then try to use it. For example, if you do this:

$data = [];
while ($row = mysql_fetch_assoc($result)) {
    // If the continent is not listed in the data array, create it.
    if (!array_key_exists($row["continent"], $data)) {
        $data[$row["continent"]] = [];
    }

    // If the country does not exist in the continent, create it.
    if (!array_key_exists($row["country"], $data[$row["continent"]])) {
        $data[$row["continent"]][$row["country"]] = [];
    }

    // If the state is not listed in the country array, add it.
    if (!in_array($row["state"], $data[$row["continent"]][$row["country"]])) {
        $data[$row["continent"]][$row["country"]][] = $row["state"];
    }
}

The $data array will store all the incoming data in this format:

$data = [
    "North America" => [
        "USA" => [
            "New York",
            "Missouri",
            "Montana"
        ],
        "Mexico" => [
            "Tamaulipas",
            "Oaxaca"
        ]
    ]
]

Which means you can loop through the continents and countries individually, and print them into sections:

foreach ($data as $continent => $countries) {
    echo "<h1>{$continent}</h1>\n";
    foreach ($countries as $country => $states) {
        echo "<blockquote><h2>{$country}</h2>\n<ul>\n";
        foreach ($states as $state) {
            echo "<li>{$state}</li>";
        }
        echo "</ul></blockquote>";
    }
}

That should create the kind of result you are looking for.

[pritaeas]

You need to organize the data in your PHP code after you fetch it

Not necessarily. If the query is grouped on continent and continent_region, then you can use GROUP_CONCAT to concatenate the states.

[Atli]

True, but you'd still have to organize the continents and countries - even if that's just done in the print loop - and unless you just plan on printing the list of states as they are provided by the GROUP_CONCAT function, you'd have to explode and process them as well.

[turpentyne]

I got sidetracked on another issue, but back to this. I thought for a second that group_concat might solve the problem too, but no?

Right now, I'm still not having success with the solution you gave of putting things in arrays. It only seems to show the very last result, instead of categorizing. And for some odd reason, I get the actual variable names printing on the page first?? Here's where I'm at right now:

$result = mysql_query("SELECT DISTINCT continents.Name AS continent1,continent_regions.Name,country.Name AS country1, states.Name AS state1, plant_locations_link.plant_id
FROM plant_locations_link
JOIN continents ON plant_locations_link.continents = continents.ID
JOIN continent_regions ON plant_locations_link.continent_regions = continent_regions.ID
JOIN country ON plant_locations_link.country = country.ID
JOIN states ON plant_locations_link.state = states.ID
WHERE plant_locations_link.plant_id = $id");

if (!$result) {
    die(mysql_error());
}

    // while ($row = mysql_fetch_assoc($result)) {

    $data = array();
    while ($row = mysql_fetch_assoc($result)) {

    // If the continent is not listed in the data array, create it.
    if (!array_key_exists($row["continent"], $data)) {
    $data[$row["continent1"]] = $row;
    }

    // If the subcontinent does not exist in the continent, create it.
    if (!array_key_exists($row["continent_regions"], $data[$row["continent"]])) {
    $data[$row["continent_regions"]][$row["country1"]] = $row;
    }

    // If the country does not exist in the continent, create it.
    if (!array_key_exists($row["country"], $data[$row["continent"]])) {
    $data[$row["continent1"]][$row["continent_regions"]][$row["country1"]] = $row;
    }
    // If the state is not listed in the country array, add it.
    if (!in_array($row["state"], $data[$row["continent"]][$row["country"]])) {
    $data[$row["continent1"]][$row["continent_regions"]][$row["country1"]][$row["state1"]] = $row;
    }
    }    
?>



 <?php
 foreach ($data as $continent1 => $countries) {
    echo "<b>{$continent1}</b>\n";
    foreach ($countries as $country1 => $states) {
    echo "{$country1}\n<ul class='location_list'>\n";
    foreach ($states as $state1) {
    echo "<li>{$state1}, </li>";
    }
    echo "</ul>";
    }
    } ?> 

I've tried a few variations, but I'm not understanding something.

[diafol]

Your original question asked for an SQL - here's my take (note some names will be different):

SELECT c1.continent, r.region, c2.country, GROUP_CONCAT(s.state ORDER BY s.state) AS allstates FROM continent AS c1 
    INNER JOIN regions AS r ON c1.continent_id = r.continent_id 
    INNER JOIN countries AS c2 ON r.region_id = c2.region_id
    INNER JOIN states AS s ON c2.country_id = s.country_id
    GROUP BY continent, region, country ORDER BY continent, region, country

The fact that you want to concat the states makes it a little easier in the while loop, but you then give up the ability to format in the loop unless you explode on ','

$continent = '';
$region = '';
$country = '';

while($data = mysql_fetch_assoc($result)
{
    if($data['continent'] != $continent)
    {
        $continent = $data['continent'];
        $output .= "<h2>$continent</h2>";
    }
    if($data['region'] != $region)
    {
        $region = $data['region'];
        $output .= "<h3>$region</h3>";
    }
    if($data['country'] != $country)
    {
        $country = $data['country'];
        $output .= "<h4>$country</h4>";
    }
    $output .= "<p>{$data['states'];}</p>";
}

echo $output;

You can modify the html for yourself. Although using nested lists can make things a little more complicated.