Skip to content

Instantly share code, notes, and snippets.

View alancoleman's full-sized avatar
:octocat:

Alan Coleman alancoleman

:octocat:
6 followers · 13 following
View GitHub Profile
@alancoleman
alancoleman / case_and_group_for _count.sql
Last active December 21, 2015 22:58
Using CASE and GROUP to count and separate records into groups. This example uses income.
SELECT
(CASE
WHEN i.income < 5000 THEN '- £5000'
WHEN i.income BETWEEN 5000 AND 20000 THEN '£5,000 - £20,000'
WHEN i.income BETWEEN 20000 AND 30000 THEN '£20,000 - £30,000'
WHEN i.income BETWEEN 30000 AND 50000 THEN '£30,000 - £50,000'
WHEN i.income > 50000 THEN '+ £5000'
ELSE 'other'
END) AS income,
COUNT(*) AS users
@alancoleman
alancoleman / konsole_commands.txt
Last active December 19, 2016 11:47
A list of basic Konsole commands for reference
// Show the details of installed software, in this instance, Firefox
sudo apt-cache show firefox
sudo apt-cache show apache2
// Change the name of a file in a directory, using sudo because the directory is www
sudo mv alan_test.tx alan.txt
// Switch to root user
@alancoleman
alancoleman / strpos_url_argument.php
Last active December 19, 2015 22:38
Use strpos to to establish a string in a url and use as an argument
<?php
if( strpos( $_SERVER['REQUEST_URI'], 'my-url' ) !== false ) {
// Do something
}
@alancoleman
alancoleman / identify_uk_postcode_area.php
Last active December 18, 2015 19:19
Isolate just the area identifier from a UK postcode. So 'BA6 5TY' would result in 'BA' and 'N5 7TY' would result in 'N'
<?php
// Define postcode
$postcode = "BA6 5TY";
// Isolate first two characters of the postcode and replace any integer with nothing
$postcode_area = preg_replace("/[0-9]/","",substr($postcode, 0, 2));
@alancoleman
alancoleman / greatest_least_date_columns.sql
Last active December 18, 2015 00:39
There are three date columns in the table, each can contain any date or be null. This query will return all records between a date range when at least one of the date columns has a value, taking the highest or lowest value date from the row.
SELECT date1, date2, date3
FROM dates AS d
WHERE GREATEST(IFNULL(d.date1,MAKEDATE(2099,365)),
IFNULL(d.date2,MAKEDATE(2099,365)),
IFNULL(d.date3,MAKEDATE(2099,365))) <= '2013-06-03 16:08:46'
AND
LEAST(IFNULL(d.date1,MAKEDATE(1900,365)),
IFNULL(d.date2,MAKEDATE(1900,365)),
IFNULL(d.date3,MAKEDATE(1900,365))) >= '2008-04-01 16:08:46'
@alancoleman
alancoleman / strip_duplicate_array.php
Last active December 17, 2015 16:09
Function to walk through a multidimensional array and remove any successive duplicates based on an index
<?php
// Function to walk through a multidimensional array and remove any successive duplicates based on an index
function stripDuplicateHeader(&$stripArray, $index){ // Pass a reference of the array to be processed and the index to check
$newArr = array(); // Create an array in which to populate the processed arrays
foreach (array_reverse($stripArray) as $val) { // walk through the array to be processed backwards
$newArr[$val[$index]] = $val; //populate
}
$stripArray = array_reverse(array_values($newArr)); // remove duplicates
return $stripArray; // Return array
}
@alancoleman
alancoleman / from-array-to-db-using-implode.php
Last active December 17, 2015 12:39
From an array to a db table using the PHP implode function
<?php
$dbh = dbconnect(); // open db conn
$tblupdatedetailsql = array(); // define $tblupdatedetailsql array
foreach ($adGroups as $adGroup) { // populate new sql array using content array
$tblupdatedetailsql[] = '('.$tblupdateid.', 0, "'.mysql_real_escape_string($adGroup->name).'")';
}
// insert in the db using the implode function and the array
$insert = "INSERT INTO tbl (id,type,desc) VALUES".implode(',', $tblupdatedetailsql);
$statement = $dbh->prepare($insert); //prepare
@alancoleman
alancoleman / Indexed-array-without-key.php
Last active December 17, 2015 12:39
Indexed array without key
<?php
//Indexed arrays without key
$array = array("foo", "bar", "hallo", "world");
var_dump($array);
/*
Will output the following:
@alancoleman
alancoleman / CSS3-color-transition.css
Last active October 13, 2015 03:18
Link mouse over transition effect using CSS3
a {
color: #A32713;
text-decoration: none;
-webkit-transition: color .4s;
-moz-transition: color .4s;
-ms-transition: color .4s;
-o-transition: color .4s;
transition: color .4s;
}