Alan Coleman alancoleman

Web Dev, Dev Ops and Software Engineer. Code, music and books, in no particular order.

alancoleman / filter_email_on_domain.sql

Created April 17, 2014 11:56

Filtering email addresses on domains

AND (SUBSTRING_INDEX(SUBSTR(email, INSTR(email, '@') + 1),'.',1)) IN('yahoo', 'ymail', 'bt', 'rocketmail', 'msn', 'live', 'hotmail')

alancoleman / sort_rows_into_age_ranges_using_dob.sql

Created April 16, 2014 08:40

Sort rows into age ranges using date of birth

	SELECT

	(CASE
	WHEN u.dob BETWEEN DATE_SUB(CURDATE(),INTERVAL 80 YEAR) AND DATE_SUB(CURDATE(),INTERVAL 75 YEAR) THEN '80-75'
	WHEN u.dob BETWEEN DATE_SUB(CURDATE(),INTERVAL 75 YEAR) AND DATE_SUB(CURDATE(),INTERVAL 70 YEAR) THEN '75-70'
	WHEN u.dob BETWEEN DATE_SUB(CURDATE(),INTERVAL 70 YEAR) AND DATE_SUB(CURDATE(),INTERVAL 65 YEAR) THEN '70-65'
	WHEN u.dob BETWEEN DATE_SUB(CURDATE(),INTERVAL 65 YEAR) AND DATE_SUB(CURDATE(),INTERVAL 60 YEAR) THEN '65-60'
	WHEN u.dob BETWEEN DATE_SUB(CURDATE(),INTERVAL 60 YEAR) AND DATE_SUB(CURDATE(),INTERVAL 55 YEAR) THEN '60-55'
	WHEN u.dob BETWEEN DATE_SUB(CURDATE(),INTERVAL 55 YEAR) AND DATE_SUB(CURDATE(),INTERVAL 50 YEAR) THEN '55-50'
	WHEN u.dob < DATE_SUB(CURDATE(),INTERVAL 80 YEAR) THEN '80+'

alancoleman / select_children_turning_an_age.sql

Last active August 29, 2015 13:58

This query will figure out how many children will turn 2 years of age if the future birth date is known, it rolls forward in 2 month periods.

	SELECT
	CASE
	-- -- Child turning 2 within 2 month periods
	WHEN ue.baby_due_date BETWEEN DATE_SUB(NOW(), INTERVAL 16 MONTH) AND DATE_SUB(NOW(), INTERVAL 14 MONTH) THEN CONCAT(DATE_ADD(CURDATE(), INTERVAL 8 MONTH ), ' to ', DATE_ADD(CURDATE(), INTERVAL 10 MONTH ))
	WHEN ue.baby_due_date BETWEEN DATE_SUB(NOW(), INTERVAL 18 MONTH) AND DATE_SUB(NOW(), INTERVAL 16 MONTH) THEN CONCAT(DATE_ADD(CURDATE(), INTERVAL 6 MONTH ), ' to ', DATE_ADD(CURDATE(), INTERVAL 8 MONTH ))
	WHEN ue.baby_due_date BETWEEN DATE_SUB(NOW(), INTERVAL 20 MONTH) AND DATE_SUB(NOW(), INTERVAL 18 MONTH) THEN CONCAT(DATE_ADD(CURDATE(), INTERVAL 4 MONTH ), ' to ', DATE_ADD(CURDATE(), INTERVAL 6 MONTH ))
	WHEN ue.baby_due_date BETWEEN DATE_SUB(NOW(), INTERVAL 22 MONTH) AND DATE_SUB(NOW(), INTERVAL 20 MONTH) THEN CONCAT(DATE_ADD(CURDATE(), INTERVAL 2 MONTH ), ' to ', DATE_ADD(CURDATE(), INTERVAL 4 MONTH ))
	WHEN ue.baby_due_date BETWEEN DATE_SUB(NOW(), INTERVAL 24 MONTH) AND DATE_SUB(NOW(), INTERVAL 22 MONTH) THEN CONCAT(CURDATE(), ' to ', DATE_ADD(CURDAT

alancoleman / array_reminders.php

Created March 27, 2014 14:47

Array reminders - I've always found arrays and the various ways of accessing their contents more complicated than it actually is.

	<?php

	/*
	Accessing array elements with square bracket syntax ¶
	Array elements can be accessed using the array[key] syntax.
	*/

	$array = array(
	"foo" => "bar",
	42 => 24,

alancoleman / check_string_against_array.php

Created March 13, 2014 09:41

Check to see if a string contains any substring values from an array. The first instance will be returned with a message.

	<?php
	$searchstring = "Alan Coleman is Grrrrate!";
	$arraycheck = array("\*", "!", "@", ",", "%");


	if (preg_match('/'.implode('\|', $arraycheck).'/', $searchstring, $matches)) {
	echo("Fail, the string '{$matches[0]}' was found in the search string.");
	} else {
	echo("Pass, None of the strings in the array were found in the search string.");
	}

alancoleman / move_to_linode_shell_commands.shell

Last active August 29, 2015 13:57

Various shell commands used to move WordPress installs to a new server (Linode VPS)

	* Use rsync to move files from old server to local, run from local machine

	rsync -chavzP --stats [email protected]:/home/alancoleman/domains /home/alan/Documents/domain_files

	* Use wget to download the latest copy of WordPress

	wget http://wordpress.org/latest.tar.gz

	* Unpack WordPress download

alancoleman / multi_assoc_array_from_array.php

Last active January 2, 2016 09:49

Build a multidimensional associative array from an array. Simple, but caught me out for a moment.

	<?php

	// Declare new array
	$fieldsarrsql = array();

	// Loop through origional array
	foreach ($origarray as $fieldsarr) {
	// Populate new array with associated data
	$fieldsarrsql[] = array(
	'tablealias'=>$fieldsarr['tablealias'],

alancoleman / using _DATE_SUB_filter_dates.sql

Created December 9, 2013 11:46

Using DATE_SUB to filter all records within the last eight months

	WHERE date >= DATE_SUB(NOW(), INTERVAL 8 MONTH)

	-- Returns everything within the last 8 months

alancoleman / output_date_using_DATE_FORMAT.sql

Created December 9, 2013 11:34

Output date format using DATE_FORMAT

	DATE_FORMAT(date,'%Y-%m-%d') AS 'date_new_format'

	-- Returns 2013-12-08

alancoleman / email_domain_output_regex.sql

Created December 9, 2013 11:27

Output email column depending on domain using regex

	CASE
	WHEN email REGEXP '@msn\|@live\|@hotmail' THEN 0
	ELSE
	1
	END
	AS 'Mailer'