Jump to content

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

ryandward

By ryandward
in PHP Coding Help

Recommended Posts

ryandward Newbie

ryandward

Posted
Posted

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>

mikosiko Newbie

mikosiko

Posted
Posted

@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 Member

spiderwell

Posted
Posted

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

 

spiderwell Member

spiderwell

Posted
Posted

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 Newbie

ryandward

Posted
  • Author
Posted 

	

<?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 Newbie

ryandward

Posted
  • Author
Posted

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 Member

spiderwell

Posted
Posted

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.

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.