Phil 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…
I’m starting this series with the simplest aspect – a MySQL query to return rows based on radius from a point. This is well trodden territory, although not always explained clearly. The maths of the query is all done for you, all you need is to decide if you are using miles of kilometres, and…
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.…
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…
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…
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…
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…
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…
Advanced Custom Fields (ACF) is a great WordPress plugin for adding custom meta fields. It has a very useful relationship field that can be used to denote a connection from one post to another – importantly this is a one-way relationship. When you are on PostA you can generate a list of all the posts…
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(…