How to insert or update multiple items in MySQL and WordPress?

You have a table with some data, lets say it is user information with emails, names, ages, etc. Now you need to insert 10 more rows of data but you don’t know if the 10 new rows are for new users or if they are attributes for users you already have in the database. This is a very common scenario where you need to figure out if you should update or insert.
Read more

Using MAX and CASE to pivot MySQL data stored in EAV Model

Last week, I covered pivoting tabular data in JavaScript. But, often times, data is not stored like a CSV or excel sheet in a database. When flexibility is needed to cover potential future changes, like adding many new columns or removing old ones, we often see the EAV model being used to store data.

Consider the previous example of storing car data in rows with columns: id, make, color, and age. If you are just adding more rows, this works well. But, if you will be adding an uncertain number of new columns in the future (model, size, etc), or removing columns, it might make sense to use the EAV model.
Read more

FastPivot – Process and Shift JSON data with JavaScript

Imagine a table of data, perhaps car data with columns: ID, Model, Make, Color, Age, etc. How do you process this data so that you can answer some simple questions or visualize it with JavaScript charts? Some questions, like “how many cars are red?” can be processed by looking at a count of rows that meet your criteria (rows where column color is red).

Other questions, like “what is the distribution of colors in your data set?”, requires shifting or pivoting the data, as you want to evaluate a specific column instead of rows. Charting will often need this data pivot and doing it optimally is crucial if you are working with a large amount of data on the client side.
Read more

Google Maps, DataTables, and FaceBook Places API

As more APIs allow CORS, we can create some really cool integrations without any server side code. Google Maps API is an obvious one but I was surprised to see successful Ajax calls to the FaceBook Graph API which made me think about building and integrating these services. I was scraping FaceBook for Places, or geo locations, so it only makes sense to start with a world map where you can search, click, or drag markers to retrieve any latitude and longitude coordinates for your geo search.

Next, these geo coords are sent to my facebook function that initiates a graph search for places near the provided geo location. The response drops markers on the Google Map, similar to Google’s own Places API and examples. But, I also wanted to visualize this dataset in more details and used the jQuery DataTables plugin to make the API data available for easy viewing, searching, and filtering. You can get more data for a specific place by clicking on an id, which initiates another place API call and drops the JSON response in a modal window with code highlighting.

Google and Facebook for Geo Places on a Map and Table
Read more

Over 2000 D3.js Examples and Demos

Here is an update to the 1000 D3 examples compilation and in addition to many more d3 examples, the list is now sorted alphabetically. Most of the D3 examples in this list come from this excel list but I also added some updates and my examples to push the list over 2K. Examples are really helpful when doing any kind of development so I am hoping that this big list of D3 examples will be a valuable resource. Bookmark and share with others. Here is the huge list of D3 demos:
Read more

Useful SQL Queries and Commands

At some point you will need to store data in a database and this blog post should help you get familiar with a SQL database like mySQL (there are other SQL databases like PostgreSQL). I want to cover some basic concepts like setting up the database, specifying proper structure or schema, and playing with tables, columns, and rows. I will focus mostly on SELECT queries but also cover INSERT, UPDATE, DELETE, plus some math functions and regular expressions. I feel like learning SQL seems scary at first so I am hoping that this quick guide will alleviate some of these fears. I will use SQLfiddle (website for testing and sharing SQL queries) and the concept of a blog to work through some common mysql examples and database concepts.
Read more

List of Countries and Capitals

I have previously shared some frustrations with aggregating a good list of countries and capitals. The problem is that the word country is a bit arbitrary and used to represent everything from sovereign states, various dependencies, special sovereign lands, uninhabited islands, and other entities. Adding to this, we have self-declared sovereign states like Somaliland that are unrecognized by most organizations so it’s hard to tell how you want to define that and which capital city to use. Moreover, there are countries that claim the same capital city (Jerusalem), islands with rotating capitals (Tokelau), and large geographical areas with no capital (Antarctica). So, here is my take on aggregating a complete list of countries and capitals of the world.
Read more

Internet and Website Trends with HTTParchive

In my last web performance tools article I have mentioned Ilya Grigorik’s Bigqueries forum where people can share and discuss interesting queries on the HTTParchive. These queries can give us some insight into internet trends and the state of the web. Today, I wanted to share two of my favorite queries from that forum and follow up with some of my own queries to identify some interesting website trends. I have decided to execute my queries in MySQL with the latest imported HTTParchive dump. This is mostly because I am constantly running into limits with using the HTTPArchive database on Google BigQuery.
Read more

Database Indexes Simplified

What is a database index? Understanding database indexes and DB optimization can be hard and confusing. I wanted to see if I can provide a simple explanation of what a DB index is, how it works, and how database indexes can be used. A good analogy for a database index could be an index in a book. If you are looking for a specific section in a book, you can either flip through every page (full table scan in database world) or find the section in the index and get a page number (pointer). Let’s consider a simple example: a table “people” with 2 columns (name and age) and we are using this table to store millions of user names with their appropriate age.
Read more

Geo Data Resources

You might have recently heard about Obama’s executive order to open government data and this made me want to dedicate a post that aggregates various sources for geographical data. The US Government’s Data.gov website currently has over 200,000 datasets and I am impressed at the available formats and filtering capabilities. There are over 400 data sets on next.data.gov. The health data community has over 800 data sets and the U.S. Census Bureau has a really large collection of maps and data available to explore.

Adding to this, we have the CIA World Factbook which has a lot of relevant country information but not in an open standards format. My last post highlighted how you could use CasperJS to scrape and transform that data into CSV or JSON files. But, let’s explore non-government data sets and geo resources:
Read more