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

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