Accessing Google Page Insights in PHP

Google Page Insights is a required tool to have when analyzing the speed and usability of your site. As you may know these metrics influence how google ranks your page in search results. If you frequently make changes to your web site designs than it becomes mandatory to check the metrics after each change to make sure that the design changes has not affected the score in any negative way. If you have many pages to test than manual testing can quickly become cumbersome.

Thankfully there are libraries that you can use to automate this process. Once such is given in this post which allows you to get Google Page Insight metrics using PHP.
(more…)

Read More

Quick way to display CSV files as HTML tables

During database projects I frequently need to export certain views or intermediate results as CSV files. These are further used for importing into other databases. However, many times it is required to display the CSV files in web pages as HTML tables. As most times the data is generated on the fly it requires some JavaScript to turn the CSV data to HTML. One of my current favorite is the csv-to-html-table library. This allows you to easily integrate CSV files into your web pages.
(more…)

Read More

Data cleaning in PHP applications

One of the important tasks in any web application is proper sanitization and standardization of data. Any data stored in a database should be in a standardized format, specially data that comes from a variety of sources.

Scrubbers or data cleaners are an important part of the data transformation process. Whenever you are involved in some data import or export process, data scrubbers can help you clean and standardize your data elements before storing.
(more…)

Read More

Calculating descriptive statistics in MySQL

Descriptive statistics can be quite useful for simple analysis of records in a database. For example, to calculate average numbers of sales or products for a particular duration, or the Variance of sales for a month etc.

We can easily calculate standard descriptive statistic measures in MySQL such as MEAN, SUM, STANDARD DEVIATION, VARIANCE, MIN and MAX using built-in functions. Below is a sample student table with student id and scores which we will use for calculating descriptive statistical measures.
(more…)

Read More

Migrating legacy data to WordPress

With an increasing number of users migrating to WordPress, moving data from an existing CMS to WordPress has become an essential process in the transition. Although migrating small databases from another CMS can be done in a few hours, large databases can take days or even weeks.

In one of my recent endeavors of the same, I had to migrate a relatively small user database from an old CMS to WordPress. Although a small task, some points tripped me and took a little longer than planned. Below are some of the points and recommended tools to consider when migrating existing databases to WordPress.
(more…)

Read More

Exchanging columns in a CSV file

In one of my recent project requiring a CSV text file import, the data columns had to be in certain predefined positions for the program to work correctly. The import did not depend on field labels, which is obviously a wrong way to process data. It would not have been much of a concern had the CSV files been in the correct format. However, the CSV files I had had the columns in wrong positions, which required me to exchange the columns to their correct positions for the parent program to import them correctly. For a few files I’d have easily used a spreadsheet for the task, but with around 34 files it was going to be tedious. The task entailed me to write a small script which helped in the matter.
(more…)

Read More

Splitting large MySQL dump files

One of the frustrating things with working with MySQL is of importing large sql dump files. Either you get a ‘max execution time exceeded’ error from PHP or a ‘Max_allowed_packet_size’ from MySQL. In a recent task I needed to import a table of around a million records on a remote host, which quickly became an exercise in frustration due to various limitations on the server. SSH was of no help as changing the configuration files was restricted to the root user.

My last resort was to split the huge ‘INSERT’ statements into smaller size files. Manually doing the same is obviously time consuming and error prone; the only other solution is to write a small script to split the insert statements. This was to be a quick hack so the parsing code was to be of minimum complexity. Splitting a sql dump containing extended insert statements is somewhat complex so you need to have the dump file in a simple format – each insert statement should be on its own line as shown below.
(more…)

Read More

International Airport codes download

During a recent project I needed a database of IATA airport codes. Airports around the world are universally known by a unique three-letter code: the “International Air Transport Association (IATA) Location Identifier”. It is much easier for pilots, travel agents, frequent flyers, baggage handlers and anyone having anything to do with airlines to say and write LGA then the full “New York, NY – La Guardia Airport“.
(more…)

Read More