For many programming languages, it's pretty easy to figure out which Connector to use. Java has Connector/J, if you're using C# or ASP you'll opt for Connector/.NET: the clue is in the name.

For PHP however, things aren't quite so straightforward. If you want to write a PHP application that communicates with a MySQL database, you have a choice of PHP extensions that you can use: mysql, mysqli, and PDO_MySQL.

We can simplify that list right away by discounting the mysql extension. This is old and was intended for use with MySQL versions before 4.1. It does not support many of the things that you can take for granted with the other two, such as improved MySQL authentication protocols or the ability to create prepared statements to prevent against SQL injection. It was dropped from PHP 7. In fact, you would have to jump through a couple of hoops just to get it to work with a later version of MySQL. So don't bother!

The mysql …

While it was always technically possible to store JSON in MySQL database columns, it was never a particularly attractive option. JSON data is just text, so any string type of sufficient length will take it quite happily. However, getting your JSON into the database using this method is one thing, but getting it back out again in any useful format had to be the responsibility of your application.

All that changed in MySQL 5.7.8 with the introduction of a native JSON data type and a range of useful built-in functions that made JSON a first-class citizen of MySQL.

In this post, we'll have a look at some of the functions you can use to store JSON data. Later posts will cover retrieving and otherwise manipulating that data.

Imagine that we are an online retailer, selling a range of electronics equipment. We sell anything from top of the range TVs to toasters. Clearly all these items will have things in common: they will be …

I used to work for a company specializing in Geographical Information Systems, and I still get excited about using computers to interact with geospatial data. Once a field that interested only geography geeks, GIS has now become mainstream, with just about every mobile app offering some kind of location awareness.

Whereas many such applications use some kind of third-party API to work with spatial data, with MySQL you can do this right in the database itself. MySQL provides a number of spatial data types which let you create and store simple geometric shapes like points, lines, and polygons that represent features like cities, roads, and countries respectively, together with their location in the real world.

Once you have created these geographical features in the database, you can use MySQL to understand the relationship between them. For example, many applications provide a "find my nearest..." capability, to locate things you …