YahooGroups Online Search Tool using MySQL

I searched very long for a tool to make the archive available in my own database. 
Yahoo2mbox did not do it's job I wanted
PG Offline was doing half what I wanted. So I needed to extend this kind of service.
So we did.

I was not happy with the way Yahoo is providing its search functionality to its users
My mailinglist is having more than 56.000 messages starting from the year 1999. Searching this database
will take hours. Now with these scripts: seconds.

Please notice that I use it to my users as a registered feature. You need to be a member of the mailinglist
to use this service. Non members do not have any access.

scripts and information can be found here http://www.aroundmyroom.com/scripts/yahoo

Needed:
PHP 4.x, MYSQL 3.x or Higher, ACCESS, PG Offline, Webserver (apache), Windows Client System, phpmyadmin
and a little common sense ;-)

In My Situation I used a Sun Qube3 with PHP 4.x and MySQL 3.x with a PIII 800 with XP Pro


Documentation by D. Slagers [dennis@mixfans.org]
Scripts by DJ Roel


Start:

[1] Use PG Offline [ http://www.personalgroupware.com ]
This tool will download all messages from your yahoogroups mailinglist and will save it in an Access Database

[2] At your Windows system you need to install a MySQL odbc driver. This one is being used by the vbs script
to connect to the mysql server. The Driver can be downloaded from this location:
http://dev.mysql.com/downloads/connector/odbc/3.51.html (‘Windows downloads’, take the exe file, 5.8 mb) 

check 
info Control Panel -> Administrative Tools -> Data Sources (ODBC))

[3] Create in MySQL a new database and user, import and use the create_table_archive.sql file
you can use phpmyadmin


[4] Use Archive2Mysql.vbs to import the mdb (access) file to your mysql server
NOTE: Standard the script is optimized for MySQL 4. If you use Mysql 3.x than you need to remove the
lines about the transactions. For MySQL 3 make sure that

objConnMySQL.BeginTrans
objConnMySQL.CommitTrans
objConnMySQL.RollbackTrans

are not being used, otherwise you will not be able to import the database with messages into your mySQL server.

with these SQL statements you are able to inject new messages retrieved with PG Offline into the MySQL database starting with the last entry available:

INSERT INTO archive ( YahooMessageID, FromUser, FromEmail, Subject,
SubjectSrt, RecDate, Message, AttCount, NewMsgFlag )
SELECT tblYgr_name-database.YahooMessageID, tblYgr_name-database.From,
tblYgr_name-database.FromEmail, tblYgr_name-database.Subject,
tblYgr_name-database.SubjectSrt, tblYgr_name-database.RecDate,
tblYgr_name-database.Message, tblYgr_name-database.AttCount,
tblYgr_name-database.NewMsgFlag
FROM tblYgr_name-database LEFT JOIN archive ON
tblYgr_name-database.YahooMessageID = archive.YahooMessageID
WHERE (((archive.YahooMessageID) Is Null));

In My situation I use above SQL statement with Access [See the Access-Example-Yahoo.jpg]
Note: you need to have a link within your Access with your MySQL database

NOTICE: Please rename the 'name-database' text information into the name of the database filename.

use the PHP files to start searching the database, Notice that you need to enter a username and password
to connect to the database (from search.php)

If you need more information: mail me, or if you have any contributions to have the script automatically update the database information: mail me ;-)

Tjsak!
Dennis