Formula for pricing buy X get X (buy 1 get 1 free) promos

python code:

each_price = ( (current_price * quantity) - (current_price * (int(quantity / (buyX + getX) * getX) ) ) ) / quantity

MySQL insert or update a random value from a set of given values

UPDATE my_table SET my_field = (SELECT sub.code FROM (SELECT '01' AS code
UNION SELECT '02'
UNION SELECT '53'
UNION SELECT '54'
UNION SELECT '55'
UNION SELECT '56') as sub
ORDER BY RAND() LIMIT 1)

MySQL search and replace unicode (hexidecimal) characters with HTML entities

Not really pretty, but this will replace Latin-1 Supplement and Latin Extended-A characters (plus permutations of quotes, dashes and apostrophes) with their corresponding HTML entities. NOTE: if you use this, copy the queries from the source code of this page, the browser is translating the entities. Query is operating on a column called DESCRIPTION.

UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A0'),' ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A1'),'¡');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A2'),'¢');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A3'),'£');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A4'),'¤');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A5'),'¥');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A6'),'¦');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A7'),'§');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A8'),'¨');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A9'),'©');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00AA'),'ª');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00AB'),'«');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00AC'),'¬');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00AD'),'­');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00AE'),'®');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00AF'),'¯');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B0'),'°');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B1'),'±');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B2'),'²');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B3'),'³');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B4'),'´');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B5'),'µ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B6'),'¶');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B7'),'·');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B8'),'¸');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B9'),'¹');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00BA'),'º');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00BB'),'»');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00BC'),'¼');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00BD'),'½');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00BE'),'¾');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00BF'),'¿');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C0'),'À');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C1'),'Á');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C2'),'Â');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C3'),'Ã');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C4'),'Ä');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C5'),'Å');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C6'),'Æ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C7'),'Ç');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C8'),'È');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C9'),'É');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00CA'),'Ê');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00CB'),'Ë');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00CC'),'Ì');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00CD'),'Í');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00CE'),'Î');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00CF'),'Ï');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D0'),'Ð');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D1'),'Ñ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D2'),'Ò');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D3'),'Ó');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D4'),'Ô');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D5'),'Õ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D6'),'Ö');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D9'),'Ù');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00DA'),'Ú');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00DB'),'Û');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00DC'),'Ü');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00DD'),'Ý');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00DE'),'Þ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00DF'),'ß');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E0'),'à');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E1'),'á');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E2'),'â');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E3'),'ã');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E4'),'ä');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E5'),'å');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E6'),'æ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E7'),'ç');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E8'),'è');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E9'),'é');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00EA'),'ê');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00EB'),'ë');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00EC'),'ì');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00ED'),'í');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00EE'),'î');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00EF'),'ï');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F0'),'ð');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F1'),'ñ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F2'),'ò');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F3'),'ó');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F4'),'ô');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F5'),'õ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F6'),'ö');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F7'),'÷');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F8'),'ø');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F9'),'ù');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00FA'),'ú');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00FB'),'û');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00FC'),'ü');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00FD'),'ý');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00FE'),'þ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0100'),'Ā');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0101'),'ā');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0102'),'Ă');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0103'),'ă');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0104'),'Ą');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0105'),'ą');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0106'),'Ć');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0107'),'ć');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0108'),'Ĉ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0109'),'ĉ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'010A'),'Ċ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'010B'),'ċ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'010C'),'Č');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'010D'),'č');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'010E'),'Ď');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'010F'),'ď');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0110'),'Đ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0111'),'đ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0112'),'Ē');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0113'),'ē');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0114'),'&Ebreve;');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0115'),'&ebreve;');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0116'),'Ė');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0117'),'ė');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0118'),'Ę');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0119'),'ę');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'011A'),'Ě');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'011B'),'ě');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'011C'),'Ĝ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'011D'),'ĝ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'011E'),'Ğ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'011F'),'ğ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0120'),'Ġ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0121'),'ġ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0122'),'Ģ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0124'),'Ĥ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0125'),'ĥ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0126'),'Ħ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0127'),'ħ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0128'),'Ĩ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0129'),'ĩ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'012A'),'Ī');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'012B'),'ī');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'012C'),'&Ibreve;');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'012D'),'&ibreve;');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'012E'),'Į');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'012F'),'į');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0130'),'İ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0131'),'ı');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0132'),'IJ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0133'),'ij');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0134'),'Ĵ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0135'),'ĵ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0136'),'Ķ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0137'),'ķ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0139'),'Ĺ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'013A'),'ĺ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'013B'),'Ļ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'013C'),'ļ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'013D'),'Ľ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'013E'),'ľ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0141'),'Ł');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0142'),'ł');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0143'),'Ń');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0144'),'ń');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0145'),'Ņ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0146'),'ņ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0147'),'Ň');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0148'),'ň');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'014A'),'Ŋ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'014B'),'ŋ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'014C'),'Ō');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'014D'),'ō');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'014E'),'&Obreve;');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'014F'),'&obreve;');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0150'),'Ő');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0151'),'ő');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0152'),'Œ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0153'),'œ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0154'),'Ŕ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0155'),'ŕ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0156'),'Ŗ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0157'),'ŗ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0158'),'Ř');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0159'),'ř');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'015A'),'Ś');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'015B'),'ś');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'015C'),'Ŝ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'015D'),'ŝ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'015E'),'Ş');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'015F'),'ş');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0160'),'Š');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0161'),'š');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0162'),'Ţ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0163'),'ţ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0164'),'Ť');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0165'),'ť');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0166'),'Ŧ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0167'),'ŧ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0168'),'Ũ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0169'),'ũ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'016A'),'Ū');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'016B'),'ū');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'016C'),'Ŭ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'016D'),'ŭ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'016E'),'Ů');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'016F'),'ů');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0170'),'Ű');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0171'),'ű');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0172'),'Ų');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0173'),'ų');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0174'),'Ŵ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0175'),'ŵ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0176'),'Ŷ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0177'),'ŷ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0178'),'Ÿ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0179'),'Ź');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'017A'),'ź');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'017B'),'Ż');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'017C'),'ż');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'017D'),'Ž');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'017E'),'ž');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D7'),'×');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0096'),'–');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0085'),'…');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'009A'),'š');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'008A'),'Š');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'2013'),'—');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'2014'),'–');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'2015'),'&mdash');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E2'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C3'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C2'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0080'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0082'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0083'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0000'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'009C'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'009D'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0093'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0094'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E2'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C3'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C2'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0080'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0082'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0083'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0000'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0099'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00BA'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B9'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0098'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'2018'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'201A'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'2019'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'2039'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'203A'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0092'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, '—','—');

Find all non-ascii characters in MySQl column

SELECT *
  FROM products 
 WHERE DESCRIPTION <> CONVERT(DESCRIPTION USING ASCII)

search and replace unicode characters for ASCII output in perl

I don't know why you would EVER need this :P but just in case, this handles most of the open/close quote and double quote issues that arise when you need to output to ASCII

 $string =~ s/[\x{00E2}|\x{00C3}|\x{00C2}]?[\x{0080}|\x{0082}|\x{0083}|\x{0000}]?[\x{0099}|\x{00BA}|\x{00B9}|\x{0098}]/'/ig;
 $string =~ s/[\x{00E2}|\x{00C3}|\x{00C2}]?[\x{0080}|\x{0082}|\x{0083}|\x{0000}]?[\x{009C}|\x{009D}|\x{0093}|\x{0094}]/"/ig;
 $string =~ s/\x{2018}/'/ig;
 $string =~ s/\x{201A}/'/ig;
 $string =~ s/\x{2019}/'/ig;
 $string =~ s/\x{2039}/'/ig;
 $string =~ s/\x{203A}/'/ig; 
 $string =~ s/\x{0092}/'/ig; 
 
 $string =~ s/\x{0093}/"/ig;
 $string =~ s/\x{0094}/"/ig;
 $string =~ s/\x{201C}/"/ig;
 $string =~ s/\x{201D}/"/ig;
 $string =~ s/\x{201E}/"/ig;

Array: remove empty, remove whitespace out of each slice

For dealing with arrays with IDs (that have been touched by humans somewhere along the way)

@array = grep /\S/, @array; # remove empty
s/\s$// for @array; # remove whitespace out of each slot

Ember.js stateful image loading component

Not sure how useful this is, but this uses javascript Image() and a promise to load an image. Allows you to set loading and error states.

import Ember from 'ember';

const {
    Component,
    get,
    set
} = Ember;

export default Component.extend({

    isLoading: true,
    isError: false,

    /* JavaScript Image Object used to do the loading */
    imageLoader: Ember.computed(function() {
        return new Image();
    },

    getImage(url, _this) {
        return new Ember.RSVP.Promise((resolve, reject) => {
            let img = _this.get('imageLoader');
            img.onload = function() {
                resolve(url)
            }
            img.onerror = function() {
                reject(url)
            }
            img.src = url
        });
    },

    didInsertElement() {
        this._super(...arguments);
        let _this = this;

        let src = get(_this, 'src');
        _this.get('getImage')(src, _this).then(success => {
            console.log("success", success);
            set(_this, 'isLoading', false);
        }).catch(error => {
            console.log("fail", error);
            set(_this, 'isLoading', false);
            set(_this, 'isError', true);
        })
    },

    willDestroyElement() {
        this._super(...arguments);
        let img = get(this, 'imageLoader');
        if (img) {
            img = img.onload = img.onerror = null;
            this.set('imageLoader', null);
        }
    }
});

Then in the template you could do something like:

{{#if isLoading}}
 
loading...
{{else}}
 {{#if isError}}
  
 {{else}}
  
 {{/if}}
{{/if}}

If the component is called "product-render" you could use it in parent template like:

{{product-render src=myImage}}

MySQL select records with timestamp in the previous day, no matter what time of day it is now

WHERE timestamp >= DATE_SUB(CONCAT(CURDATE(), ' 00:00:00'), INTERVAL 24 HOUR) AND timestamp < DATE_ADD(DATE_SUB(CONCAT(CURDATE(), ' 00:00:00'), INTERVAL 24 HOUR), INTERVAL 24 HOUR)

Create Self-Signed Cert on OSX and tell the OS to trust it

Not as easy as you might think. Let's say you wanted to create a self-signed cert for a local domain called "my.webtool"... First create a file called v3.ext with these contents:

authorityKeyIdentifier=keyid,issuer
basicConstraints=CA:FALSE
keyUsage = digitalSignature, nonRepudiation, keyEncipherment, dataEncipherment
subjectAltName = @alt_names

[alt_names]
DNS.1 = my.webtool
DNS.2 = localhost
DNS.3 = 127.0.0.1

Then run these commands. This assumes you have openSSL installed:

openssl genrsa -des3 -passout pass:x -out my.webtool.pass.key 2048
openssl rsa -passin pass:x -in my.webtool.pass.key -out my.webtool.key
rm my.webtool.pass.key
openssl req -new -key my.webtool.key -out my.webtool.csr
openssl x509 -req -days 1000 -in my.webtool.csr -signkey my.webtool.key -out my.webtool.crt -extfile v3.ext

Then install the .key and .crt files in whatever server you're running. THEN you have to tell your system to trust the certificate by importing it into your keychain AND change the "trust" settings on it. See http://www.accuweaver.com/2014/09/19/make-chrome-accept-a-self-signed-certificate-on-osx/ update! this appears to be a one-shot deal:

openssl req \
-newkey rsa:2048 \
-x509 \
-nodes \
-keyout yarr.mydev.com.key \
-new \
-out yarr.mydev.com.crt \
-subj /CN=yarr.mydev.com \
-reqexts SAN \
-extensions SAN \
-config <(cat /etc/ssl/openssl.cnf \
    <(printf '[SAN]\nsubjectAltName=DNS:yarr.mydev.com,IP:192.168.56.101')) \
-sha256 \
-days 3650

clean the apache cache nicely

/usr/local/apache/bin/htcacheclean -vn -p/var/cache/apache -l500M

Kinda rough regex for pattern matching and capturing apache log info

the IP is parsed into \2 \3 and \4

^(((25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)) \- \- (\[.*?\]) "(.*?)" (\d+) (\d+) "(.*?)" "(.*?)"$

Geocode a batch of addresses with perl and Google

I am working with this store locator and needed a way to geocode 7k addresses, so I wrote this quick-n-dirty script. Google only lets you geocode 2500 addresses a day. The script will start where it left off, then quit when you hit the query limit. So it feasibly could be run on a cron until all your addresses are coded.

#!/usr/bin/perl

use strict;
use DBI;
use Google::GeoCoder::Smart;

my $geo = Google::GeoCoder::Smart->new();

my $host =""; 
my $database =""; 
my $user =""; 
my $mysqlpassword ="";

my $dbh = DBI->connect("DBI:mysql:database=$database;host=$host","$user","$mysqlpassword",{'RaiseError'=>1}); 

 my $ctr= 0;
 my $sql = "SELECT * FROM sb_locations WHERE lat = \"\"";  
 my $sth = &db_query($sql);
 while (my $ref = $sth->fetchrow_hashref) {
        my ($num, $error, @results, $returntext) = $geo->geocode(
        "address" => stripChars($ref->{'address'}), 
        "city" => stripChars($ref->{'city'}), 
        "state" => stripChars($ref->{'state'}), 
        "zip" => stripChars($ref->{'postal'})
        );
        my $lat;
        my $lng;
        eval {
         $lat = $results[0]{geometry}{location}{lat};
        };
        eval {
         $lng = $results[0]{geometry}{location}{lng};
        };  
        print "id: $ref->{'id'} returntext?: $returntext error?: $error lat: $lat lng: $lng\n";
        if ($error eq 'OVER_QUERY_LIMIT') {
         last;
        }         
        if (($lat) && ($lng)) {
         $lat = $dbh->quote($lat);
         $lng = $dbh->quote($lng);
         my $upd_q = "UPDATE sb_locations SET lat = $lat, lng = $lng WHERE id = $ref->{'id'}";
         my $sth2 = &db_query($upd_q);
         $sth2->finish();
        }
        $ctr++;
        if (($ctr%10)==0) {
         print "sleeping\n";
         sleep 5;
        }
 }
 $sth->finish();
 undef ($sql);
 undef ($sth);

$dbh->disconnect();

sub stripChars {
        my($text) = @_;
        $text =~ s/^\s*//; # strip out leading space if there is one
        $text =~ s/\n/ /g; # strip carraige returns
        $text =~ s/\t/ /g; # strip tabs
        $text =~ s/\a/ /g; # strip carraige returns
  $text =~ s/"/'/g; # strip quotes and replace with single quotes
        $text =~ s/\s+/ /g; # strip repeating spaces and replace with one
        $text =~ s/[^[:ascii:]]+//g;
        return ($text);
 
} # end sub strip chars

sub db_query {
 my ($query) = @_;
 my $sth = $dbh->prepare("$query"); 
 #print $query;
 $sth->execute;
 #print $dbh->err; 
 my $err = $dbh->err;  
 my $errstr = $dbh->errstr;
 if ($err) { print "$err: $errstr on query $query"; }
 return $sth;
 
} # end sub db_query

grep mail.log for only sent emails

tail -1000 /var/log/mail.log | grep -E -o "\bto=<[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,6}\b" | grep -v '[DOMAINS YOU WANT TO EXCLUDE]'

regex to grab the last path of a url

.*/([^/]+*)$

Geocode thousands of addresses with perl

I'm using this jquery store locator and needed a way to geocode 7K addresses. Google only lets you code 2500 in a 24H hour period, so this will quit when the query limit is reached. When it runs again it will pick up where is left off. So it could theoretically be run on a cron until it's done.

#!/usr/bin/perl

use strict;
use DBI;
use Google::GeoCoder::Smart;

my $geo = Google::GeoCoder::Smart->new();

my $host =""; 
my $database =""; 
my $user =""; 
my $mysqlpassword ="";

my $dbh = DBI->connect("DBI:mysql:database=$database;host=$host","$user","$mysqlpassword",{'RaiseError'=>1}); 

 my $ctr= 0;
 my $sql = "SELECT * FROM sb_locations WHERE lat = \"\"";  
 my $sth = &db_query($sql);
 while (my $ref = $sth->fetchrow_hashref) {
        my ($num, $error, @results, $returntext) = $geo->geocode(
        "address" => stripChars($ref->{'address'}), 
        "city" => stripChars($ref->{'city'}), 
        "state" => stripChars($ref->{'state'}), 
        "zip" => stripChars($ref->{'postal'})
        );
        my $lat;
        my $lng;
        eval {
         $lat = $results[0]{geometry}{location}{lat};
        };
        eval {
         $lng = $results[0]{geometry}{location}{lng};
        };  
        print "id: $ref->{'id'} returntext?: $returntext error?: $error lat: $lat lng: $lng\n";
        if ($error eq 'OVER_QUERY_LIMIT') {
         last;
        }         
        if (($lat) && ($lng)) {
         $lat = $dbh->quote($lat);
         $lng = $dbh->quote($lng);
         my $upd_q = "UPDATE sb_locations SET lat = $lat, lng = $lng WHERE id = $ref->{'id'}";
         my $sth2 = &db_query($upd_q);
         $sth2->finish();
        }
        $ctr++;
        if (($ctr%10)==0) {
         print "sleeping\n";
         sleep 5;
        }
 }
 $sth->finish();
 undef ($sql);
 undef ($sth);

$dbh->disconnect();

sub stripChars {
        my($text) = @_;
        $text =~ s/^\s*//; # strip out leading space if there is one
        $text =~ s/\n/ /g; # strip carraige returns
        $text =~ s/\t/ /g; # strip tabs
        $text =~ s/\a/ /g; # strip carraige returns
  $text =~ s/"/'/g; # strip quotes and replace with single quotes
        $text =~ s/\s+/ /g; # strip repeating spaces and replace with one
        $text =~ s/[^[:ascii:]]+//g;
        return ($text);
 
} # end sub strip chars

sub db_query {
 
 my ($query) = @_;

 my $sth = $dbh->prepare("$query"); 
 #print $query;
 $sth->execute;
 #print $dbh->err; 
 my $err = $dbh->err;  
 my $errstr = $dbh->errstr;
 if ($err) { print "$err: $errstr on query $query"; }
 return $sth;
 
} # end sub db_query

Add transparent PNG to a JPG using ImageMagick Convert

convert shirt.jpg art.png -gravity center -composite -matte output.jpg

recursively search and replace ambersands (or any character) in filenames

Replaces the ampersands with underscores in any file ending in .jpg (all subdirs)

find . -type f -iname "*.jpg" -exec rename 's/&/_/' {} \;

Same thing, but on systems that don't have 'rename' (like OS X)

find . -name '*.jpg' -type f -exec bash -c 'mv "$1" "${1/\/\&/_/}"' -- {} \;

wget a site for caching purposes (spider it)

Sometimes you want to crawl a site so that it gets cached on its server. This will "spider" a site and not download content

wget -r -l6 --spider -D mysite.com http://mysite.com/

MySQL query to compare two COUNT results to get a percentage

SELECT
 keyed.keyed_codes AS "Entered Codes",
 totals.total_table AS "Total Codes",
 ROUND(((keyed.keyed_codes/totals.total_table)*100),2) AS "Percent Keyed"
FROM
(SELECT count(*) AS keyed_source FROM `table` WHERE code RLIKE "^[1-9]" AND timestamp BETWEEN '20150801000000' AND '20150831000000') AS keyed,
(SELECT count(*) AS total_table FROM `table` WHERE timestamp BETWEEN '20150801000000' AND '20150831000000') AS totals

Stop iTunes v12 from Expiring Podcasts

If you're a heavy podcast listener you've probably seen this iTunes message:

 "iTunes has stopped updating this podcast because you have not listened to any episodes recently. Would you like to resume updating this podcast?"

 iTunes stops downloading podcasts if you haven't listened to any of the last 5 downloaded episodes. I perhaps because bandwidth gets expensive? But it's annoying because sometimes you're traveling and not able to sync, or you occasionally listen to the podcast on Stitcher or some other podcatcher right on your phone. Doesn't mean you want them to stop downloading.

 This script http://dougscripts.com/itunes/scripts/ss.php?sp=updateexpiredpodcasts used to work, but somewhere in iTunes version 11 they wised up to it and it stopped working. Now they are actually checking the downloaded tracks to see if any are played.

 So, I wrote a little script that gives 'em what they want. It plays the first unplayed episode in each subscribed podcast, and then back-tracks to the beginning of the podcast so that when you go to listen you don't know the difference. In order for a track to be identified as "played" it appears it needs to have been playing for at least 15 seconds. While the script is running it sets the volume to 0. But it will return the volume to what it was previously set at when it's done. So you can run this via launchd or cron once a day, in the middle of the night.

 Yes, it's kludgey. I can't even say for sure that it works, as I just wrote it. I borrowed some of Doug's code, so credits to him.

UPDATE: This actually appears to work.  I've been running it as a cron job late at night for a couple weeks and haven't had the "expired podcast" issue.  Only weirdness I'm seeing is that the iTunes "messages" (little note that pops up to tell you when a new track is playing) sticks on the last track processed.

--check if itunes is running.  if not, activate it
set itunesOK to my itunes_is_running()
if itunesOK is false then
 tell application "iTunes"
  activate
 end tell
end if

tell application "iTunes"
 with timeout of 300 seconds
  
  --see if there's a currently playing track so we can return to it when done
  set playTrack to false
  if player state is playing then
   set aTrack to current track
   stop
   set playTrack to true
   set myMessage to "Running the StopExpired script, will return to your track shortly"
   set myReturn to my growlMessage(myMessage)
  end if
  
  --see what the volume is currently set at
  set curVol to (get sound volume)
  
  --turn the volume all the way down so what we're about to do is not audible
  set sound volume to 0
  
  set podcast_playlist to some playlist whose special kind is Podcasts
  
  -- filter multiple names
  set each_podcast to {}
  try
   set each_podcast to my ASCII_Sort(my get_discrete_list_of(get album of every track of podcast_playlist))
  end try
  
  --display the list (debugging only)
  --set selectedVoice to {choose from list each_podcast}
  
  -- loop thru each podcast
  if each_podcast is not {} then
   repeat with this_podcast in each_podcast
    
    --create a list of unplayed episodes of within each podcast
    set each_unplayed to {}
    try
     set each_unplayed to (every track of podcast_playlist whose album is this_podcast and unplayed is true)
    end try
    
    if each_unplayed is not {} then
     set this_unplayed to (item 1 of each_unplayed)
     
     --for debugging
     set myMessage to this_podcast & ": " & name of this_unplayed as string
     set myReturn to my growlMessage(myMessage)
     
     --download the episode, if it's not already
     
     try
      set url_track to (get URL track of this_unplayed)
      set myMessage to "DOWNLOADING " & name of this_unplayed & " URL: " & URL track as string
      set myReturn to my growlMessage(myMessage)
      download url_track
     end try
     
     
     try
      play this_unplayed
      delay 20
      set player position to 0
      stop this_unplayed
      --back track this_unplayed
      
     end try
     
     --why is this necessary? I don't know.  Sometimes the last track just keeps playing.
     stop
     
    end if
    
   end repeat
  end if
  
  --update all the podcasts
  updateAllPodcasts
  
  --return the sound volume to previous level
  set sound volume to curVol
  
  set myMessage to "StopExpired script complete"
  set myReturn to my growlMessage(myMessage)
  
  --if there was a track already playing, return to it
  if playTrack is true then
   play aTrack
   set playTrack to true
  end if
 end timeout
end tell

--if itunes was originally closed, then close it again
if itunesOK is false then
 tell application "iTunes"
  quit
 end tell
end if

to get_discrete_list_of(list1)
 script a
  property list1ref : list1
 end script
 set list2 to {}
 script b
  property list2ref : list2
 end script
 repeat with i from 1 to length of list1
  set this_item to item i of a's list1ref
  considering case
   if this_item is not "" and this_item is not in b's list2ref then set end of list2 to this_item
  end considering
 end repeat
 return b's list2ref
end get_discrete_list_of

on ASCII_Sort(my_list)
 set the index_list to {}
 set the sorted_list to {}
 repeat (the number of items in my_list) times
  set the low_item to ""
  repeat with i from 1 to (number of items in my_list)
   if i is not in the index_list then
    set this_item to item i of my_list as text
    if the low_item is "" then
     set the low_item to this_item
     set the low_item_index to i
    else if this_item comes before the low_item then
     set the low_item to this_item
     set the low_item_index to i
    end if
   end if
  end repeat
  set the end of sorted_list to the low_item
  set the end of the index_list to the low_item_index
 end repeat
 return the sorted_list
end ASCII_Sort

--subroutine showing messages in growl (preferably)
--and if no growl, default dialog with timeout
to growlMessage(myMessage)
 --show our output message
 -- Check if Growl is running:
 set isRunning to my growl_is_running()
 
 --Only display growl notifications if Growl is running:
 if isRunning = true then
  
  tell application "GrowlHelperApp"
   -- Make a list of all notification types:
   set the allNotificationsList to ¬
    {"Notification 1", "Notification 2"}
   
   -- Make a list of the default enabled notifications:
   set the enabledNotificationsList to ¬
    {"Notification 1"}
   
   -- Register the script with Growl
   -- using either "icon of application"
   -- or "icon of file":
   register as application ¬
    "StopExpired" all notifications allNotificationsList ¬
    default notifications enabledNotificationsList ¬
    icon of application "Script Editor"
   
   -- Send a notification:
   notify with name "Notification 1" title "StopExpired output" description myMessage application name "StopExpired"
  end tell
 else
  display dialog myMessage giving up after 1
 end if
end growlMessage

--sub checks if growl is running
on growl_is_running()
 tell application "System Events" to return (exists process "GrowlHelperApp")
end growl_is_running

--subroutine checks if itunes is running
on itunes_is_running()
 tell application "System Events" to return (exists process "iTunes")
end itunes_is_running