Category: Development

I’ve recently been doing some more work on my coppr|Distilleries to Visit website and have added Nearest Distilleries and Nearest Attractions sections where I show the nearest 20 distilleries or attractions to the one beign viewed.

To do this I loaded GPS co-ordinates to each page and then needed to work out how to calculate the distance between them.

Initially I found the ST_Distance MySQL function which seemed like it would do the job. A little more reading though suggested that it would calculate distance between two points on a flat surface, but ot on a (almost) sphere like the Earth.

ST_Distance_Sphere then seemed a good option. I wrote some code and uploaded the page to test and received an error that the function didn’t exist.

After doing some investigation, I spoke to my web host and discovered that they were not using MySQL, but rather used MariaDB which does not have that function.

After some more investigation on options, I came across an old post on using the Haversine formula to calculate distance. The post had sample code in PHP and MySQL with the latter being what I wanted.

The only real difference between the code on the blog and the below, is MySQL no longer allows the use of alias in a WHERE clause so I’ve changed it to a HAVING clause:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
$sql = 
	"SELECT
		T2.*
		,ROUND((((acos(sin((T1.latitude * pi()/180)) * sin((T2.latitude * pi()/180)) + cos((T1.latitude * pi()/180)) * cos((T2.latitude * pi()/180)) * cos(((T1.longitude- T2.longitude) * pi()/180)))) * 180/pi()) * 60 * 1.1515),2) AS DISTANCE 
	FROM 
		{table1} AS T1 
	INNER JOIN 
		{table2} AS T2
			ON
				T2.ID = T1.ID 
	HAVING 
		DISTANCE <= 100";

I’ve posted a couple of articles now on making MySQL backups using Cron, time-based scheduler, on Linux based hosting.

This is, currently, the last post on this subject. The examples so far have been on backing up single databases, but it is possible to backup multiple databases using one command.

The below example shows the syntax to make a compressed backup of all mySQL databases, with the sections to change highlighted:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
/usr/bin/mysqldump -u {username} -p'{password}' --all-databases | gzip -c > /home/{username}/backups/backup_{databasename}_$(date +\%Y\%m\%d\%H\%M\%S).sql.gz

The below example shows the syntax to make a compressed backup of multiple named mySQL databases, with the sections to change highlighted (any additional database names can be specified):

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
/usr/bin/mysqldump -u {username} -p'{password}' --databases {databasename} {databasename} | gzip -c > /home/{username}/backups/backup_{databasename}_$(date +\%Y\%m\%d\%H\%M\%S).sql.gz

Earlier this month I posted an article on making MySQL backups using Cron, time-based scheduler, on Linux based hosting.

Since then I’ve realised that I have two databases which are well over 1GB in size and have now looked into making compressed backups.

The below example shows the syntax to make a compressed backup of a mySQL database, with the sections to change highlighted:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
/usr/bin/mysqldump -u {username}-p'{password}' {databasename} | gzip -c > /home/{username}/backups/backup_{databasename}_$(date +\%Y\%m\%d\%H\%M\%S).sql.gz

This is only a little different to the uncompressed backup syntax, but results in a file of much smaller size.

As I mentioned in yesterdays post on making backups of mySQL databases, earlier this year I migrated from a Microsoft Windows based web hosting package to a Linux based one. This meant I had to learn a few new ways of doing things, such as backing up databases or files, on a regular repeating basis.

This is accomplished on Linux web hosts using the Cron time-based scheduler. I needed to create two types of cron jobs; one to backup databases and one to backup files. Yesterdays post was on the databases and todays is on backing up files.

The syntax for backing up a files using Cron jobs will largely be the same on all web hosts.

The below example shows the syntax to backup a files, with the sections to change highlighted:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
/bin/tar -czvf /home/{username}/backups/backup_{sitename}_$(date +\%Y\%m\%d\%H\%M\%S).tar.gz /home/{username}/{foldername}

Earlier this year I migrated from a Microsoft Windows based web hosting package to a Linux based one. This meant I had to learn a few new ways of doing things, such as backing up databases or files, on a regular repeating basis.

This is accomplished on Linux web hosts using the Cron time-based scheduler. I needed to create two types of cron jobs; one to backup databases and one to backup files.

The syntax for backing up a MySQL database using Cron jobs will largely be the same on all web hosts.

The below example shows the syntax to backup a mySQL database, with the sections to change highlighted:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
/usr/bin/mysqldump -u {username} -p'{password}' {databasename} > /home/{username}/backups/backup_{databasename}_$(date +\%Y\%m\%d\%H\%M\%S).sql