Grant Holmes Posted February 29, 2008 Share Posted February 29, 2008 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>"; ?> Quote Link to comment Share on other sites More sharing options...
fnairb Posted February 29, 2008 Share Posted February 29, 2008 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. Quote Link to comment Share on other sites More sharing options...
Grant Holmes Posted February 29, 2008 Author Share Posted February 29, 2008 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? Quote Link to comment Share on other sites More sharing options...
Grant Holmes Posted February 29, 2008 Author Share Posted February 29, 2008 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. Quote Link to comment Share on other sites More sharing options...
wobbit Posted February 29, 2008 Share Posted February 29, 2008 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. Quote Link to comment Share on other sites More sharing options...
Grant Holmes Posted February 29, 2008 Author Share Posted February 29, 2008 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' Quote Link to comment Share on other sites More sharing options...
wobbit Posted February 29, 2008 Share Posted February 29, 2008 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. Quote Link to comment Share on other sites More sharing options...
Grant Holmes Posted February 29, 2008 Author Share Posted February 29, 2008 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! Quote Link to comment Share on other sites More sharing options...
fnairb Posted February 29, 2008 Share Posted February 29, 2008 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 Quote Link to comment Share on other sites More sharing options...
Grant Holmes Posted February 29, 2008 Author Share Posted February 29, 2008 Thanks for the great ideas guys!! I appreciate the help. Quote Link to comment 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.