Awking Mysql

How do you perform awk type functions in Mysql?

Say you have a list of urls and you want to find the most common domain names hosting gif files.

http://www.fileden.com/123/foo.gif
http://h1.ripway.com/11.gif
http://www.hostmyfile.info/22/web.gif
http://www.fileden.com/24/fun.gif

If this list is in a file you can use standard linux commands to find the most common domains.

grep '\.mp3' my_pictures |awk -F'/' '{print $3}' |sort |uniq -c|sort -nr


If the list is in a mysql database you can use locate() and substring() to accomplish the same thing!

SELECT count(*) as count, substring(url,1,locate('/',url,9)) as f FROM `my_pictures` WHERE url IS NOT NULL and url !='' and filename like '%.mp3' group by f order by count desc

Happy Awking!

phpMyAdmin #1045 - Access denied for user 'root'@'localhost' (using password: NO)

When installing phpMyAdmin the most common error is not being able to connect to the database after installation.

#1045 - Access denied for user 'root'@'localhost' (using password: NO)

Here is the solution:
Make sure your config file in the phpMyAdmin directory is named "config.inc.php"!

In the installation instuctions it says to copy config.default.php into the phpMyAdmin directory and then customize it (usually the only change needed is to change the 'auth_type' is changed from 'config' to 'http'). Make sure to rename config.default.php to config.inc.php.

Get the Latidude and Longitude from an IP

ip2location shell script

for k in `cat ip_list`;
do
id=`echo $k|awk -F_ '{print $1}'`;
ip_num=`echo $k|awk -F_ '{print $2}'|awk -F. '{ printf "%10d\n",$1 * 16777216 + $2 * 65536 + $3 * 256 + $4 }'`;
echo id=$id;
echo ip_num=$ip_num;
echo "SELECT ip_latitude,ip_longitude,ip_city FROM ip2location WHERE (ip_from <= $ip_num) AND (ip_to >= $ip_num) LIMIT 1"|mysql -u doug crostel_store;
done


UPDATE my_space_users u,
ip2location i SET u.user_location = i.ip_city WHERE (

ip_from <=1091213045
) AND (
ip_to >=1091213045
) LIMIT 2

mysql> update my_space_users u, ip2location l set u.ip_country=l.country_short, u.ip_region=l.ip_region, u.ip_city=l.ip_city, u.ip_latitude=l.ip_latitude, u.ip_longitude=l.ip_longitude, u.ip_isp=l.ip_isp, u.ip_domain=l.ip_domain WHERE (l.ip_from <= u.reg_ip_num) AND (l.ip_to >= u.reg_ip_num);

Using Wikipedia as a natural language corpus

1) download the english yahoo abstracts from

http://en.wikipedia.org/wiki/Sort-Merge_Join

wiki.txt
Anarchism is derived from the Greek αναÏÏία ("without archons (ruler, chief, king)"). Anarchism as a political philosophy, is the belief that rulers, governments, and hierarchal social relationships are unnecessary and should be abolished, although there are differing interpretations of what this means.
|
Albedo is the measure of reflectivity of a surface or body. It is the ratio of electromagnetic radiation (EM radiation) reflected to the amount incident upon it.
Abu Dhabi (Arabic: أب٠ظب٠ʼAbÅ« áºaby) is the largest of the seven emirates that comprise the United Arab Emirates and was also the largest of the former Trucial States. Abu Dhabi is also a city of the same name within the Emirate that is the capital of the country, in north central UAE.


wiki.txt1
Anarchism
is
derived
from
the

wiki.txt2
is
derived
from
the
Greek

wiki.txt12
Anarchism is
is derived
derived from
from the
the Greek

wiki.txt.sorted
wiki.txt.sorted.1
wiki.txt.sorted.nonums
wiki.txt.sorted.nonums1
wiki.txt.sorted.nonums1.uniq
wiki.txt.sorted.nonums1.uniq.2words

export LC_ALL=C

cat
wordnet.index
wordnet.index.sorted
wordnet.index.sorted.uniq
wiki.txt.sorted.nonums1.uniq.2words.cols
wiki.txt.sorted.nonums1.uniq.2words.cols.LC_ALL
wordnet.index.sorted.uniq.LC_ALL
wiki-wordnet.join


for k in `cat wordnet.index.sorted.uniq.LC_ALL`; do look $k wiki.txt.sorted.nonums1.uniq.2words.cols.LC_ALL; done >/tar-backups/wiki-wordnet.join



3 word n-grams
grep -v '[^[:print:]]' wiki.txt1 >wiki1.txt
tail +2 wiki1.txt >wiki2.txt
tail +2 wiki2.txt >wiki3.txt

grep "[[:print:]]\+\t[[:print:]]\+\t[[:print:]]" wiki123.txt >wiki123.txt3



find the most used 3 word phrases in a text file
sort rewrite123.txt3 >rewrite123.txt3
grep ".\+\t.\+\t.\+" rewrite123.txt >rewrite123.txt3
sort rewrite123.txt3 >rewrite123.txt3.sorted
uniq -dc rewrite123.txt3.sorted >rewrite123.txt3.sorted.uniqd
sort -nr rewrite123.txt3.sorted.uniqd >rewrite123.txt3.sorted.uniqd.sorted
more rewrite123.txt3.sorted.uniqd.sorted

3 be traveling with
2 you want to
2 want to have
2 student group bookings.
2 part of their
2 on the other
2 notarized letter of
2 made with tap
2 entertainment , activities
2 and want to

Plot a wav file - wav2png

#!/bin/sh
#usage: wav2png.sh file.wav


BASE=${1%.wav}
PNG=$BASE.png
WAV=$BASE.wav
DAT=$BASE.dat

#echo $BASE,$PNG,$WAV,$DAT
sox $WAV $DAT
grep -v '^;' $DAT >$DAT.clean
FREQ=`head -1 $DAT|tr -d ';'`

echo -e "set terminal png;set title '$FREQ';set output '$PNG'; plot '$DAT.clean'" |gnuplot

Split a midi file into separate tracks

Get the program:

wget http://interglacial.com/~sburke/pub/midi_splitter.pl


perl midi_splitter.pl /tmp/vanilla.mid
file vanilla*.mid
midi2abc vanilla.mid
midi2abc vanilla_t01_c00.mid >vanilla_t01.abc

Access internet hotspots in client mode with WRT54G

Linksys WRT54G (below version 5 because linksys later switched to vmware)

Load the sveasoft firmware. (I am using Alchemy-pre5.3 v2.04.4.8sv)

Put it in client mode with loopback off.

Here is a good tutorial:
http://www.engadget.com/2005/05/24/how-to-connect-your-linksys-wrt54g-network-to-the-internet/

ssh into the router and type:
~ # wl scan
~ # wl scanresults|tr -d '\n' |sed 's/noise/\n/g' |tr ']' '\n'|grep ^SSID|awk '{print $(NF-1),$2}' |sed 's/Mode://'|sort -n
-96 "zeus"
-94 "LF-X1U.00014A10A7C8"
-94 "wireless"
-89 "Doug"
-88 "GHOST"
-86 "COR-MDA-LAN"
-86 "linksys"
-86 "linksys"
-83 "default"
-83 "hpsetup"
-83 "linksys"
-78 "hpsetup"
-78 "linksys"
-74 "hirsh
-51 "link"

You will get a sorted list of the most powerfull access points in range.

Split a polyphonic midi file into monophonic tracks

perl midi_splitter.pl /tmp/vanilla.mid

Music secrets revealed

" Using Sox, LAME, FLAC, and madplay - I put together this "dictionary" of shell commands that can be run to convert audio from one format to other, including dialup-modem-streamable lofi mono MP3 files, and including shorter clips of music."

-Derek Silvers is the founder and sole programmer of http://cdbaby.com

Well done!

See the article here:
www.oreillynet.com/pub/wlg/5894


Here are a few other usefull linux audio commands:

mp3info /tmp/07_The_Worst_Hangover_Ever.mp3
48 kbit/s j-stereo (2m 57s)

sox /tmp/orig.07_The_Worst_Hangover_Ever.wav -e stat
Samples read: 15695872
Length (seconds): 177.957732
Scaled by: 2147483647.0
Maximum amplitude: 0.999969
Minimum amplitude: -1.000000
Midline amplitude: -0.000015
Mean norm: 0.237282
Mean amplitude: -0.000037
RMS amplitude: 0.312397
Maximum delta: 1.965851
Minimum delta: 0.000000
Mean delta: 0.163578
RMS delta: 0.230147
Rough frequency: 5170
Volume adjustment: 1.000

Using pdftk to process pdf files

pdftk multimodal.pdf dump_data output report.txt; cat report.txt

pdftk multimodal.pdf dump_data_fields output report.txt; cat report.txt

Yahoo hosting MySQL tricks

To load a csv file into a MySQL database.

1) FTP the csv file to /tmp/afile.csv directory on your yahoo server.

2)From yourdomain.com/phpMySql run this sql:

LOAD DATA LOCAL INFILE '/tmp/afile.csv' REPLACE INTO TABLE `carrier`
FIELDS TERMINATED BY ''
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n';


Here is another example:

LOAD DATA LOCAL INFILE "/tmp/IP.CSV" INTO TABLE iploc FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'

Lame is sweet

Lame is a pretty cool linux program you can use to batch process MP3 files:

lame --resample 22.05 --scale 10 -b 48 -a HowBizarre.wav -o HowBizarreLoud.mp3

while i'm at it; here is the whole script

#!/bin/bash
for k in *
do
lame --resample 22.05 --scale 10 -b 48 -a $k -o ${k%.wav}.mp3
done

Garbled man pages in Linux?

I use the putty windows client to connect to my linux boxes and always run into this problem:

>man sort

NAME
sort â sort lines of text files
SYNOPSIS
sort [OPTION]... [FILE]...
DESCRIPTION
Write sorted concatenation of all FILE(s) to standard output.
Ordering options:

Mandatory arguments to long options are mandatory for short options
too.
âb, ââignoreâleadingâblanks ignore leading blanks
âd, ââdictionaryâorder
consider only blanks and alphanumeric characters
âf, ââignoreâcase
fold lower case to upper case characters


Luckily there is an easy fix:

export LANG="en_US"

I don't know why Fedora sets the LANG variable to something that breaks your terminal.

SMS service centers

t-mobile 12063130004
cingular 13123149810

Fix a hosed Linux terminal

Are you seeing this:
[⎼⎺⎺├@▒1▒ ·]#
[⎼⎺⎺├@▒1▒ ·]# ⎽├├≤ ␋␌▒␌⎺┼

Instead of this?
[root@a1a ~]#
[root@a1a ~]# ls

Your screen can get hosed if you cat a binary file or for some reason your screen recieved a ^n character. This trash on your screen is called graphics mode. You can fix it by typing:

echo ^O

-while you are typing, it will look like garbage on the screen. Don't worry.
-to print a control O you can type: echo ctl-v ctl-o

More 2 Cent Tips... | Linux Gazette: "To force it back you need to have a shell or other program to send the
^o to the stricken display. I often switch to another virtual terminal
and do an 'echo -e '\017' >/dev/tty2'"

GPRS and WAP settings

Here is a good link to get worldwide wap settings for pre-paid and contract GSM phones.

http://www.taniwha.org.uk/gprs.html

Send an email to any phone

Here is a list of email addresses for all cellphones alphabetized by carrier.
http://www.avtech.com/Support/Email/Email_A.htm

Nokia secret codes

* # 06 # = show the serial number IMEI -
*#92702689# (= *#war0anty#) = show warranty information

Nokia Ringtone Capabilities

1100 mono -sms delivery
v120t manual
5165 mono - sms delivery
5125 mono - sms delivery
2600 - cingular email

SI WAP PUSH Calculator

echo -n 0051000B919145557705F400F5AA560605040B8423F0DC0601AE
02056A0045C60C033230322e392e39382e35343a383038302f4347536572
7665722f73657276652e6a73703f69643d31323334353600010349742773
206120424f595c215c215c21000101 |
awk -v FIELDWIDTHS="10 12 6 2 38 90 6 32 6" '{printf "%s,%s,%s,%s,%s,%s,",$1,$2,$3,$4,$5,$6; system("echo "$6" |xxd -r -p"); printf ",%s,%s,",$7,$8; system("echo "$8" |xxd -r -p"); printf ",%s,",$9}' |tr ',' '\n'

0051000B91 #top header
9145557705F4 #11 digit number
00F5AA
56 #bytes in payload
0605040B8423F0DC0601AE02056A0045C60C03 #payload header
3230322E392E39382E35343A383038302F43475365727665722F73657276652E6A73703F69643D313233343536
202.9.98.54:8080/CGServer/serve.jsp?id=123456 000103
49742773206120424F595C215C215C21 #message in HEX
It's a BOY\!\!\!
000101 #footer