Echo array based on distinct key

Question

I have “ordered by” a database to be in ascending country order, then descending years. Each database record contains: countryname, year, details. There are many duplicate countries, but the years are different. For instance:

Albania,  2000, details  
Albania,  1965, details  
Croatia, 2014, details  
Croatia, 2003, details

Can’t figure out how to echo the array to get results like the following where country is on one line and years & details are listed below without duplicating the name of the country:

Albania  
    2000,  details  
    1965,  details  

Croatia  
    2014,  details  
    2003,  details

Seems like I need foreach distinct country, echo year and details?

Here is my php so far:

$result = mysql_query("SELECT country, year, details FROM studies ORDER BY country, year DESC ");  
    //output data from each row in db  
    while($row = mysql_fetch_array($result))  {  
echo "   Country:  " .$row['country']. "<br />      Year:  " .$row['year'].  "    Details:  ".$row['details']. "<br /><br /> ";  
}

Would appreciate any help, I'm stumped!

In the future, highlight code blocks and do ctrl+k and it will format them as code. — developerwjk
– developerwjk, Commented Feb 11, 2015 at 21:52
@Fred-ii-, GROUP BY is not enough; The OP needs to format their output as described. — Sablefoste
– Sablefoste, Commented Feb 11, 2015 at 21:59
Use nested while loop maybe ? First get distinct country name, display that value and as you are iterating through the country name, pull the year and other details for each country ? — Maximus2012
– Maximus2012, Commented Feb 11, 2015 at 22:05

Sablefoste · Accepted Answer · 2015-02-13 15:10:25Z

2

Try adding a country check:

$newcountry = '';
 while($row = mysql_fetch_array($result))  {
   if ($newcountry != $row['country']) {
     echo "Country:". $row['country']."<br />";
     $newcountry = $row['country'];
   }
  echo " Year:  " .$row['year'].  "    Details:  ".$row['details']. "<br /><br /> ";
}

This should work, because you have ordered your query by Country. This is critical, otherwise you should absolutely add a GROUP BY clause to your SQL.

EDIT: to add a <div> around the group, you simply would change the echo sequence, checking first to see if the country has already been set once. It would look like:

 $newcountry = 'undefined';
     while($row = mysql_fetch_array($result))  {
       if ($newcountry !='undefined' && $newcountry != $row['country']){
         echo '</div>'; // only add a closing div if a new country (and not 1st)
       }
       if ($newcountry != $row['country']) {
         echo "Country:". $row['country']."<br /><div class='countryDetail'>";
         $newcountry = $row['country'];
       }// added the start of the <div>
      echo " Year:  " .$row['year'].  "    Details:  ".$row['details']. "<br /><br /> ";
    }
    if ($newcountry != 'undefined') { //make sure at least one <div> is set
      echo "</div>"; // close the last <div>
    }

I added the class countryDetail to the div, so you can use this with toggle in your jQuery.

edited Feb 13, 2015 at 15:10

answered Feb 11, 2015 at 21:56

Sablefoste

4,2123 gold badges41 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ptrk Over a year ago

Minor improvement: $newcountry = $row['country']; can be put in the if block to avoid void assignements. Changing $newcountry makes sense only if it's different.

DianneP Over a year ago

This works perfect! However, for simplicity I didn't include html formatting requirements. Can't get a <div></div> to surround each group of years & details for the countries. I need it to show/hide with an arrow next to the country. I know the jquery works with plain html. No matter where I put the <div> in the echo statements, it surrounds each year & detail. Thanks, I'm almost there.

Sablefoste Over a year ago

Modified, with your <div> request. You could also wrap with jQuery, but I think this is easier to manage.

DianneP Over a year ago

Thank you so very much, this works perfect!! The details are now enclosed in a <div> & the jquery toggle works great.

Maximus2012 · Accepted Answer · 2015-02-11 22:16:54Z

You can use nested while loops. You might also want to use PDO/mysqli_ functions/prepared statements in place of mysql_ functions:

// get unique country list first
$sql1 = "SELECT DISTINCT(country) FROM studies ORDER BY country";
$result1 = mysql_query($sql1);

// iterate through result set of sql1
while($row1 = mysql_fetch_array($result1))   
{
     $country = $row1['country'];
     echo "<br>";      // new line
     echo $country;      

     // get year, details for each country
     $sql2 = "SELECT year, details FROM studies WHERE country = '$country' ORDER BY year DESC";
     $result2 = mysql_query($sql2);
     // iterate through result set of $sql2 
     while ($row2 = mysql_fetch_array($result2))
     {
          echo "<br>";      // new line
          echo $row2['year']. ", " . $row2['details']; 
     }
}

lcapra · Accepted Answer · 2015-02-11 22:28:02Z

0

loop party!

$rows = [];
while($row = mysql_fetch_array($result))  {
    $rows[ $row['country'] ] = [ $row['year'], $row['details'] ];
}

foreach($rows as $country => $row) {
  $details = array_map(function($items) { return sprintf("\t - %s: %s\n", $items[0], $items[1]) }, $row);
  echo sprintf("\n%s:\n %s", $country, $details);
}

answered Feb 11, 2015 at 22:28

lcapra

1,58018 silver badges20 bronze badges

Collectives™ on Stack Overflow

Echo array based on distinct key

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related