Mapping series – an aside, a note on views in MySQL

While working on a project recently – a mapping one which required complex queries – I worked more with MySQL views. They undeniably make looking at complex data sources easier – you can see the aggregated data while masking the complexity of queries behind them. However they have 2 significant downsides: They do not actually…

Mapping series – Working with Algolia Places address autocompletion api

For a recent mapping project I implemented Algolia Places for address autocompletion┬áto turn an address into latitude and longitude for querying the database. In the past we’ve used Google Maps, but since this project wasn’t using Google Maps for the map display, using the Places API just for Geocoding is against their terms of use.…

New theme, dark mode, upcoming blogs

Trying to give myself a nudge to complete some 1/2 written posts, and write the unwritten ones, I decided to switch out the theme (again). I’ve gone with a very minimal theme called Susty by Jack Lenox. It’s a very interesting concept – bytes matter, not just to be the fastest loading or get the…

Exclude certain posts from search

Excluding posts (or custom post types) from search results when they meet certain criteria is easy in theory. Just use the pre_get_posts action to modify the WP_Query arguments used to get the results. However there are a couple of small pitfalls to watch out for. Below are brief explanations and snippets for excluding posts based…

Selecting title, meta and multiple term names with MySQL

I often run one off reports for clients directly from the MySQL database behind their (normally WordPress) websites. Most are straightforward, perhaps requiring data from the wp_posts and wp_postmeta tables. However it get's a lot more complicated if term data is needed too. This is because determining which terms belong to an object, what taxonomy…

Ignore spaces when group MySQL query results

Let's get a list of all duplicate postcodes (could be any string of course) out of a specific field in the database. This could be handled in PHP but, with a decently spec'd server especially, it's quicker to handle it in the database. The basic query looks like this: (this example is from WordPress but…

Case insensitivity on Apache servers

Regularly when we launch a new site we also put up a static scrape of the old site so that users can access content that has been removed from the new site. Commonly this is old news content that the client want’s to keep available for a transition period, but doesn’t want cluttering up the…

Avoiding permissions problems when creating Zip files in PHP

A typical PHP snippet to create a Zip file looks something like this: $zip = new ZipArchive(); $zipname = ‘package_name.zip’; if ( true === $zip->open( $zipname, ZipArchive::CREATE ) ) { $zip->addFromString( ‘file_name.txt’, $file_contents ); $zip->close(); header( ‘Content-Type: application/zip’ ); header( ‘Content-disposition: attachment; filename=’ . $zipname ); header( ‘Content-Length: ‘ . filesize( $zipname ) ); readfile(…