Saturday, August 27, 2005

Remove duplicate rows / entries from a mySQL database table

Here is a fairly quick and painless way to remove duplicate rows from a mySQL database table.

To remove the duplicates in mySQL 3.23 and above we will move the non duplicates into a temporary table, then delete the old table, and rename the temporary table to the old table to finish the circle.

First Create the temporary table and populate it with the values from the old table.

NOTE: As of mySQL 3.23, you can create one table from another by adding a SELECT statement at the end of the CREATE TABLE statement :: CREATE TABLE new_tbl SELECT * FROM orig_tbl;

SELECT * FROM oldtable WHERE 1 GROUP BY [column to remove duplicates from];

Now we have a newtable with all of the duplicates removed. So next we drop the old table (the one with all the duplicates still remaining)

DROP TABLE oldtable;

Now we just rename the newtable to the name of the oldtable so we don't break our whole code base!

RENAME TABLE newtable TO oldtable;

Thats it, duplicates removed. With newer versions of mySQL and with other database engines such as Oracle 9 you can remove duplicates in a single statement and step but this method will still work (using any changes in the SQL commands necessary for your engine).

Friday, August 26, 2005

Full USA Zip Code Database Download

Recently I needed a zip code database table for a project but had a horrible time finding one on-line. Seeing as how this is public data I was astounded at how difficult it was to find. Well finally I put together a csv file of all the USA Zip Codes and associated city and county information and have provided the full file, free to download, on my blog. You can click on the link below to download the zipped CSV file with all the zip code informaiotn. This CSV zip code file can be imported into many database programs. I used the DB Web Admin with Plesk to import it into a MYSQL database. The one problem I did find is that I believe the file has too many rows for an Excel Import.

Columns Included in the CSV Zip Code database file: Zip Code, City, State, Area Code, City Alias, City Alias Abbreviation, City Type, County name, State FIPS, County FIPS, Time Zone, Daylight Saving Status, Latitude, Longitide, and Elevation.

Click Here to Download the Zip Code CSV File

NOTE: Because this file includes not only the City but also the City Alias each zip code has multiple rows in the zip code table. Once you have the table set up you can always drill down the zip code data in any way you need. I personally removed all of the city aliases to trim down the table.

Sunday, August 21, 2005

Install Microsoft Money 2003 on Pocket PC 2003

Recently I had not used my HP IPAQ Pocket PC for a while and the battery died thus erasing all my programs and data, what a pain. Well I got most things working fine but again entered into the battle to install Microsoft Money 2003's Pocket PC version on my Pocket PC running Pocket PC 2003. I KNEW it could be done because I had it running before my pocket pc died but I also remembered that it required some voodo and magic. After finding one place with the walktrough incorrectly I found the correct one and vowed to blog it so I could always find it. Here you go:

This walkthrough has been found a couple of places but here it is for you!

Original Posting:

Follow these instructions and you should be able to install Money for the PPC (2003 version):

1. With the PPC in the cradle and ActiveSync running, start the installation of Pocket Money for Money 2003 on the desktop.

2. Go through the normal install routine, following the prompts on the desktop machine.

3. When the PPC pops up an error message, don't clear it. Instead, open File Explorer on the PPC. (I don't know if the error message needs to remain open,

but without having experimented, I suggest leaving it on screen for now.)

4. Browse to "My Device /Windows /AppMgr /Install" and tap-and-hold on the file that is in that folder. (This is the one caveat... your folder may end up

being different, but something tells me that this is the folder you should look in). The file should be obviously related to Pocket Money.

5. When the menu appears, choose copy.

6. Still using File Explorer on the PPC, browse to "My Device /Temp" and paste the file in this new folder.

7. Once the file appears in this new folder, single tap the file. The installation will begin on its own.

8. Close the error message from the original installation.

9. After you have successfully installed Money, with the PPC in the cradle, explore the PPC (use Explore from within ActiveSync).

10. Browse to "My Device /Windows" and look for "MoneyCE".

11. Right click on this file and choose "Create a shortcut".

12. Move this shortcut to "My Device /Windows /Start Menu /Programs" to finish the install.

13. Rename this new shortcut as "Microsoft Money".

14. Remove the PPC from the cradle. Check the settings for the Money Conduit in ActiveSync, place the PPC back into the cradle, and away you go.

At this point, it should be working like a charm. Money shows up in the uninstall listing on the PPC, but I actually don't know if it will work or not. Good luck!