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

Martini App with Ajax, JSON, and User Sessions

Previously, I have created a really simple web app with Go Lang and Martini that communicated with MySQL to retrieve a list of posts, an individual post, and allowed creation of a post. Now, we are going to handle updates and deletion, transform responses to use JSON for client side Ajax requests, and implement the user relation: user login, logout, managing sessions and authentication, as well as, retrieving posts by user. Supporting PUT (update) and delete requests with Ajax and JSON was pretty trivial (thanks to Martini) but evolving the app to handle users properly with good validation was much more work then I originally expected. Still, Go does make the code easy to understand and Martini contributed utilities have been a great help. Lets start.

GoLang and Martini Web Application
Read more

Simple App with Go, Martini, Gorp and MySQL

Last week I saw a video on the Martini web framework for Go and it inspired me to check it out. Looking at the performance of Go is even more inspiring and today I decided to learn the Go language and write a really simple app with Go, Martini, and MySQL. I started using the native SQL package for Go but quickly adopted gorp as it looks like a really clean API to talk to the database. I have also leveraged the binding and render Martini utilities to abstract more of the code out of my main go file. The app will create a table in mysql, create some sample posts, show a homepage with these posts, show an individual post view, and allow the end user to create their own post. I will also dig into error handling, validation, converting timestamps, and properly using layout template in the render utility for good SEO.

Sample Martini App

Read more

WordPress on VPS with Nginx and PHP APC

It was time to pay the yearly fee for shared hosting and I decided to check out some VPS options. Performance was not really a problem as WordPress caching plugins do a good job but I wanted more control of the stack and VPS pricing is not that far off from shared hosting. DigitalOcean plans start at 5 dollars and you get 1 Core with 512MB Memory, 20GB SSD Disk, and 1TB of Transfer. You can setup a droplet in 1 minute and you can pay month to month. No wonder they are doing so well, it makes sense to just try them out. They also have great documentation, API, and an easy to use control panel. Here are some details on how I moved my WordPress blog from shared hosting to VPS and made it really fast with Nginx, PHP APC, and Super Cache.
Read more

WordPress Plugin with Ajax and MySQL

I wanted to follow up from my last post on building a WordPress Plugin Admin Page and write about using Ajax calls with WordPress plugins. It is very common nowadays to use XMLHttpRequest to move data from the front-end to the back-end and WordPress supports this very well. Consider a contact form on your blog, when someone hits submit you could initiate an Ajax request to your plugin and process the data. Today, I will show how to use Ajax with a WordPress plugin and I will build a complete Quick Contact WordPress plugin that stores a form submission to a new database table which is displayed for quick viewing on a new admin page.
Read more

Creating a WordPress Plugin Admin Page

If you are building a WordPress plugin, there is a high chance that you want to create a backend page, perhaps a place for Admin settings or options. This post will cover how to do that and I will also build a WP Plugin which has an Admin page that interacts with the WordPress database. The plugin will render a mySQL table in a HTML table with dynamic JavaScript features like sorting, searching, etc. I will show how to include specific JavaScript and CSS files for the plugin Admin page and how to interact with WordPress mySQL tables. The cool think about a plugin admin page is that you can use it test all kinds of WordPress Database queries.
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

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

Reverse Geocoding with MySql and MaxMind

Reverse geocoding is the process of translating the geo location of a point (latitude and longitude values) into a readable address or place name. Google has the Geocoding API which includes Address Loookup and I have previously written about using Google for Reverse Geocoding. Here is an example of Google Reverse Geocoding, and here is an example of a reverse geocoding on a Google Map. This is a good tutorial explaining how to use Google’s GeoCoding API, but what about other reverse geocode services and options?

There are a few other geocoding APIs like Bing’s Find a Location by Point, MapQuest’s Reverse GeoCode Example, ArcGIS reverse geocode method, AfriGIS Reverse Geocoding API, Open Street Map reverse geocoding query, and CloudMate’s Reverse GeoCoding Method. Personally, I would go with GeoNames Web Services as it is available under Creative Commons Attribution License and you can even download their database. If you are already using MaxMind to translate IP addresses into place names, you could also leverage that database as it has city names with latitude and longitude values. But, how do you query such a database to get city names with some proximity of one geo coordinate?
Read more