Select and recurse thru an entire category tree in mysql (this is 5 levels deep)

SELECT
root.category_code AS root_category_code,
root.category_name AS root_category_name,
sub1.category_code AS sub1_category_code,
sub1.category_name AS sub1_category_name,
sub2.category_code AS sub2_category_code,
sub2.category_name AS sub2_category_name,
sub3.category_code AS sub3_category_code,
sub3.category_name AS sub3_category_name,
sub4.category_code AS sub4_category_code,
sub4.category_name AS sub4_category_name
FROM categories_loop AS root
LEFT JOIN categories_loop AS sub1 ON sub1.category_parent = root.category_code
LEFT JOIN categories_loop AS sub2 ON sub2.category_parent = sub1.category_code
LEFT JOIN categories_loop AS sub3 ON sub3.category_parent = sub2.category_code
LEFT JOIN categories_loop AS sub4 ON sub4.category_parent = sub3.category_code
WHERE root.category_parent = '0'
ORDER BY 
root_category_code, 
sub1_category_code, 
sub2_category_code, 
sub3_category_code,
sub4_category_code

Show a table structure, with comments in MySQL

SHOW FULL COLUMNS FROM myTable

Set auto-incement value in MySQL

ALTER TABLE tbl AUTO_INCREMENT = 1000;

Start/Stop MySQL on OS X Leopard

/Library/StartupItems/MySQLCOM/MySQLCOM [start|stop|restart]

search and replace text in files on the command line

find . -name "*.php" -print | xargs sed -i 's/foo/bar/g'

use '-maxdepth 1' before -name if you want to restrict to current directory

run X programs on your mac (say, on a parallels linux host or remotely)

Run this on the mac side:

ssh -X user@host

Find and delete duplicate records in MYSQL

DELETE t2 from table AS t1, table AS t2 WHERE t1.field = t2.field AND t2.id < t1.id

encode mp3s on the fly

I use this to record audio programs (voice quality). This gives fairly
small files, which I like because it makes it possible to save a LOT of
sound on a cd, and I find that the quality is satisfactory for me.



Here is the command:

sox -t ossdsp -w -s -r 44100 -c 1 /dev/dsp -t raw - | lame --nores -b 16
-x -m m - $(date +"/home/david/test--%a%d%b%Y_%I-%M%p.mp3") &

Change the number 16 to something else, like 32 to get better quality.
Like this. (These examples produce mono.)

sox -t ossdsp -w -s -r 44100 -c 1 /dev/dsp -t raw - | lame -b 32 -x -m m -
$(date +"/home/david/audio-temp/--ChangeMyName--%a%d%b%Y_%I-%M%p.mp3") &

This part of the command:
$(date+"/home/david/test--%a%d%b%Y_%I-%M%p.mp3")
produces a file name with a date and time extension as part of it.



For stereo, try this:

sox -t ossdsp -w -s -r 44100 /dev/dsp -t raw - | lame -t -r -x -h -b 160
-s 22.05 -q 5 --nores --strictly-enforce-iso - $(date
+"/home/david/test_%a%d%b%Y_%I-%M%p.mp3")

or

sox -t ossdsp -w -s -r 44100 /dev/dsp -t raw - | lame -t -r -x -h -b 128
-s 22.05 -q 5 --nores --strictly-enforce-iso - $(date
+"/home/david/test_%a%d%b%Y_%I-%M%p.mp3") 

New way to get all This American Life episodes

Do this in the directory you want to download them into. Adjust numbers.

counter=318;  while [  $counter -lt 366 ]; do echo "http://audio.thisamericanlife.org/jomamashouse/ismymamashouse/$counter.mp3" | xargs wget; let counter=counter+1; done;

MySQL Select the maximum row in each group

SELECT f.id AS id, f.client AS client, f.customer_id AS customer_id, f.bill_email AS bill_email
FROM (
   SELECT client, max(id) AS top_id
   FROM activity GROUP BY client
) AS x INNER JOIN activity AS f ON f.client = x.client and f.id = x.top_id;

Another way to find installed perl modules

find `perl -e 'print "@INC"'` -name '*.pm' -print

Regex to match a price with or without the decimal

\d+\.?\d{0,2}?

command to kill a process by name

kill `ps -axwwwww | grep MyAPP | grep -v grep | awk '{print $1}'`

Applescript to close all open applications

tell application "System Events" to set the visible of every process to true

tell application "System Events" to set theApps to (name of every process whose visible is true and name is not "Finder" and name is not (my name as text))

repeat with theApp in theApps
    tell application theApp to quit
end repeat



Applescript to unmount disk image


tell application "Finder"
    if exists "MyDisk" then
        do shell script "hdiutil detach \"/Volumes/MyDisk\""
    end if
end tell



Another way:


tell application "Finder"
    if (exists the disk "local_home_backup") then
        eject "local_home_backup"
        delay 2
    end if
end tell

MySQL use CONCAT and UPDATE together in mysql to add to an existing string

UPDATE products SET SHIPPING = CONCAT('+', SHIPPING) WHERE SHIPPING != "" AND SHIPPING REGEXP '^[^\+]'

install perl module at the command line with CPAN

perl -MCPAN -e 'install Bundle::CPAN'

Group records by month in mysql

SELECT
count(*) as REQUESTS,
DATE_FORMAT(date,"%a %b %e, %Y") as DATE,
date as DATE2,
MONTH( date ) as "MONTH OF THE YEAR"
FROM requests
WHERE date BETWEEN '2007-10-01' AND '2008-06-17'
GROUP BY "MONTH OF THE YEAR"
ORDER BY DATE2 ASC

Perl subroutine to validate e-mail addresses

    sub is_valid_email ($) {
       my ($addr) = @_;
       my $atext = qr/[A-Za-z0-9\!\#\$\%\&\'\*\+\-\/\=\?\^\_\`\{\|\+\~]/;
       my $dot_atom_text = qr/$atext+(\.$atext+)*/;

       my $no_ws_ctl_char = qr/[\x01-\x08\x0b\x0c\x0e-\x1f\x7f]/;
       my $qtext_char = qr/([\x21\x23-\x5b\x5d-\x7e]|$no_ws_ctl_char)/;
       my $text = qr/[\x01-\x09\x0b\x0c\x0e-\x7f]/;
       my $qtext = qr/($qtext_char|\\$text)*/;
       my $quoted_string = qr/"$qtext"/;

       my $quotedpair = qr/\\$text/;
       my $dtext = qr/[\x21-\x5a\x5e-\x7e\x01-\x08\x0b\x0c\x0e-\x1f\x7f]/;
       my $dcontent = qr/($dtext|$quotedpair)/;       
       my $domain_literal = qr/\[(${dcontent})*\]/;

       if ( $addr =~ /^($dot_atom_text|$quoted_string)\@($dot_atom_text|$domain_literal)$/ ) {
               return 1;
       } else {
               return 0;
       }
   }

Sort a directory by filesize, ascending

ls -alSr

Force install a perl CPAN module

perl -MCPAN -e "CPAN::Shell->force(qw(install IO::Socket::SSL));"