Connecting PHP to MySQL on Bluemix

Most of the PHP I write runs on Bluemix - it's IBM self-service cloud, and since I work there, they pay for my accounts :) There are a bunch of databases you can use there, mostly open source offerings, and of course with PHP I like to use MySQL. Someone asked me for my connection code since it's a bit tricky to grab the credentials that you need, so here it is. Continue reading

MySQL 5.7 Introduces a JSON Data Type

There's a new JSON data type available in MySQL 5.7 that I've been playing with. I wanted to share some examples of when it's useful to have JSON data in your MySQL database and how to work with the new data types (not least so I can refer back to them later!)

MySQL isn't the first database to offer JSON storage; the document databases (such as MongoDB, CouchDB) work on a JSON or JSON-ish basis by design, and other platforms including PostgreSQL, Oracle and SQL Server also have varying degress of JSON support. With such wide adoption as MySQL has, the JSON features are now reaching a new tribe of developers. Continue reading

From MySQL to MailChimp via CSV

Don't you hate disclaimers? I do, but before I do anything else, I must ask that you don't use the techniques below unless you are emailing responsibly.

Today I needed to pull email addresses for people who had signed up to a thing out of MySQL and into MailChimp so that I could actually email them about the thing. MySQL actually has a very cute feature for exporting the results of an SQL query as a CSV file, which I had to look up to remember how to do it. It goes something like this: Continue reading

SQL JOINing a Table to Itself

Getting two sets of information from one table in a select statement often leads people to write subselects, but it really doesn't matter that this is the same table twice, we can just give it a new alias and treat it as if it were a different table. This is one of those techniques where, once you've seen it, it's really obvious, but until that point it can be very confusing. I explained this to someone else recently, so I thought I'd capture it here in case it's helpful to anyone else.

Consider that tried-and-tested example: employees and managers. Here's the staff table from the database (today's imaginary data isn't particularly imaginative, sorry):

mysql> select * from staff;
| id | first_name | last_name | manager_id |
|  1 | Hattie     | Hopkins   |          4 |
|  2 | Henry      | Hopkins   |          4 |
|  3 | Harry      | Hopkins   |          5 |
|  4 | Helen      | Hopkins   |       NULL |
|  5 | Heidi      | Hopkins   |          4 |
|  6 | Hazel      | Hopkins   |          1 |
6 rows in set (0.00 sec)

Continue reading

Inner vs Outer Joins on a Many-To-Many Relationship

Someone will probably tell me that this is an elementary-level topic, but I got some good questions regarding joins from my most recent ZCE class students, so I thought I'd put down the examples that I used to explain this to them. Being able to join with confidence is a key skill, because it means that you can refactor and normalise your data, without worrying about how hard something will be to retrieve.
Continue reading

Explaining MySQL's EXPLAIN

The MySQL explain plan is a great tool to help developers and database administrators to improve the performance of specific queries happening against a database. It is very easy to use, but its output can be confusing, so I thought I'd show a very simple example. Continue reading

PHPMyAdmin Designer View

This week I've been using phpMyAdmin for what feels like the first time in years. I'm happier at the command line, but needed some graphical representation of information and easy ways to export example queries for the book I'm working on. I noticed that phpMyAdmin now has a Designer tab, which shows relationships between tables and allows you to define them.

Continue reading

Gearman Priorities And Persistent Storage

I have been writing a bit about Gearman lately, including installing it for PHP and Ubuntu, actually using it from PHP and also how I use persistent storage with Gearman. I'm moving on to look at adding jobs of different priorities.

I use Gearman entirely as a point to introduce asynchronous-ness in my application. There is a complicated and image-heavy PDF to generate and this happens on an automated schedule. To do this, I use the GearmanClient::doBackground method. This inserts a priority 1 job into my queue.

Using the doHighBackground() and the doLowBackground() methods insert jobs into the queue and checking out my persistent storage I see that the priorities work like this:

priority method
0 doHighBackground()
1 doBackground()
2 doLowBackground()

Gearman works out which task is the next highest priority and will hand it to the next available worker - which means that I can set my automated reporting lower priority than the reports requested by real live people wanting them now, and everyone is happy!