MySQL – finding counts of data by columns

Okay, the title may not be clear, but this is basically what I was trying to do, and I present here the script that achieved it.

During a data migration of a legacy Access database (actually, nine Access databases) to Sugar CRM, I wanted to be able to find any data that was missing. Looking at the numbers of rows migrated is only half the story.

Because the source data was spread over so many different databases, there was a complex set of mapping rules, to map the old database columns onto the new database. This was not a simple column to column mapping – some recoreds split out into multiple records. Some columns became flags in other tables or reference links between two tables. Due to this complexity, some source tables did not get mapped properly, so some columns were not complete. This left me with, for example, all the postal codes from databases 1 to 5, but no postal codes from databases 6 to 9. Trying to find these was proving a pain.

The script I came up with in the end, with some help from the fine people at Stack Overflow, can be downloaded below. It is a PHP5 script, is not complicated and hopefully not obscure in any way.

The script will loop through each table, then look through each column in each table and report the number of rows for which that column contains some data. The output is a simple HTML table for each MySQL table.

I hope it is useful. Have fun with it!

No comments yet.

Leave a Reply