CODEWITHSUNDEEP
phpmysqlarraysdatabasekey-value
DianneP
DianneP

Reputation: 13

Echo array based on distinct key

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!

Upvotes: 1

Views: 95

Answers (3)

Sablefoste
Sablefoste

Reputation: 4192

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.

Upvotes: 2

lcapra
lcapra

Reputation: 1550

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

Upvotes: 0

Maximus2012
Maximus2012

Reputation: 1819

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']; 
     }
}

Upvotes: 0

Related Questions