Thomas Gericke

Since I am a “everything has to be stored”-freak, I created a way to extract short messages (SMS) from my cellphone and store them into a MySQL-Datebase. I also wrote a simple PHP frontend which allows to display messages, to filter them by sender’s or receiver’s name and also to arrange them to (so called) conversations.Conversations may be very useful if you write lots of messages with a certain person and some short messages are about a specific topic.Here’s what you need:

• a cellphone with Symbian (e. g. UIQ3, S60v3)
• BestMessageStorer
• a Perl script
• a MySQL database
• an Apache webserver
• a PHP interpreter
• lots and lots of short messages 🙂

This is how to set up the database tables:

Database tables sms and sms_conversations:

CREATE TABLE sms (
  ID int(5),
  conversation int(3),
  src text,
  dest text,
  weekday text,
  year int(4),
  month text,
  day int(2),
  time text,
  text text,
  timestamp timestamp,
  PRIMARY KEY(ID)
)

CREATE TABLE sms_conversations (
  ID int(3),
  name text,
  PRIMARY KEY(ID)
)

I won’t post the Perl scripts (one for reverting the order of extracted short messages, one for storing them in the database) here. I neither won’t post the PHP file. You may get in touch with me to optain them.

Once you have this database up and running, you collected some short messages on your cellphone, getting them stored is as easy as 1-2-3. Here we go:

1. safe messages to a file using BestMessageStorer
2. transfer file onto a computer
3. safe file with encoding “ANSI” (or anything else, whatever fits your needs)
4. transfer file onto your server (on which you have the Perl scripts installed)
5. process the file using: cat FILENAME | ./reverse_file | ./process_sms
6. finished

Have fun! 🙂