ryandward Posted April 19, 2011 Share Posted 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> Quote Link to comment https://forums.phpfreaks.com/topic/234178-countries-list-how-do-i-display-all-countries-in-a-region-together/ Share on other sites More sharing options...
spiderwell Posted April 19, 2011 Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/234178-countries-list-how-do-i-display-all-countries-in-a-region-together/#findComment-1203629 Share on other sites More sharing options...
mikosiko Posted April 19, 2011 Share Posted April 19, 2011 @spiderwell : even when your suggestion should work, those are no just 2 queries Quote Link to comment https://forums.phpfreaks.com/topic/234178-countries-list-how-do-i-display-all-countries-in-a-region-together/#findComment-1203632 Share on other sites More sharing options...
spiderwell Posted April 19, 2011 Share Posted April 19, 2011 What are you trying to say? I suggested a way to solve your issue did I not? Quote Link to comment https://forums.phpfreaks.com/topic/234178-countries-list-how-do-i-display-all-countries-in-a-region-together/#findComment-1203663 Share on other sites More sharing options...
mikosiko Posted April 19, 2011 Share Posted 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>"; } Quote Link to comment https://forums.phpfreaks.com/topic/234178-countries-list-how-do-i-display-all-countries-in-a-region-together/#findComment-1203667 Share on other sites More sharing options...
spiderwell Posted April 19, 2011 Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/234178-countries-list-how-do-i-display-all-countries-in-a-region-together/#findComment-1203675 Share on other sites More sharing options...
ryandward Posted April 19, 2011 Author Share Posted 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! Quote Link to comment https://forums.phpfreaks.com/topic/234178-countries-list-how-do-i-display-all-countries-in-a-region-together/#findComment-1203678 Share on other sites More sharing options...
ryandward Posted April 19, 2011 Author Share Posted April 19, 2011 http://128.123.242.201/countries3.php Here is a link to the last one you sent, Spiderwell Quote Link to comment https://forums.phpfreaks.com/topic/234178-countries-list-how-do-i-display-all-countries-in-a-region-together/#findComment-1203679 Share on other sites More sharing options...
spiderwell Posted April 19, 2011 Share Posted April 19, 2011 is it working how you want it too? Quote Link to comment https://forums.phpfreaks.com/topic/234178-countries-list-how-do-i-display-all-countries-in-a-region-together/#findComment-1203680 Share on other sites More sharing options...
ryandward Posted April 19, 2011 Author Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/234178-countries-list-how-do-i-display-all-countries-in-a-region-together/#findComment-1203682 Share on other sites More sharing options...
mikosiko Posted April 19, 2011 Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/234178-countries-list-how-do-i-display-all-countries-in-a-region-together/#findComment-1203683 Share on other sites More sharing options...
mikosiko Posted April 19, 2011 Share Posted April 19, 2011 @ryan: which code did you use finally? Quote Link to comment https://forums.phpfreaks.com/topic/234178-countries-list-how-do-i-display-all-countries-in-a-region-together/#findComment-1203685 Share on other sites More sharing options...
spiderwell Posted April 19, 2011 Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/234178-countries-list-how-do-i-display-all-countries-in-a-region-together/#findComment-1203687 Share on other sites More sharing options...
ryandward Posted April 19, 2011 Author Share Posted April 19, 2011 I am using the very last one Spiderwell's modification of yours Mikosiko Quote Link to comment https://forums.phpfreaks.com/topic/234178-countries-list-how-do-i-display-all-countries-in-a-region-together/#findComment-1203688 Share on other sites More sharing options...
spiderwell Posted April 19, 2011 Share Posted April 19, 2011 and i found a better accordian script now for a site I have, so I might nick that for myself too! Quote Link to comment https://forums.phpfreaks.com/topic/234178-countries-list-how-do-i-display-all-countries-in-a-region-together/#findComment-1203689 Share on other sites More sharing options...
mikosiko Posted April 19, 2011 Share Posted 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"> Quote Link to comment https://forums.phpfreaks.com/topic/234178-countries-list-how-do-i-display-all-countries-in-a-region-together/#findComment-1203692 Share on other sites More sharing options...
mikosiko Posted April 19, 2011 Share Posted April 19, 2011 good... glad that both codes contributed to your solution. Quote Link to comment https://forums.phpfreaks.com/topic/234178-countries-list-how-do-i-display-all-countries-in-a-region-together/#findComment-1203694 Share on other sites More sharing options...
ryandward Posted April 19, 2011 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/234178-countries-list-how-do-i-display-all-countries-in-a-region-together/#findComment-1203695 Share on other sites More sharing options...
ryandward Posted April 19, 2011 Author Share Posted April 19, 2011 Apparently using the . to tie code together is messing with the divs for some reason... working on it now Quote Link to comment https://forums.phpfreaks.com/topic/234178-countries-list-how-do-i-display-all-countries-in-a-region-together/#findComment-1203696 Share on other sites More sharing options...
ryandward Posted April 19, 2011 Author Share Posted 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 ?> Quote Link to comment https://forums.phpfreaks.com/topic/234178-countries-list-how-do-i-display-all-countries-in-a-region-together/#findComment-1203698 Share on other sites More sharing options...
spiderwell Posted April 19, 2011 Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/234178-countries-list-how-do-i-display-all-countries-in-a-region-together/#findComment-1203703 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.