Filtering or sorting for unique records

[Grant Holmes]

I have code that is successfully showing all the records (nearly 14,000) in my database. I figured this was the best place to start. Due to some imported records and such, we have some records that need to be "fixed", as it were. My biggest issue is the many ways countries have been added to the db. I know I can change all the "United states" to "US" through MySQL. But I have MANY variations of MANY countries and I don't know how to find records with unique country entries.

 

One I see how many ways people have entered the data, I can straighten it out. So my question is how I would alter my code (or tell me to start over!!) below to just show records that have unique entries in the country field?

 

<?php
include("dbinfo.inc.php");
mysql_connect(mysql,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * from birthdays";
$result=mysql_query($query);
$num=mysql_numrows($result); 
mysql_close();
?>
<CENTER>
<table border="1" cellspacing="2" cellpadding="3" class="sortable" width="90%">
<TR>
	<TH valign="top">From:</TH>
	<TH valign="top">City</TH>	
	<TH valign="top">St</TH>					
	<TH valign="top">Country</TH>				
</TR>
<?
$i=0;
while ($i < $num) {
$Active=mysql_result($result,$i,"Active");
$firstname=mysql_result($result,$i,"Contact_Info_FirstName");
$lastname=mysql_result($result,$i,"Contact_Info_LastName");
$city=mysql_result($result,$i,"Contact_Info_City");
$state=mysql_result($result,$i,"Contact_Info_State");
$zip=mysql_result($result,$i,"Contact_Info_ZipCode");
$country=mysql_result($result,$i,"Contact_Info_Country");
$Bfirstname=mysql_result($result,$i,"Birthday_Info_FirstName");
$Blastname=mysql_result($result,$i,"Birthday_Info_LastName");
$Bcity=mysql_result($result,$i,"Birthday_Info_City");
$Bstate=mysql_result($result,$i,"Birthday_Info_State");
$Bzip=mysql_result($result,$i,"Birthday_Info_ZipCode");
$Bcountry=mysql_result($result,$i,"Birthday_Info_Country");
$id=mysql_result($result,$i,"id"); 

?>
</form>
      <tr> 
            <td valign="top"><input type="hidden" name="ud_id" value="<? echo $id; ?>">
            <STRONG><? echo "$firstname $lastname"; ?></STRONG></TD>
            <TD valign="top"><? echo "$city"; ?></TD>
            <TD valign="top"><? echo "$state"; ?></TD>				   	   
            <TD valign="top"><? echo "$country"; ?></TD>	   		   
            </TD>
      </tr>
<?
++$i;
} 
echo "</table></CENTER>";
echo "<P>Total Records: $i</P>";
?>

[fnairb]

You may want to forgo the PHP and go right to the source with a one two punch.  (True, the second punch needs to be repeated for each replace but you get the idea)

 

1) Quick look at the each country entry and how many entries are of each

 

select Birthday_Info_Country, count(*) from birthdays group by Birthday_Info_Country;

 

2) When you know which countries you are want to replace (NOTE: MAKE SURE you include the proper where clause)

 

update birthdays set Birthday_Info_Country = 'Good Name' where Birthday_Info_Country = 'Bad Name';

 

 

 

My knee jerk reaction, and I mean no disrespect, was to let you know you are missing out on the power of the relational database.  Normalize the country (and probably city as well) out of the birthdays table and save yourself a lot of headaches.  However, that doesn't solve the problem you have now.  It is something you should investigate for future projects though.

 

 

[Grant Holmes]

fnairb

 

Relational would be great. But you understand the challenge.

 

I'll look into your suggestion, which I hear you saying just do that select in mySql?

[Grant Holmes]

fnairb,

 

I haven't had time to try your solution, but looking at your post, I'm not sure I understood it at first.

 

The point is that there are nearly 14,000 records. I don't want to "look" through those records. I need something to tell me all the unique entries.

[wobbit]

Can't you jsut use SQL to get all the unqiue countries?

 

If I remember correctly...

 

"SELECT DISTINCT country FROM birthdays"...

 

That should give you a complete unqiue list of countries.

[Grant Holmes]

Wobbit,

 

I know little about PHP, and NOTHING about MySQL queries!!

 

I tried, "SELECT DISTINCT Contact_Info_Country FROM birthdays"

 

MySQL said: "#1046 - No database selected"

 

Can you help me correct the query? The database is 'contacts', table is 'birthdays' and field is 'Contact_Info_Country'

[wobbit]

Hi Grant,

 

What you typed is correct but you hadn't selected your database.

 

To do this while in MySQL command line type 'USE contacts;' then you can do your query

 

"SELECT DISTINCT Contact_Info_Country FROM birthdays"

 

Wob.

[Grant Holmes]

That did it. I'd actually "tried too hard" to do the query. The Yahoo server this is on a BC version of PHPmyAdmin.

 

Anyhow, I'd tried browsing into the DB/table. This time I just did the query after selecting the DB.

 

Got the data I need. Thanks!

[fnairb]

Grant,

 

The query below will give you a unique list of countries but it also has a count of the number of times that exact sting is present.  That little piece of data may help you decide which version to use.

 

select Birthday_Info_Country, count(*) from birthdays group by Birthday_Info_Country;

 

 

If, for the sake of example your table was just countries and had the values:

 

US

US

UK

US

United States

UK

us

 

 

The query results would be something approximating....

 

US, 3

us, 1

UK, 2

United States, 1

 

[Grant Holmes]

Thanks for the great ideas guys!! I appreciate the help.