PHP - MySQL country, state and city | PHP, MySQL and Affiliate Marketing Technology

PHP - MySQL country, state and city

Thu, 08/26/2010 - 13:42 — russellharrower

Hi I am having a small issue, I need to stop it showing state names that it already has printed out once

                         ID	Name	CountryCode	District	Population
Sydney	AUS	                New South Wales	3276207
Melbourne	AUS	                Victoria	2865329
Brisbane	AUS	                Queensland	1291117
Perth	AUS	                        West Australia	1096829
Adelaide	AUS	                South Australia	978100
Canberra	AUS	                ACT	322723
Gold Coast	AUS	                Queensland	311932
Newcastle	AUS	               New South Wales	270324
Central Coast	AUS	        New South Wales	227657
Wollongong	AUS	        New South Wales	219761
Hobart	AUS	                Tasmania	126118
Geelong	AUS	                Victoria	125382
Townsville	AUS	                Queensland	109914
Cairns	AUS	                Queensland	92273

as you can see from the above the district for some of the cities aka name are the same,
So I wondering if there is anyone to only show it once?

Thanks

Thu, 08/26/2010 - 18:03 — dmorison

Hi Russell, Whilst there are

Hi Russell,

Whilst there are duplicated districts - ultimately name+district (at least in the example data above) is unique - so do you really want to filter on district alone? It would look like you are losing data by doing that...

Fri, 08/27/2010 - 03:32 — russellharrower

Yes, as I am generating a

Yes, as I am generating a drop down box automaticlly from this data to get the users state aka district.
Then I will run another PHP code to allow the user to select his/her city.

Fri, 08/27/2010 - 09:26 — dmorison

Hi Russell, I understand -

Hi Russell,

I understand - no problem, all you need to do is populate an array to hold already displayed districts, and only display if not set; e.g.

<?php
  $displayed = array();
  foreach($items as $item)
  {
    if (!$displayed[$item["district"])
    {
      // display item here

      // then set $displayed array so this district isn't displayed again
      $displayed[$item["district"]] = 1;
    }  
  }
?>

Sat, 08/28/2010 - 04:57 — russellharrower

maybe I am doing something

maybe I am doing something wrong, however it does not seem to print the results.

here is all the code

<select id="state">
<?
$stateCodeSearch = mysql_query("SELECT District FROM City WHERE CountryCode = '".$code."'");
while($stateCoderow = mysql_fetch_array($stateCodeSearch))
{
$items = $stateCoderow["District"];
$displayed = array();
  foreach($items as $item)
  {
    if (!$displayed[$item["district"]])
    {
	
	$displayed[$item["district"]] = 1;
	
	}
  }
	
?>


    <option value="<? print $displayed[$item];?>"><? print $displayed[$item];?></option>
   
<?
}
?>
</select>

Sat, 08/28/2010 - 08:11 — dmorison

Hi Russell, To access the

Hi Russell,

To access the result of a query by field names you need to use mysql_fetch_assoc() - but as you are using MySQL, there is a much easier way to do this - you can just use DISTINCT(Distrcit)! Have a go with;

<select id="state">
<?
$stateCodeSearch = mysql_query("SELECT DISTINCT(District) AS District FROM City WHERE CountryCode = '".$code."' ORDER BY Distrcit");
while($stateCoderow = mysql_fetch_array($stateCodeSearch))
{
  ?>
    <option value="<? print $stateCoderow["District"];?>"><? print $stateCoderow ["District"];?></option>
  <?
}
?>
</select>

Cheers,
David.

Sat, 08/28/2010 - 13:07 — russellharrower

tried ur code and it just

tried ur code and it just shows up blank

select a country and it will load the states, but it does not show

Sat, 08/28/2010 - 13:11 — russellharrower

found a work around by using

found a work around by using a simple group by