Countries list, how do I display all countries in a region together?

ryandward · April 19, 2011

Basically, I am creating a list of all the regions in the world, and I want it to display the countries that are inside each region. I am saving the data in a table that is composed of 2 columns, region name and country name. I have to do it this way, because there are some countries that fall into several regions.

The link is here http://128.123.242.201/countries.php. As you can see it is repeating regions (as it should) but I don't know how to combine all the countries in each region together, and only display each geographical region once.

<!DOCTYPE html>
<?php require "connect.php";?>
<html>
<head>
  <link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/themes/base/jquery-ui.css" rel="stylesheet" type="text/css"/>
  <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.5/jquery.min.js"></script>
  <script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/jquery-ui.min.js"></script>
  
  <script>
  $(document).ready(function() {
    $("#accordion").accordion();
  });
  </script>
</head>
<body style="font-size:75%;">


<?php

if(isset($_POST['submit'])) {


  $region = $_POST['region'];
  $country = $_POST['country'];
    
$query= "INSERT INTO regions (region,country) VALUES ('$region','$country')";

$result = mysql_query($query);

   if(!$result) {
        $confirmMsg = "There was a problem updating the record.";
    }
    elseif(mysql_affected_rows()==0) {
        $confirmMsg = "There was no record to update.";
    }
    else {
        $confirmMsg = "The record was successfully updated.";
    }


echo $confirmMsg; 
}

?>






<h3>
S<br />Add information:
<form method="post" action =""><br />

Country: <input type = "text" name="country" />
Region: <input type = "text" name="region" />
<br />
<input type = "submit" value = "submit" name="submit" />
</form>
</h3>

<div id="accordion">

<?php


//Delcare variable $info
$region = $country = NULL;
$query = "SELECT * FROM regions";
$result = (mysql_query($query)); 


while 	($row=mysql_fetch_assoc($result))
{
$region 	= "{$row['region']}"; 
$country	= "{$row['country']}";


$deleteimg = "http://www.veryicon.com/icon/png/System/Float/Delete.png";

$delete = "<a href=\"delete.php?region=".$region."&country=".$country."\"onclick=\"";
$delete .= "return confirm('Are you sure you want to delete?')\">";
$delete .= "<img src=".$deleteimg." height=\"20\"/></a>";


$accord = "<h3><a href=\"#\">$region</a></h3>";
$accord .= "<div>";
$accord .= "<p>";


$accord .= $delete.$country;

$accord .= "</p>";
$accord .= "</div>";



echo $accord;

}

?>


</div>


</body>
</html>

spiderwell · April 19, 2011

how about using 2 sql queries

query 1 grabs unique regions, and loop through that to pass the region back to the database and query 2 select all countries that match the region.

mikosiko · April 19, 2011

@spiderwell : even when your suggestion should work, those are no just 2 queries

spiderwell · April 19, 2011

What are you trying to say?

I suggested a way to solve your issue did I not?

mikosiko · April 19, 2011

@spiderwell : No offense was intended... just pointing that your suggestion is not efficient because imply multiples access to the DB (no just 2 queries as you said) if the OP has 100 regions your suggestion imply 101 queries to the DB instead of apply a different programing method and solve the OP issue with just 1 query... (1 access to the DB)... like p.e: (abstract... only the relevant part shown)

$oldRegion = '';
$deleteimg = "http://www.veryicon.com/icon/png/System/Float/Delete.png";

while ($row=mysql_fetch_assoc($result))   {
    	$region 	= $row['region']; 
    	$country	= $row['country'];
    
    if ($region != $oldRegion) {
        echo "<h3><a href=\"#\">$region</a></h3>";
        $oldRegion = $region;
    }	
    echo "<div><p><a href=\"delete.php?region=$region&country=$country\" onclick=\"return confirm('Are you sure you want to delete?')\"><img src=$deleteimg height=\"12\"/></a>$country</div>";
}

spiderwell · April 19, 2011

then you need to group by region or order by region.

then you need to make a check to see if region has changed as you loop through and close off the div and start a new one for the new region, when the region does change.

something roughly like

$currentregion = false;
while ($row=mysql_fetch_assoc($result))   {
if (!$currentregion) //first call
{
   echo "<h3>" . $row['region'] . "</h3>";
   $currentregion = $row['region'];
   echo "<div>" ;
}
elseif($currentregion != $row['region']) // region has changed
{
  echo "</div>"; //close last region div
  echo "<h3>" . $row['region'] . "</h3>";
   $currentregion = $row['region'];
   echo "<div>" ; 
}
echo $row['country'];


}// end DB loop
echo "</div>"; //close last div

ryandward · April 19, 2011

I got the multiple query method to work, but I am still trying to figure out how to make the oldRegion thing to work. I am sure it would work, I am just messing with all this accordion crapola!

ryandward · April 19, 2011

http://128.123.242.201/countries3.php

Here is a link to the last one you sent, Spiderwell

spiderwell · April 19, 2011

is it working how you want it too?

ryandward · April 19, 2011

Yeah it is working well. I am just very interested in this conversation about querying the database the fewest times. Thanks for your help guys

mikosiko · April 19, 2011

@spiderwell: Seems that you are answering to the wrong post... did you read my answer to you and the code that i posted?... doesn't looks like

mikosiko · April 19, 2011

@ryan: which code did you use finally?

spiderwell · April 19, 2011

yeh sorry mikosiko, i didnt realise you had replied, i thought it was ryandward.

the 2 solutions do give different results. mine puts all countries into 1 div tag, whereas yours puts each country into its own div tag, not sure which is correct for ryan

ryandward · April 19, 2011

I am using the very last one Spiderwell's modification of yours Mikosiko

spiderwell · April 19, 2011

and i found a better accordian script now for a site I have, so I might nick that for myself too!

mikosiko · April 19, 2011

ryan is using a Jquery accordion, therefore he need every region in a <h3> element and all the details associated with each region (countries) in one <div> element per region. everything wrapped in a <div id="accordion">

mikosiko · April 19, 2011

good... glad that both codes contributed to your solution.

ryandward · April 19, 2011

	

<?php

$region = $country = NULL;
$query = "SELECT DISTINCT  region FROM regions";
$result = (mysql_query($query)); 


while 	($row=mysql_fetch_assoc($result))
{
$region 	= "{$row['region']}"; 


$deleteimg = "http://www.veryicon.com/icon/png/System/Float/Delete.png";




$accord = "<h3><a href=\"#\">$region</a></h3>";
$accord .= "<div>";
$accord .= "<p>";

  $query=mysql_query("SELECT country FROM regions WHERE region='$region'");
  while($row=mysql_fetch_assoc($query)){
$country	= "{$row['country']}";


$delete = "<a href=\"delete.php?region=".$region."&country=".$country."\"onclick=\"";
$delete .= "return confirm('Are you sure you want to delete?')\">";
$delete .= "<img src=".$deleteimg." height=\"20\"/></a>";


$accord .= $delete.$country."<br />";


}
$accord .= "</p>";
$accord .= "</div>";


echo $accord;

}

This code looks the best, I am still trying to figure out where the divs when awry in the other coding.

ryandward · April 19, 2011

Apparently using the . to tie code together is messing with the divs for some reason... working on it now

ryandward · April 19, 2011

I have no idea why, I did have to remove all the .'s from the code for the divs to work properly..

<div id="accordion">

<?php


//Delcare variable $info
$region = $country = NULL;
$query = "SELECT * FROM regions";
$result = (mysql_query($query)); 




$currentregion = false;
while ($row=mysql_fetch_assoc($result))   {
if (!$currentregion) //first call
{
echo 	"<h3><a href=\"#\">{$row['region']}</a></h3>"; //echo first call

   $currentregion = $row['region'];
echo "<div>" ;
}
elseif($currentregion != $row['region']) // region has changed
{
  echo "</div>"; //close last region div
  echo "<h3><a href=\"#\">{$row['region']}</a></h3>"; //echo subsequent regions
   $currentregion = $row['region'];
   echo "<div><p>" ; 
}
echo $row['country']."<br />";



}// end DB loop
echo "</p></div>"; //close last div





?>

spiderwell · April 19, 2011

ryan you are doing what miko said not to do with the SQL, just use one SQL statement and order by region.

	

<?php


$currentregion = false;
$result = "SELECT * FROM regions ORDER BY region ASC;";	
while ($row=mysql_fetch_assoc($result))   {
if (!$currentregion) //first call
{
   echo "<h3>" . $row['region'] . "</h3>";
   $currentregion = $row['region'];
   echo "<div><p>" ;
}
elseif($currentregion != $row['region']) // region has changed
{
  echo "</p></div>"; //close last region div
  echo "<h3>" . $row['region'] . "</h3>";
   $currentregion = $row['region'];
   echo "<div><p>" ; 
}
echo "<a href=\"delete.php?region=".$row['region']."&country=".$row['country']."\" onclick=\"return confirm('Are you sure you want to delete?')\"><img src=\"http://www.veryicon.com/icon/png/System/Float/Delete.png\" height=\"20\"/></a>";
echo $row['country'] . "<br>";


}// end DB loop
echo "</p></div>"; //close last div

p.s. i havent checked above code for typo errors.

Sign In

Countries list, how do I display all countries in a region together?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information