recurse thru a category structure and count items in categories with perl and mysql

Loop thru the categories table (must have category_code AND category_parent),
and inside the loop call the subroutine. sub db_query is just a simple query wrapper.

# loop thru the categories and count all prods underneath
my $prodcount=0;
my $cat_loop_q = "SELECT category_code,category_parent FROM $categories";
my $sth = &db_query($cat_loop_q);
while (my $ref = $sth->fetchrow_arrayref) {
 $prodcount = &countTree($$ref[0]);
 my $update_q = "UPDATE $categories SET count = '$prodcount' WHERE category_code = '$$ref[0]'";
 my $update = &db_query($update_q);
 $prodcount=0;
}
sub countTree {
 my ($root) = shift; 
 my $drill_q = "SELECT category_code FROM $categories WHERE category_parent = '$root'";
 #print "$drill_q\n";
 my $sth = &db_query($drill_q);
 my $rc = $sth->rows;
 if ($rc>0) {
  while (my $ref = $sth->fetchrow_arrayref) {
   &countTree($$ref[0]);
  }
 } 
 my $sql_q = "SELECT 
  a.category_name,
  a.category_code,
  a.category_parent,
  COUNT(DISTINCT b.skuid) 
  FROM $categories AS a, $products_to_categories AS b 
  WHERE a.category_code=b.category_code 
  AND a.category_code='$root'
  GROUP BY a.id";
  
 my $sth = &db_query($sql_q);
 my ($name,$parent,$code,$count);
 while (my $ref = $sth->fetchrow_arrayref) {
  $name=$$ref[0];
  $code=$$ref[1];
  $parent=$$ref[2];
  $count=$$ref[3];
  $prodcount=$prodcount+$count;
  
 }
 return($prodcount);
 
}

MySQL to show an entire category tree

SELECT a.category_name AS parent_name,
b.category_name AS child_name, 
a.category_code as a_category_code, 
a.category_parent AS a_category_parent, 
b.category_code as b_category_code, 
b.category_parent AS b_category_parent
FROM categories_raw_loop AS a, categories_raw_loop AS b 
WHERE a.category_code = b.category_parent
ORDER BY a.category_code;

MySQL Load data local infile

LOAD DATA LOCAL INFILE "/full/path/file" INTO TABLE [table] FIELDS TERMINATED BY ';' (col1,col2);

Select most popular items in MySQL

SELECT count( unoptioned_skuid ) as count , unoptioned_skuid as skuid
FROM orders_products
GROUP  BY unoptioned_skuid 
ORDER BY count DESC

Burn an ISO to CD from command line

cdrecord -v -pad speed=1 dev=1,2,0 src.iso  

Fun with tar and gzip

recursively backup files and directories, excluding some:

tar -czvf /home/site/backup.tar.gz /home/site/cgi-bin --exclude=mysubdir

append more files/dirs to an existing archive:

tar -r --file=/home/site/backup.tar /home/site/more_stuff

don't forget to gzip it when you're done!

gzip /home/site/backup.tar

list the contents of a tar.gz


gzip -dc /home/site/backup.tar.gz | tar tvf -

Find installed perl modules

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

Get the size of a file within a shell script

file_size=$(ls -l "path/file" | awk '{ print $5 }')
echo $file_size

Create a .tgz archive

tar -czf [archive_name].tgz *

Search and replace text in files from the command line

find . -name '*.html' -maxdepth 1 | xargs perl -pi -e 's/search_text/replace_text/g;'

Create CDs from XMMS playlists

#! /bin/bash
#
# M3U CDWRITER
# 10/16/2003
# rory o'connor - rory | thewhiteroom.com
#
# writes CDs from .m3u files created with XMMS
# Tracks are written in the order they appear in
# the .m3u file.
#
# prerequisites:
# lame
# normalize
# cdrecord
# m3u playlist no longer than 1:19 (for 80min cd)
#
######configuration
# "dev" parameter to cdrecord.  if you don't know,
# try 'cdrecord --scanbus' on the cmd line
dev=1,2,0
#
# speed of the cdrecorder
speed=12
#
# the temp directory for .wav files
# THIS SCRIPT CREATES AND REMOVES IT
holding=$HOME/cdtmpdir # no trailing slash

file=""
m3u=""
zero=0
count=0

if [ -d $holding ]; then echo "CD temp ($holding) cannot already exist...exiting."; exit; fi

echo "Please enter the path and filename of the .m3u file you want to write to CD relative to $HOME:"
read m3u

file=$HOME/$m3u

if [ -f $file ]

then
 
 # make the temp directory
 mkdir $holding
 
 for file in $file
 do
  grep -v '^#' $file | while read line
  do
 
  count=$[ $count + 1 ] 
  name=`echo $line | awk -F"*" '{print $1}'` 
  echo "$name" 
  
  # check the length of $count
  length=`echo "$count" | wc -c | cut -c1-8`
  length=$[ $length - 1 ] 
  
  # fix the count to a 2-digit number
  if [ $length = 1 ]; then
   newcount=$zero$count
  else
   newcount=$count
  fi
  
  # write wav file to our holding pen
  lame --decode "$name" $holding/$newcount.wav
  
  done
 
 done

  
 # normalize the wav files
 normalize -m $holding/*.wav
 
 # burn the CD
 cdrecord dev=$dev -eject speed=$speed -pad -audio $holding/*.wav
 
 # remove the files
 
 for file in `ls -1 $holding`
 do 
  rm -f $holding/$file
 done
 
 rmdir $holding
 
else 

 echo "file does not exist."

fi