nix, shell, perl, php, mysql and mac os x tips and tricks

Wednesday, June 18, 2008

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

Tuesday, June 17, 2008

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;
       }
   }

Monday, June 16, 2008

Sort a directory by filesize, ascending

ls -alSr

Thursday, June 12, 2008

Force install a perl CPAN module

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