ࡱ > ` [ bjbj 1 4 [ [ [ 8
\ \ & n] L ] L ^ ^ ^ ^ ^ ^ $ ܆ h D ˄ 5_ ^ ^ 5_ 5_ ˄ ^ ^ ~ ~ ~ 5_ ^ ^ ~ 5_ ~ ~ G y ^ b] `0aQ [ 1v ` x 0 & c } , y y , ^ > D^ , ~ p^ $ ^ ^ ^ ^ ˄ ˄ ~ d ^ ^ ^ & 5_ 5_ 5_ 5_ M [ [ (There is also a file called Essential.info.DD.and.DDC.doc with vital password and basic structure info).
By D. Harris, last updated 2007 Feb 07 pm 2:30 Pacific Time
Important Methods
(usually for routine activities)
for DarwinDay.org and
DarwinDayCelebration.org:
Network:
On 30Jan'05, trying to install the Dell printer on the Ethernet (wireless and wired router box) network, Tech Support from Dell and I were unable to figure out the Network Administrator name and password. That is the combination needed when doing a remote installation of the printer software. But finally they told me that I could use the Dell blue printer CD to install the printer (by installing its drivers) on the local computer (named DDC2) that we stuck the CD into. The Dell MFP printer's own protection is either admin/admin or admin/password (settable with the CD for the Dell printer drivers, re-settable from the control panel).
To move newest signups (supporters and email-only) from the remote to the mailing database:
Remove the duplicates from the remote persons database.
Select all the fields of the persons on the remote database using
SELECT *
FROM `persons`
FOR ALL GROUPS OF PROFESIONALS E-MAILINGS:
METHOD 1 [Now outdated. Use Method 3 instead]:
Use a SQL SELECT command to retrieve the desired names from the REAL database on the remote machine (see one example code below). Use the MySQL administrator program to Export the desired fields in .csv data format. Set Fields Terminated By tabs to '|', no enclosing of fields, the other fields as their defaults. Check the box for "Put field names at first row" if you expect to need the reminder of what the fields/columns represent. Save the file on your local hard disk as "Whatever.csv", checking "Save as File" with "None" compression.
The program statestocodes.pl will translate a column of state names (upper or lower case) into the 2 character USPS abbreviations.
To make the insertion (loading) of data, either use programs of the style of
For a mailing to all the contact persons in 2005:
SELECT `contact`, `email` FROM `event`
WHERE YEAR( eventdatetime ) = '2005' ORDER BY 'email' LIMIT 0 , 400
SELECT `eventsernum` , `contact` , `email` , YEAR( eventdatetime ) FROM `event` ORDER BY 'email' LIMIT 0, 400
SELECT `persontitle` , `personname`
FROM `person`
WHERE YEAR( eventdatetime ) = '2005'
ORDER BY 'email'
LIMIT 0 , 400
Working from a template file like "outreach1.doc", use the Tools/Letters and Mailings/MailMerge to start the MailMerge Wizard. Say you are making "E-mail messages". When you are ready to generate the messages, output from the MailMerge by clicking the icon for MERGE TO NEW DOCUMENT (not to an e-mail) to get the big file we need. Use the Default character encoding and Plain Text (or Filtered HTML for HTML messages) format. Name the file "Unsent Messages.txt" (or .htm) and rename it later as "Unsent Messages".
Have the Mail handler program (Mozilla Communicator or Thunderbird) exited. I.e. do not have it open. You may have to close the Mozilla browser too. Firefox needn't be closed.
'C:\Documents and Settings\User 2\Application Data\Mozilla\Profiles\default\hhetgoov.slt\Mail\Local Folders\ Unsent Messages' is the file that will be created by techniques like MailMerge in Word, and then sent by being copied and pasted to that long address. [It's different for Thunderbird, for which the procreturns.pl program is written.] Be sure you are using the rstephens@DarwinDayCelebration.org account, and then use File/Send Unsent Messages to send all the messages. You may want to Exit after each bunch to avoid suspiciously large sendings. The SMTP protocol at smtp.earthlink.net seems to break frequently, or is programmed to do so if a lot of mail comes through in a short time.
After sending the Unsent Messages, select the Local Folders mail account and File/Compact Folders, to remove the marked messages that have been sent.
METHOD 2:
There is now an emailing database with all the persons data and personfacts describing things about their email identity. So the following is too short. Go to METHOD 3!
There is now a Perl program for sending emails using SMTP at semi-random intervals: sendit.pl or names of that sort that end in .pl meaning Perl. Use SQL to extract the names and fields to be used in the mailing from the MySQL database on the host computer (HostRocket.com).
SELECT `persontitle` , `personname`, `email`, `mayemail`, `maydisplay`
FROM `person`
WHERE `mayemail` = 'y'
ORDER BY `personsernum`
LIMIT 0, 400
Or maybe
SELECT `profname` , professor.email
FROM `professor`
LEFT JOIN `unsubscribers` ON professor.email=unsubscribers.email
WHERE unsubscribers.email IS NULL
ORDER BY 'profname'
LIMIT 0, 4000;
Export as a CSV file for MS Excel (the Windows 2000 version, not the 2003 version).
Delete column heading line (if any) in Excel.
Save as a Tab delimited file and reopen with Notepad.
Replace all Tab characters with '|' characters for the separator. (highlight a Tab and ^C to copy it, so you have something to drop into the FIND box).
Save the textfile to good named file, like regs.bars.txt.
Read and use the directions at the top of the template *.textmsg file and also the comments near the start of sendit.pl.
You can copy in a HTML formatted message by saving the message in "Send Later", and going to the Local Folders under the Mail folder from the operating system. There may be old messages present but not yet removed by "Compact Folders". Use Notepad to copy the message to the Perl *txtmsg file. Remove unnecessary header material. BE SURE YOU ARE WORKING ON A TEXT IN THE SAME DIRECTORY AS THE DATA!
XX
METHOD 3 (May 2006):
Remove the duplicates from the remote persons database.
Select all the fields of the persons on the remote database using
SELECT *
FROM `person` ; , or better = click on Browse at the top of the persons screen
On the resulting output, at the bottom, click Export
Export as a CSV file, fields terminated by '|', fields enclosed by NOTHING, fields escaped by '\', lines terminated by '\r\n', replace NULL by NULL.
UNcheck the box before "Put fields names in the first row".
Click "Save as file" and then click "Go" to export to the local PC.
Choose "Save to disk" as a Comma Separated Values file (.csv), into a place like the "My Documents/Supporters" folder with an appropriate filename.
Open with Notepad and change the field separators to '|' if necessary. Save the file.
Use this SQL on the local mailing database to get the next personsernum to use if you're loading them into the mailing database:
SELECT MAX(personsernum)
FROM person ;
Use Perl script load.newsignups.pl to read thru the .csv file that came from the remote computer and generate SQL to insert only NEW persons (NEW emails) into the mailing database.
RETURNED E-MAILS:
The procreturns.pl program should be run with the Thunderbird emailer CLOSED (EXITed), and then Thunderbird restarted to see the new processed emails. The *.msf files will be created by Thunderbird for the matching named files and folders. To discard Extracted and Problem messages, have T'bird closed and delete the desired folders from the appropriate Mail subdirectory.
After processing the returned emails with procreturns.pl, you can save the ProblemReturns for future processing by:
Exiting Thunderbird
Making a copy of the Returns.sbd folder (for safety until there is no question of lost messages)
Deleting the Returns.sbd folder
Renaming the ProblemReturns.sbd folder to the Returns.sbd folder name and deleting any files that start with 'ProblemReturns.' .
Starting Thunderbird again.
Now future incoming mail will go into the Returns folders with the old ProblemReturns messages, and future runs of procreturns.pl will try again to extract from the total combinations of new and previously tried messages.
Unsubscribers
Script unsubs.extractractor2.pl will run through the compacted unsubscribers Inbox and extract the emails in the From: lines.
I've written a Perl script called unsubs2sql.pl that takes a file of text email addresses and turns them into single lines of INSERT commands. It can be used to create the SQL for inserting new lines into the database table "unsubscribers".
MySQL TIMESTAMP change coming:
At this writing, HostRocket uses MySQL version 4.0.22 (see the HostRocket Control Panel, left hand column). At some time HostRocket will upgrade to MySQL 4.1 or higher. There is a chance that our TIMESTAMP related code will be broken at that point. See the Person table for instance, where TIMESTAMP was used to show when a person registered, and compared with a "when valid" field that showed when the field was confirmed as good values. There will be another change when MySQL versions 5.0.2 or later are reached. For the complex details, see the online MySQL documentation at HYPERLINK "http://dev.mysql.com/doc/mysql/en/timestamp-4-1.html"http://dev.mysql.com/doc/mysql/en/timestamp-4-1.html .
USEFUL SQL SAMPLES:
For loading a few hundred persons and create personfacts entries as appropriate, use a Perl program like load.sciclubs.pl .
For loading data that would require files larger than 2048K in size (of SQL), use instead the LOAD INLINE bulk loading command of MySQL, at HYPERLINK "http://dev.mysql.com/doc/refman/5.0/en/load-data.html"http://dev.mysql.com/doc/refman/5.0/en/load-data.html like:
LOAD DATA LOCAL INFILE './medprofs.emails.txt'
INTO TABLE `emailbase.testdb`
FIELDS TERMINATED BY '|' TERMINATED BY '\r\n'
(`personsernum`, `email`)
SET `lastupdated` = CURRENT_TIMESTAMP;
For knowing how many in each department in the professors table:
SELECT `department`, COUNT(`department`)
FROM `professor`
GROUP BY `department`
LIMIT 0,300 ;
How many Supporters? On the remote database run:
SELECT `maydisplay`, COUNT(`maydisplay`)
FROM `person`
GROUP BY `maydisplay`
LIMIT 0,300 ;
24May06 IN DEVELOPMENT NOT USEFUL YET:
SELECT rperson.*
FROM `rperson`
LEFT JOIN person ON person.email = rperson.email
WHERE person = 'withpaper'
AND person.personsernum = personfacts.personsernum
AND unsubscribers.email IS NULL
ORDER BY person.email
LIMIT 0, 200
Above query comes from this valid pattern:
SELECT table1.* FROM table1
LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.id IS NULL;
What titles and how many of each?:
SELECT `persontitle`, COUNT(`persontitle`)
FROM `person`
WHERE 'persontitle` LIKE 'Prof%'
GROUP BY `persontitle`
LIMIT 0,300
Counting Evolution Sunday churches:
SELECT COUNT( `public` ) FROM `event` WHERE `eventname` LIKE '%Evolution Sunday%'
For the table of arbitrary facts (PERSONFACTS) applied to various persons in the local mailing database:
SELECT `factname` , COUNT( `factname` ) , MIN( `pfnum` ) , MAX( `pfnum` ) FROM `personfacts` GROUP BY `factname` LIMIT 0 , 300 ;
How many kinds of personfacts are there? Local db.
SELECT factname, COUNT(factname) AS NumOccurrences
FROM personfacts
GROUP BY factname
For all the persons of a particular group, into a display that can be EXPORTed and fed to the Perl mailing program: [Need to check against unsubscribers!]
SELECT *
FROM `person`,`personfacts`
WHERE (factname = 'issupporter')
AND (person.personsernum = personfacts.personsernum)
4Jan2006: Drawing out the duplicated records from the mailing database:
SELECT * FROM `person`,`personfacts` ,`dups`
WHERE (dups.email = person.email)
AND (person.personsernum = personfacts.personsernum)
ORDER BY person.personsernum
LIMIT 0,500
Counting the UNIQUE/DISTINCT persons:
SELECT COUNT(DISTINCT email)
FROM `person`
LIMIT 0, 30
4Jan2006: Isolating the duplicate email addresses (no longer required because the `person.email` field is now indexed as UNIQUE).
May18: The UNIQUE is only on the mailing database version of email, because people are being allowed to enter themselves on the remote database! If they attempt an update by adding a new entry that duplicates an email, we don't block them. This can change when we have logins to sessions.
SO this SQL is for use with the remote database, isolating the duplicates in that:
SELECT email,
COUNT(email) AS NumOccurrences
FROM person
GROUP BY email
HAVING ( COUNT(email) > 1 )
For use on the remote database, to select records that may be duplicates of the person, but do add possible new personfact records about the person (22May06): THIS DOESN'T MAKE SENSE!!!
REPLACE INTO `PERSON`
VALUES ('75839','Robert Stephens','Dr.',
'NULL','343 Lexington','NULL',
'Menlo Park','CA','94025',
'892','rjstsi@aol.com','NULL','',
'y','y','2004-10-29 00:00:00','20041029000000')
For revising things like the country numbers in the database:
UPDATE event
SET `country` = 408
WHERE `country` = '372'
---------------------------------------------------------------
For getting professors of a particular department, WITH UNSUBSCRIBERS EXCLUDED. THIS DOESN'T WORK (because there is no nested SELECT before MySQL 4.1 which is not on Hostrocket, so see the rework below:
SELECT `profsernum` , `profname` , `email` , `department` FROM `professor` , `unsubscribers` WHERE (`department` LIKE 'Organis%')
AND (professor.email != unsubscribers.email) LIMIT 0 , 900
This is a reworking of the above without nested SELECT, and it DOES exclude the unsubscribers:
[This does select the PR mailing list as 4 fields. 14Jan06 ]
SELECT personfacts.factvalue1, person.stateprovetc, person.personname,
person.email
FROM person, personfacts
LEFT JOIN unsubscribers ON person.email = unsubscribers.email
WHERE personfacts.factname = 'withpaper'
AND person.personsernum = personfacts.personsernum
AND unsubscribers.email IS NULL
ORDER BY person.email
LIMIT 0, 200
[14Jan2006: To collect all the emails, except unsubscribers and signups who don't want email, that received mailing last year: ]
SELECT DISTINCT factname , person.email, persontitle, personname
FROM person, personfacts
LEFT JOIN unsubscribers ON person.email = unsubscribers.email
WHERE unsubscribers.email IS NULL
AND mayemail = 'y'
AND person.personsernum = personfacts.personsernum
AND personfacts.factname IN
( 'isnodisplay', 'issupporter', 'waseventcontact', 'isarthurslist', 'isprofessor')
ORDER BY personfacts.factname, person.email
LIMIT 0, 7000
For a last minute mailing in 2006 to event contacts. Get contacts from remote site:
SELECT DISTINCT(contact, email)
FROM event
WHERE event.email IS NOT NULL
AND event.email LIKE '%@%'
ORDER BY event.email
LIMIT 0, 400
Then use the Perl mailer program to send the message.
[OLD, when there was a separate 3500 professors]
SELECT `profsernum` , `profname` , professor.email , `department`
FROM `professor`
LEFT JOIN `unsubscribers` ON professor.email=unsubscribers.email
WHERE unsubscribers.email IS NULL
AND `department` LIKE 'Bio%'
ORDER BY 'email'
LIMIT 0, 600
For displaying the person duplicates for editing:
SELECT * FROM `person`, `personfacts` , `dups` WHERE person.email = dups.email AND person.personsernum = personfacts.personsernum ORDER BY 'email' LIMIT 0 , 2000
DELETE
FROM `event`
WHERE `eventsernum` > 348;
SELECT factname, COUNT( factname ) FROM personfacts GROUP BY factname ORDER BY factname;
DEVELOPMENTAL SQL WORK:
24May06: The last of the remote database personsernums is 1701, for HYPERLINK "mailto:roberttkonig@yahoo.com"roberttkonig@yahoo.com.
The lastupdated date of the first transfer from the remote to the mailing database was at 20051231192226.
The first of the NEW persons has personsernum 75839 in the mailing database.
24May06: Getting signups on the remote db:
SELECT person.personsernum, email, lastupdated
FROM `person`
WHERE person.maydisplay = 'y'
OR person.maydisplay = 'n'
ORDER BY person.lastupdated ;
24May06: Getting the signups from the unified mailing database into a PRE-CREATED empty table with the structure of the person table:
INSERT INTO `signups`
SELECT person.*
FROM `person`, `personfacts`
WHERE person.personsernum = personfacts.personsernum
AND (personfacts.factname = 'issupporter'
OR personfacts.factname = 'isnodisplay')
ORDER BY person.email
25May06 Getting the one email that is "extra" in emailbase13jan:
SELECT emailbase13jan.unsubscribers.*
FROM emailbase13jan.unsubscribers
LEFT JOIN emailbase.unsubscribers
ON emailbase13jan.unsubscribers.email = emailbase.unsubscribers.email
WHERE emailbase.unsubscribers.email IS NULL ;
25May06: Putting the value into where it was missing in emailbase:
INSERT INTO emailbase.unsubscribers (email)
VALUES ('revrosi@bostoncoop.net');
26May06: Seeking records that are logically inconsistent in the mailing database, in which the person has a 'n' for maydisplay from a "mayemail"-only registration, and also a 'y' for maydisplay from a full Supporter registration:
SELECT *, COUNT(`personsernum`)
FROM `personfacts`
WHERE (factname = 'issupporter'
OR factname = 'isnodisplay')
GROUP BY personsernum
HAVING COUNT(`personsernum`) > 1
ORDER BY personsernum
26May06: How many people came over from the remote database?:
SELECT rperson.*
FROM `rperson`
LEFT JOIN signups ON rperson.email = signups.email
WHERE signups.email IS NULL ;
26May06: Getting the new records for processing by a Perl script and putting into the mailing database. This compares the email field on ALL the person records from the remote database, and the smaller set of existing registered person records in the mailing database.
SELECT rperson.*
FROM `rperson` LEFT JOIN signups ON rperson.email = signups.email WHERE signups.email IS NULL;
29May06: Seeking incompatible with the person information [May NOT BE THE RIGHT SEARCH]:
SELECT COUNT(personfacts.personsernum),person.*
FROM `personfacts`, `person`
WHERE person.personsernum = personfacts.personsernum
AND (factname = 'issupporter'
OR factname = 'isnodisplay')
GROUP BY personfacts.personsernum
HAVING COUNT(personfacts.personsernum) > 1
ORDER BY personfacts.personsernum
29May Seeking records NOT to insert in mailing person table
SELECT person.*
FROM person, personfacts
WHERE ((personfacts.factname = 'issupporter')
OR (personfacts.factname = 'isnodisplay'))
AND (person.personsernum = personfacts.personsernum)
AND (person.personsernum > 75000)
29May06: Getting a table (set) of JUST THE person records which would cause uniqueness/key problems if ADDED TO THE MAILING person table:
SELECT person.*, newperson.*
FROM person, newperson
WHERE person.email = newperson.email
PROTOTYPE FOR LEFT JOINS:
SELECT table1.*
FROM table1
LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.id IS NULL;
7Jun06: Finding all the person records who have no personfacts records:
SELECT PERSON.* FROM PERSON LEFT JOIN personfacts
ON PERSON.personsernum = personfacts.personsernum WHERE personfacts.personsernum IS NULL ;
(A similar search can find all the personfacts records that point to a nonexistent person record.)
9Jun06: Find all the personfacts records that go with a particular personsernum:
SELECT person.*, personfacts.*
FROM person, personfacts
WHERE person.personsernum=110
AND person.personsernum=personfacts.personsernum
12Jun06: Find any records in the mailing person table that have the same email as a remote (self-registered) person record:
SELECT newperson.*, person.*
FROM newperson, person
WHERE newperson.email = person.email
12Jun06: trying to remove the questionable people from remote person table
SELECT person . * , personfacts . * FROM `person` , personfacts WHERE person.personsernum = personfacts.personsernum AND person.personsernum >75837 AND (personfacts.factname = 'issupporter' OR personfacts.factname = 'isnodisplay') ORDER BY lastupdated, person.personsernum
Are there any problem cases?:
SELECT person.* , personfacts.* FROM `person` , personfacts WHERE person.personsernum = personfacts.personsernum AND person.personsernum >75837 AND NOT (personfacts.factname = 'issupporter' OR personfacts.factname = 'isnodisplay') ORDER BY lastupdated, person.personsernum
SELECT person.* , personfacts.* FROM `person` , personfacts WHERE person.personsernum = personfacts.personsernum AND person.personsernum =76135
What would a DELETE remove?:
SELECT personfacts.* FROM `person` , personfacts WHERE person.personsernum = personfacts.personsernum AND person.personsernum >75838 ORDER BY lastupdated, person.personsernum
First delete the associated personfact records:
DELETE personfacts.*
FROM `person` , personfacts
WHERE person.personsernum = personfacts.personsernum
AND person.personsernum >75838
SELECT person.*
FROM `person`
WHERE person.personsernum >75838
ORDER BY lastupdated, person.personsernum
Secondly, delete the person records:
DELETE person
FROM `person`
WHERE person.personsernum >75838
Now, backing up to May 26 or thenabout, of all the records from the remote person table, which are not duplicates of persons already in the Mailing database?:
SELECT rperson.*
FROM `rperson`, `person`
LEFT JOIN person ON rperson.email = person.email
WHERE person.email IS NULL
12June06:
SELECT * FROM `person`, `personfacts` WHERE (factname = 'issupporter' OR factname = 'isnodisplay') AND (person.personsernum = personfacts.personsernum)
DELETE personfacts
FROM personfacts, person
WHERE person.email = 'uu1stomaha@aol.com'
AND person.personsernum = personfacts.personsernum
DELETE
FROM `person`
WHERE email = 'uu1stomaha@aol.com';
All the personfacts information about one person in the mailing database:
SELECT `email`, personfacts.personsernum, factname, factvalue1, factvalue2
FROM `person`, `personfacts`
WHERE person.email= 'ghgolter@aol.com'
AND person.personsernum = personfacts.personsernum
LIMIT 0, 30
Lowercase and remove leading and trailing spaces for all the emails in a table:
UPDATE `person`
SET email = LOWER(email);
UPDATE `person`
SET email = LTRIM(email);
UPDATE `person`
SET email = RTRIM(email);
And to make sure the unsubscribers match the other names. These things (and removal of single quote and surrounding quotes) are done as part of unsubs2sql.pl processing:
UPDATE `unsubscribers`
SET email = LOWER(email);
UPDATE `unsubscribers`
SET email = LTRIM(email);
UPDATE `unsubscribers`
SET email = RTRIM(email);
12Dec06:
And for the human names that might be surrounded by whitespace:
UPDATE `person`
SET personname= LTRIM(personname);
UPDATE `person`
SET personname= RTRIM(personname);
SELECT `factvalue1`, `personname`, person.email
FROM `person`,`personfacts`
LEFT JOIN `unsubscribers` ON person.email=unsubscribers.email
WHERE unsubscribers IS NULL
ORDER BY 'person.email'
LIMIT 0, 600
mailbox:/C|/Documents and Settings/User 2/Application Data/Thunderbird/Profiles/nijx0stm.default/Mail/pop.darwindaycelebration-1.org/RecentEmails
If you want to disable autocommit mode for a single series of statements, you can use the START TRANSACTION statement:
START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;
For a mailing to all the contact persons in 2005:
SELECT `contact` , `email` FROM `event`
WHERE SUBSTR(`eventdatetime`,1thru4.syntax.of.substr) = '2005' ORDER BY 'email' LIMIT 0 , 400
2006Nov17: All the ismedprof records for the medprofs emailing:
SELECT DISTINCT person.email
FROM person, personfacts
LEFT JOIN unsubscribers ON person.email = unsubscribers.email
WHERE unsubscribers.email IS NULL
AND person.personsernum = personfacts.personsernum
AND personfacts.factname IN
( 'ismedprof')
ORDER BY person.email
LIMIT 0, 26000
9Dec2006: All the isprofessor records for the (is)professors emailing:
SELECT DISTINCT person.personname, person.email
FROM person, personfacts
LEFT JOIN unsubscribers ON person.email = unsubscribers.email
WHERE unsubscribers.email IS NULL
AND person.personsernum = personfacts.personsernum
AND personfacts.factname IN
('isprofessor')
ORDER BY person.email
LIMIT 0, 4000
29Dec06: Arthurslist people, with the factvalues etc:
SELECT DISTINCT person.personname, person.email, personfacts.*
FROM person, personfacts
LEFT JOIN unsubscribers ON person.email = unsubscribers.email
WHERE unsubscribers.email IS NULL
AND person.personsernum = personfacts.personsernum
AND personfacts.factname IN
('isarthurslist')
ORDER BY factvalue1, factvalue2, person.email
LIMIT 0, 2000
11Jan07: How many events are registered for each year?:
SELECT YEAR(eventdatetime), COUNT(`eventsernum`)
FROM `event`
GROUP BY YEAR(eventdatetime)
ORDER BY YEAR(eventdatetime)
LIMIT 0, 4000
12Jan07: How many personal signups a year? Email only and Supporters:
SELECT YEAR( lastupdated ) , COUNT( email ) , maydisplay FROM `person` GROUP BY YEAR( lastupdated ) , maydisplay ORDER BY YEAR( lastupdated ) , maydisplay LIMIT 0 , 4000
25Jan'07: All the State fields used in Events table:
SELECT stateprovetc , COUNT(stateprovetc) FROM `event` GROUP BY stateprovetc ORDER BY stateprovetc LIMIT 0 , 10000
31Jaqn07: Remove bungled rows:
DELETE
FROM `event`
WHERE eventdatetime IS NULL;
7Feb'07: How many events in each country in a particular year?:
SELECT countries.country, COUNT( countries.country ) FROM `event` , `countries` WHERE countries.cnumber = event.country
AND `eventdatetime` LIKE '2007-%'
GROUP BY `country` LIMIT 0 , 300 ;
Use of Dreamweaver (DW):
New human language (or propagation of ALL of NEWlang revisions to an existing language:
BE CAREFUL not to overwrite (erase/obliterate/copy onto) any translated pages!
When a new language is needed, on the DDC2 computer ("David's") use CTL-D to Duplicate the NEWlang directory (folder), which will create "Copy of NEWlang". Then rename the topmost "Copy of NEWlang" folder/directory to the language name. Try for a unique language name to avoid hitting the same string in some text. In particular, use englishL for English, spanishSA for SpanishSouthAmerican, AutoTrans for Automatic computer Translation, (more to be assigned). When a popup offers to check for broken links, click "Don't Update".
Select the closed topmost folder with the new language name and use CTL-F to get the Find and Replace tool. Use the Find capacity on "Source Code" in the "Selected Files in Site" to find "NEWlang" (with "Match case") and Replace All with the new language special name, like englishL. Be aware that the Find and Replace tool does not find and replace FOLDER/DIRECTORY names. If there are any NEWlang-containing folders, they must be renamed manually. For instance, the new englishL folder will contain mainNEWlang.css which must be renamed to mainenglishL.css!
For each language, one must assure that there is
Library entries for the current-block and for the navbar (navigation bar). For the Library, open the NEWlangxxxx.lbi language file and Save As the desired language like englishLxxxx.lbi into the Library folder. Then use the Find and Replace (^F) command on the Current Document to change the internal language names in each Library file and re-Save into the new language.
a CSS file specific to that language,
a Template. Make a Copy of mainNEWlang.dwt and rename it to mainenglishL.dwt . Remove the attached NEWlang.css and attach the englishL.css using the Modify/Templates/, and
You may need to use the Site/Advanced/Create New Cache capacity to tell DW about what you've created.
Chooselang/index.html is unusual, and has its own mainchooselang.dwt template file. The chooselang/index.html file will only be changed when a new language is added (or deleted permanently).
FOR CHANGING ENGLISH: Be sure that links to englishL versions are correct in (1) the default /index.html that is used by a browse to darwinday.org and by a browse to (2) the /home/index.html that was sometimes saved by long time visitors. Both should be fixed if needed to link to the englishL language version. This may be done by renaming the file mentioned above to preserve it and moving to the location discussed and changing from NEWlang to. In this case DO allow updating of links.
There is a page for each language in the /Library folder that is named like NEWlangcurr.lbi . That is the block that appears on the middle home page, meant for localized and current event items. That file should only be edited following the Dreamweaver directions for Library items (that will be pulled into places that are set to have the library item). It will be used by the home page, if correctly edited.
Use the Results panel of DW to see if there are unexplained broken links or orphaned files, and fix as needed.
When a new language subtree is created, the chooselang/index.html file must have the new language added to the list of choices. This is only true for the final languages that the public sees. Translators' "testing trees" must NOT be publicly linked from the chooselang/index.html page. (
If this is the first time the new language tree of pages is being done, duplicate the Template/mainNEWlang.dwt template and rename all occurrences of 'NEWlang' to the new language name:
With the top folder of the new sub tree selected, use the Find and Replace (of course use Match Case Finding to narrow the set of hits) to change the "mainNEWlang.dwt" references to "mainXXYZ.dwt" where XXYZ is for the new language.
At the first creation, also duplicate stylesheets/mainNEWlang.css to the new language equivalent (stylesheets/mainXXYZ.css), and use Find and Replace as with the Templates. Colleagues
You can select a language's topmost folder and Replace "Darwin Day Celebration"
with "Darwin Day Celebration - XXYZ " to get top-of- page titling that identifies the XXYZ Language.
[For the Web site creator] Create a restricted single language tree of pages for translators to work upon:
Use HostRocket's CONTROL PANEL/FTP MANAGER/FTP USERS utility to create an obscure string to stand for the language while the translator is working on it. E.g. Chinese could be 'C73R8ch' (is sensitive to upper or lower case of letters). Set the CHMOD (change modes) protection bits so that the Other bits are RWX (octal 7, for Read, Write, and eXecute/search). Create an FTP user name with the same string. Test the FTP login process to verify that logging in with that name lets the translator see all the pages under his ftp name.
[There is an independent file called Essential.translators.directions.doc that tells the translator how to get files, make changes, test the changes, and place the satisfactory file to be made public under the language name.]
Site management for development:
When working as a single worker, set the Local Host and Testing Server to the same place, and turn off the Check Out system in the Site definition settings. After developing local versions that work on one computer, use the Synchronize feature (right click on the Local View) to move all the new files from the Local machine to the Remote server.
If working in a team, turn on the Check Out system, and work in a "pseudo language" (NEWlang) sub tree on the remote server. The NEWlang sub tree can be copied to replace older versions as if the pseudo version was a real language. Visualizing it as a tree, the languages are beside one another, and the pseudo language gets copied sideways to become used as the live version.
For the Current Article quickies [SEE THE PROBLEMS TOWARD THE END OF THE LIST!]:
Go to the home page /NEWlang/home/index.html or if another language's pages will be stable, got to its code. There is one block called currarts.html for each language. If the language of currarts has been translated, do not change it. If they have to use English, you can copy the English version to replace their currarts after you get currarts perfect in English.
Find the code that has 18% src="../currarts.html" or other % at the left. Record what the previous percentage was, since that will have to be found and changed globally by methods below. This percentage tells how much space to reserve on the home page for the contents of the currart.html block. [If the home page size changes, the percentage may change.]
Vary the percent, save the page, use a browser to see how it looks, as needed to make the insert look good on the Newlang home page.
Use Dreamweaver's Find and Replace function over the entire Newlang folder contents to replace % src="../currarts.html"
with % src="../currarts.html" wherever it occurs. This will change all the pages that have that insert. BE VERY CAREFUL! Being a single character off (even a blank space) can break the code everywhere the currarts block is used.
The percentage for height will now be correct for the amount of material on the currarts.html page (because you figured out what percentage to use), everywhere the currarts.html block is going to be inserted.
PROBLEMS [A group of 4 methods that were tried and didn't work for an easily varied block of new material]: The iframe material is only displayed in the context of the DDC page. If an URL in the iframe refers to another DDC page, or some kinds of external pages, the page is stuffed into the space on the DDC that has the clickable URL. For our pages, this produced a fresh pagetop with DNA and all midway down the page, and scroll bars!
PROBLEM 2: The Server Side Includes, which would be great for including material just as if it were inline, either require renaming the file extension to something like .shtml (which doesn't match what browsers expect from Amanda's days and our last year's work) or might work with the "XBitHack on" setting, which doesn't seem to be on the terms that can be searched out at HR. So SSI's don't look promising.
PROBLEM 3: While PHP can do includes, that would require that the pages end with the extension of .php, which would fail to match the expectations of browsers that have saved our Web site address, like in PROBLEM 2.
SO, it may be necessary to have an invisible marker and a file of instructions for the special block currarts.TXT, an manually insert the included code. Yech! So do this:
Search within a language subtree with CTL+F for , copy the material to be included from the FILE1 , and highlight the material to be replaced (down to ). Press CTL+V to do the replacement.
PROBLEM 4: Now the URL's aren't updated to correspond to the changed tree-depth of the material! VOMIT! So one has to clean up the broken links for every internal-to-the-project link in the block of changeable material.
SOLUTION: Use the Dreamweaver Libraries capacity to store and modify and insert a block of material. Now called currNEWlang.lbi for the front page. And the same Libraries capacity to hold the navbar for each language, which will vary by the size of words and characters.
End of Dreamweaver concepts.
Preparing HTML style emails:
Wherever the original text comes from, drop it (or import it, etc.) into a MS Word 2000 text file. In the lower left corner of the document's window, click on the leftmost symbol, to get "Normal" formatting.
When the document looks good, Save As "Web, Filtered" into a file that will have the .html extension.
(We're assuming you have the emailer set to compose in HTML format.)
Use ^A or the mouse to highlight all you want to use in the final letter. Then ^C to copy the material, and at the email Compose window for the body of the text, ^V will paste the HTML formatted material into place.
Highlight the desired material and Format/Paragraph as "Body Text", which will remove excess lines. If you need interline spacing, highlight and make "Paragraph" format.
Send Later gives you a chance to ask for sending as both text and HTML formatted, and then puts it into the /Local Folders/Unsent Messages file.
Use the Notepad simple editor to copy most of the message into the *.textmsg document that brings together the fields of the emails file and the desired message that can be customized by the sendit.pl type Perl program. At the top of the *.textmsg file are directions for declaring the fields, organizing the message, etc.
Running a routine batch of emails:
With the computer on, click on the "start" button in the lower left and choose the "Command Prompt". A little black window will open, ready to accept typed commands.
[If the "Command Prompt" tiny picture wasn't visible right after clicking "start", select "All Programs/Accessories/Command Prompt" to invoke the Command Prompt.]
Type "cd " which will jump the place of action to the working directory/folder.
Type "sendsep9.bat" or whatever the date to be sent is, all in lower case characters and with no leading zeroes in the date. That name will vary by project, but the .bat part is likely to be in common with all. That will start the pre-constructed files working away. The first email should be sent immediately by the program, to HYPERLINK "mailto:dharris@darwinday.org"dharris@darwinday.org, and that account checked to see that the message is proper in appearance..
Printed at DATE \@"H:MM:SS' a'M'/p'M" 2:02:01 a2/p2 (GMT) on DATE \@"D\-MMM\-YY" 10-Feb-07 - Page PAGE 1 of NUMPAGE \*Arabic 17 pages -
! ( + $ & - . / 3 4 ; D H I J W X Y b c h i k l s t y z { | } ~ ýýýýýɽý
h^ 0J
h^ 0J
h^ 0J
h^ 0J
h^ 0J
h^ 0J
h^ 0J
h^ 0J
h^ 0J
h^ 0J h^ 5 h^ >*h^ h^ 5>* h^ 5CJ aJ h 5CJ aJ h^ 5CJ aJ : j k 3 u ~ $a$ Z = ( O ` n o v w * + } ~ 4 I
& F
^ / 4 5 6 C D E n f o | ! W ^ x! ! ! ! W" ]" " " " һֻһһһһһһһһһһһһһһ h^ 5
h^ 0J
h^ 0J
h^ 0J h^
h^ 0J
h^ 0J
h^ 0J
h^ 0J
h^ 0J
h^ 0J
h^ 0J H ^ & \ / = @ A V ) ^
& F
h
& F
^ ^ / s ! ^! ! ! " " " " " G$ H$ $ $ $ 2% R% % % % & & & ['
& F
^
& F
h " " & & ~' ' O( n( * * * * * .+ /+ 2+ G+ Q, R, , , , , , <- R- 71 =1 >1 C1 D1 E1 M1 N1 O1 P1 Q1 U1 V1 ]1 ^1 _1 d1 e1 p1 q1 u1 v1 1 1 1 1 2 2 2 2 2 2 2 2
h^ 0J
h^ 0J
h^ 0J
h^ 0J
h^ 0J
h^ 0J
h^ 0J
h^ 0J
h^ 0J j h^ U
h^ 0J j h^ Uj h^ U h^ 5h^ h^ 5>*;[' \' N( O( n( 2+ 3+ G+ H+ + + , , - #- R- S- o- - - - -
. . 6. D. E. w. . . & . . . . . / / N/ i/ / / / / / 0 (0 R0 m0 n0 0 0 0 0 1 1 1 1 71 1 1 1 1 y2 z2 2 2 2 3 3 3 3 3 3 3 4 4 g4 4 4 4 5 5 5 ;5 Y5 ,&
R(
Px 4 #\'*.25@9d
Hp #&,*-T14|8< 2 2 2 2 2 2 "2 #2 $2 +2 ,2 -2 .2 /2 02 32 42 52 <2 =2 >2 ?2 @2 D2 E2 R2 S2 T2 Y2 Z2 \2 ]2 g2 h2 i2 n2 o2 p2 q2 r2 s2 y2 z2 2 3 i5 n5 o5 p5 r5 u5 v5 ?7 E7 r7 9 9 ?: ; ; ; ; ; ; !; +; ص h^ 5CJ OJ QJ ^J aJ h^ CJ OJ QJ ^J aJ h^ 5CJ aJ
h^ 0J
h^ 0J h^
h^ 0J
h^ 0J
h^ 0J
h^ 0J
h^ 0J
h^ 0J AY5 i5 u5 v5 5 7 r7 7 7 7 7 7 7 8 8 8 8 "9 L9 9 9 9 9 9 9 ,&
R(
Px 4 #\'*.25@9d
Hp #&,*-T14|8< 9 9 ?: ; ; ; ; .< k< < < < = A= t= = = = = :> }> > > >
? =? Z? ? ? ? +; ,; -; .; 5; 6; 7; 8; D; E; F; J; K; V; h; i; n; p; |; ~; ; ; ; ; ; ; ; ; ; ; ; .< = = = = ? ? @ @ A A >A JA KA LA MA WA XA YA ZA iA jA kA lA
B B B B B B (B )B [B aB bB cB dB eB iB jB rB sB tB B B B B B B B
h^ 0J
h^ 0J 5
h^ 0J
h^ 0J
h^ 0J h^
h^ 0J
h^ 0J
h^ 0J P? ? F@ f@ q@ @ @ @ @ @ A A A 7A yA A A A
B B (B )B [B B C C C 0C 1C C B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B 1C 7C 8C @C AC BC GC HC IC QC RC SC TC UC YC ZC eC fC kC lC nC oC wC xC }C ~C C C C C C C C ޱh^ 0J 5>*
h^ 0J
h^ 0J
h^ 0J
h^ 0J
h^ 0J
h^ 0J h^
h^ 0J
h^ 0J
h^ 0J
h^ 0J
h^ 0J FC C C C C 0D D D D D E EE TE rE E E E 2F HF XF uF F F F G G UG {G G G C C C C C C D D D -D .D D D $F *F V ?V CV DV LV MV NV OV ZV [V `V aV gV hV tV uV vV wV
h^ 0J
h^ 0J
h^ 0J
h^ 0J
h^ 0J
h^ 0J
h^ 0J
h^ 0J
h^ 0J h^
h^ 0J
h^ 0J IwV V V V V V V V V V V V V V V V V V V V V V V V V V V V W W W W W W W W &W 'W 3W 4W 7W 8W >W ?W KW LW MW RW SW XW YW [W \W gW hW iW oW pW }W ~W 4X 5X X X Y Y Y Y Z Z &Z ,Z -Z .Z /Z 0Z 4Z 5Z =Z >Z ?Z
h^ 0J
h^ 0J
h^ 0J
h^ 0J
h^ 0J
h^ 0J h^
h^ 0J
h^ 0J P5X FX TX uX X X X X X Y Y Y Y Y Y Y Z Z &Z Z Z Z Z Z [ K[ L[ T[ b[ [ ?Z LZ MZ NZ SZ TZ UZ ]Z ^Z _Z `Z mZ nZ pZ qZ yZ zZ {Z |Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z K[ L[ [ [ \ \ u] v] ^ ^ d_ e_ 0` 1` ` ` ` a /a a
h^ 0J h^ 5
h^ 0J h^ 5OJ QJ ^J h^ 5CJ OJ QJ ^J aJ h^ CJ OJ QJ ^J aJ
h^ 0J
h^ 0J
h^ 0J
h^ 0J
h^ 0J
h^ 0J
h^ 0J h^
h^ 0J
h^ 0J 7[ [ [ \ 9\ `\ \ \ \ \ \ ] !] 1] K] [] u] v] !^ 8^ R^ i^ ^ ^ ^ ^ ^ ^ _ 1_ 1_ A_ d_ e_ _ _ _ ` #` 0` 1` ` ` h ?h @h Eh Fh Hh Ih Mh Nh Oh Zh [h \h ]h
h^ 0J
h^ 0J
h^ 0J
h^ 0J
h^ 0J h^
h^ 0J
h^ 0J
h^ 0J
h^ 0J
h^ 0J Lc c c c c d Pd id d d d e 3e Ie We Xe e e e &f If |f f f f f f )g [g hg hg g g g g g h h h Oi Pi pi xi i i i i ]j j j j j j k fk gk m m o gd{ ]h ^h _h ih jh oh ph rh sh wh xh yh h h h h h h h h h h h h h h h h h h h h h h h h h i i i i i i i i i i i i i 'i )i .i /i 1i 2i >i ?i Di Ei Fi Gi Hi Li Oi i i i i
h{ 0J
h{ 0J
h{ 0J
h^ 0J
h^ 0J
h^ 0J
h^ 0J
h^ 0J
h^ 0J h^
h^ 0J
h^ 0J
h^ 0J Di i i i i j j j
j j j j j j j j j %j &j 'j (j 3j 4j 5j :j ;j Dj Ej Lj Mj Nj Oj Tj Uj aj j j j j j j j j j j j j j j j j j j k k dk ұ h^ 0J 5B*ph
h^ 0J
h^ 0J 5h^ h^ 0J 5>*
h{ 0J
h{ 0J
h{ 0J
h{ 0J h{
h{ 0J
h{ 0J
h{ 0J
h{ 0J
h{ 0J
h{ 0J
h{ 0J 7dk fk gk k k l l l l l l nm {m m m m =n Dn n n o o o o r r Zs [s s s s s t t Gu Hu v v Uw Vw w w zx {x ]y cy y y y y z z z z { { | | | :~ ;~ `~ ~ = ? @ j k ) 1 ȃ )
h^ 0J >*
h^ 0J 5
h^ 0J h^ h^ 0J B*ph Vo o o o ^q q 3r r r Zs [s Gu Hu v v Uw Vw zx {x 5y z z z z 9{ { { |
& F
^ | | :~ ;~ ? @ j k ݃ D Ʌ K @ ʉ f > ލ Џ
& F
) I U u E P 7 Џ я
Z [ ֖ ז # $ M N O d e ˘ ̘ ץחװװ hh mH nH uh*4 j h*4 U
h^ 0J je h^ Uj h^ Uh^ 0J CJ OJ QJ aJ h^ 0J 5>*CJ OJ QJ aJ h^ h^ 0J 5>*
h^ 0J 5
h^ 0J h^ 0J 5>*OJ QJ ^J 6Џ я G g
1&
& F
U(
Px 4 #\'*.25@9$ Lt !0%(X,/37: &
& F
h ,&
R(
Px 4 #\'*.25@9d
Hp #&,*-T14|8< & Z [ ֖ ז X Y ' & 1&
& F
U(
Px 4 #\'*.25@9$ Lt !0%(X,/37: ,&
R(
Px 4 #\'*.25@9` D l$ (+(/2P69x= ! " * + 1 2 3 4 8 9 K L N O X Y Z [ h*4 hh 0J mH nH uh*4 0J mH nH u
h^ 0J j h^ 0J Uhh mH nH uh^ j h^ U Y Z [ / 1h;0 / =!"#$% D y K y K j h t t p : / / d e v . m y s q l . c o m / d o c / m y s q l / e n / t i m e s t a m p - 4 - 1 . h t m l D y K y K l h t t p : / / d e v . m y s q l . c o m / d o c / r e f m a n / 5 . 0 / e n / l o a d - d a t a . h t m l D y K y K < m a i l t o : r o b e r t t k o n i g @ y a h o o . c o m D y K y K : m a i l t o : d h a r r i s @ d a r w i n d a y . o r g ) d @ d N o r m a l $ *$1$A$ a$ + B* CJ OJ PJ QJ ^J _HaJ mH sH tH D A D D e f a u l t P a r a g r a p h F o n t V i V T a b l e N o r m a l :V 4 4
l a ( k ( N o L i s t D A@ D D e f a u l t P a r a g r a p h F o n t 2 O2 W W 8 N u m 2 z 0 OJ QJ J OJ W W - D e f a u l t P a r a g r a p h F o n t 6 U@!6 H y p e r l i n k B*ph >*$ O1$ s y n t a x d OAd & s y n t a x _ a l p h a s y n t a x _ a l p h a _ r e s e r v e d w o r d \ OQ\ " s y n t a x _ q u o t e s y n t a x _ q u o t e _ b a c k t i c k Z OaZ ! s y n t a x _ p u n c t s y n t a x _ p u n c t _ l i s t s e p X OqX s y n t a x _ q u o t e s y n t a x _ q u o t e _ s i n g l e Z OZ ! s y n t a x _ d i g i t s y n t a x _ d i g i t _ i n t e g e r ` O` $ s y n t a x _ a l p h a s y n t a x _ a l p h a _ i d e n t i f i e r ^ O^ # s y n t a x _ p u n c t s y n t a x _ p u n c t _ q u a l i f i e r d Od & s y n t a x _ a l p h a s y n t a x _ a l p h a _ f u n c t i o n n a m e p Op , s y n t a x _ p u n c t s y n t a x _ p u n c t _ b r a c k e t _ o p e n _ r o u n d r Or - s y n t a x _ p u n c t s y n t a x _ p u n c t _ b r a c k e t _ c l o s e _ r o u n d 0 O0 s y n t a x _ p u n c t . )@. P a g e N u m b e r B b@B H T M L C o d e CJ OJ PJ QJ ^J aJ N O "N H e a d i n g
! x $ CJ OJ PJ QJ ^J aJ 6 B@ "6 B o d y T e x t
" x ( /@!2( L i s t # ^J H "@ BH C a p t i o n
$ x x $ 6CJ ]^J aJ . O R. I n d e x % $ ^J e@ b H T M L P r e f o r m a t t e d 7 &
2 (
Px 4 #\'*.25@9 CJ OJ QJ ^J aJ 4 @ r4 F o o t e r
'
! < ^@ < N o r m a l ( W e b )
( [ [ j k 3 u ~ = \ 0* v: 0* v: 0* v: 0* 0* 0* 0* 0* 0* 0* 0* 0* 0* 0* 0* 0* 0* 0* 0* 0* 0* 0* 0* 0* 0* 0* 0* 0* 0* 0* 0* 0* 0* 0* 0* j k 3 u ~ = ( O ` n o v w * + } ~ 4 I ^ & \ / = @ A V ) ^ / s ^ G H 2 R [ \ N O n 2# 3# G# H# # # $ $ % #% R% S% o% % % % %
& & 6& D& E& w& & & & & & & ' ' N' i' ' ' ' ' ' ( (( R( m( n( ( ( ( ( ) ) ) ) 7) ) ) ) y* z* * * * + + + + + + + , , g, , , , - - - ;- Y- i- u- v- - / r/ / / / / / / 0 0 0 0 "1 L1 1 1 1 1 1 1 1 ?2 3 3 3 3 .4 k4 4 4 4 5 A5 t5 5 5 5 5 :6 }6 6 6 6
7 =7 Z7 7 7 7 7 F8 f8 q8 8 8 8 8 8 9 9 9 79 y9 9 9 9
: : (: ): [: : ; ; ; 0; 1; ; ; ; ; ; 0< < < < < = E= T= r= = = = 2> H> X> u> > > > ? ? U? {? ? ? @ 3@ 4@ w@ @ @ @ A A A B B 5B WB mB nB B B B B C C ,D =D D D D )E FE {E E E E F &F 'F cF sF F F F G ޅ Ї ч G g
Z [ ֎ X Y \ 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 &0 &0 &0 &0 &0 &0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 &0 &0 &0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 &0 &0 &0 &0 &0 &0 &0 &0 &0 &0 &0 &0 &0 &0 &0 &0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 &0 &0 &0 &0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 @ 0 @ 0 @ 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 &0 &0 &0 &0 &0 &0 &0 &0 &0 &0 &0 &0 &0 &0 &0 &0 &0 &0 &0 &0 &0 0 y 0 0 y 0 0 y 0 0 y 0 0 y 0 0 y 0 0 y 0 0 y 0 0 @ '0 y 0 0 y 0 0 " 2 +; B C P :T `U wV ?Z a ]h i dk ) [ M Q T X [ ] _ b d e f h k n o p s v ^ [' . 1 Y5 9 ? C G FM rQ 5X [ 1_ c hg o | Џ Y [ N P R S U V W Y Z \ ^ ` a c g i j l m q r t u w Z O " " .# Q$ $ $ ; < -< # N d [ XXXX 4 B M c m v } ! : D@ @ d@ 0 0 1 1 \ 0 0 1 1 \ 9 *urn:schemas-microsoft-com:office:smarttagsplace 8 *urn:schemas-microsoft-com:office:smarttagsCity w | J W
6 C S
\
G J ^ N V Q \ a k x
( 5 : I J ] d w h o ; >
# m p ? J _ q 7 E ) > K O T ! ! K" P" " " u# # G$ L$ % !% W% c% v% % & & & & & & ' ' ' ' 1' =' @' M' ^' g' m' ' ' ' ' ' ' ' ( ( ( ( ( ( ( ) f) o) ) * * * %* ** 6* ;* F* Q* ^* f* * * * * * * * * * * * + L+ T+ + + + + + + + + + , , , v, , , , , , , , , , , , , - - - / / H0 R0 0 0 0 0 2 2 2 2 3 3 "3 *3 Z3 g3 3 3 3 3 3 3 4 ,4 4 4 4 4 4 4 4 4 4 4 4 5 5 5 5 25 65 ?5 E5 X5 [5 s5 x5 5 5 5 5 5 L6 T6 W6 c6 e6 p6 r6 |6 6 6 6 6 6 6 6 6 6 6 6 7 7 !7 $7 <7 A7 U7 f7 q7 u7 7 7 7 7 7 7 7 7 7 7 7 x8 8 8 8 8 8 ?9 I9 N9 V9 Z9 i9 9 9 9 9 9 9 9 9 u: : : : : : : : : : : : ; '; 8; @; I; Q; Z; e; o; w; ; ; ; ; 4< ?< < < = 0= 9= D= Z= k= u= = = = h> s> {> > > > > > > > > > > > ? ? ? ? ? @ @ (@ l@ u@ @ @ a Ia Na a a a a b b ;b Lb Ob \b bb ob b b b b c c c d %d ,d d d d d d d d d =f Df f f
g g \g dg yg g g g h h Nh ]h h h i i i i i i j j j j k k yl l m m m m ;n Fn jo qo p p q q q q q q Er `r r r !t -t xu u x x y &y ){ 1{ { { { { { { } } } } ~ ~ *~ 0~ 3~ 6~ V~ \~ _~ b~ ( 0 ր ܀ u z J O Ƀ ̃ ل ݄ І ۆ / > ؇ d k a c e o Y \ ! (
= H 0 5 f i | N R # # $ $ % % <& C& & & & & &
' ' ' ( ( ( ( ( ( >) D) * * * * + + }, , B- H- . -. / / / / / / 0 0 1 1 1 1 ?2 2 3 3 a4 j4 56 96 :6 J6 7 %8 M8 V8 q8 8 I9 L9 : : B; H; 9= D= = = H> W> U? z? ,@ 2@ @ @ YA kA A A 5B BB B B B B C C C C ,D ,, To move newest signups (supporters and emailC 2 R 2 2 , 8 , I , ' ! ' 2 8 8 8 ' ! ' 8 8 8 2 , ! , , ' 2 8 8 , R 2
2
> --! P 2
> . only) from the remote to the mailing database:2 8 2 ! " , 2 R ! 8 , , - R 3 ! , ! 2 ! 8 , R 2 8 2 8 2 ! 2 8 2 ' , ! - @ !
I , -
2
> -6 -
2
, -- - 2
"
, RemoveC , N 2 2 = 2
"
M! e the duplicates from the remote , 2 , 2 2 2 , , , ' " " 2 N 2 , ! , N 2 , - 2
"
q persons*8 , , ' 2 8 ' - 2
"
database. 2 , , 2 , ' ,
2
"
B
-. m 2
,A Select all the fields of the persons on the remote database using,8 , , , , 2 , ! , 2 ' 2 ! 2 - 2 , ! ' 2 2 ' 2 2 2 , ! , N 2 , 3 , , 2 , ' , 2 ' 3 1
2
-. 2
, SELECT *8 > ; = C = 2
2
-. 2
{ , FROM `persons`7 C H Y 2 , ! ' 2 2 ( !
2
{ -.
2
, --
2
a , -- - = 2
,! FOR ALL GROUPS OF PROFESIONALS E < N H H C C M H N I < 8 N < < H O < C 8 ' N H H C 8 C
2
--! 2
MAILINGS:^ H ' C ' H M 8 ! - @ !
, -
2
! -7
2
I
, -6 - P 2
,. METHOD 1 [Now outdated. Use Method 3 instead]:Y = = H H H 2 " H 2 H 2 2 2 - , 2 H ' , Y , 2 2 2 2 2 ' , - 2 "
2
l -,
2
-, -- ) 2
, Use a SQL SELECT comH ' , , 8 I < 8 > < = C = , 3 N 2
0P mand to retrieve the desired names from the REAL database on the remote machine N , 2 2 2 ! , ! , 2 , 2 , 3 - ' ! , 2 2 , N , ' ! " 2 N 2 , C = I ; 3 , , 2 , ( , 2 2 2 , ! , N 3 , N , , 2 2 , 2
,m (see one example code below). Use the MySQL administrator program to Export the desired fields in .csv data ! ' , , 2 2 , , 3 , N 2 , , 2 2 , 3 , 2 H ! H ' , 2 , [ 0 8 I < , 2 N 2 ' ! , 2 ! 2 ! 3 1 ! , N 3 = 3 2 2 ! 2 , 2 , ' ! , 2 ! , 2 ' 2 , ' 2 2 , , p 2
,C format. Set Fields Terminated By tabs to '|', no enclosing of fiel ! 2 ! N , 8 , 7 , 2 ' = - ! N 2 , , 2 D 0 , 2 ' 2 2 2 , 3 , 2 ' 2 1 2 ! ! , X 2
- 3 ds, the other fields as their defaults. Check the 2 ' 2 , 2 2 - ! ! , 2 ' , ' 2 , ! 2 - ! , 2 ' C 2 , , 2 2 , 2
,q box for "Put field names at first row" if you expect to need the reminder of what the fields/columns represent. 2 2 3 ! 2 ! ( 8 2 ! , 2 2 , N , ' , ! ! ' ! 2 I ( ! 0 2 2 , 3 2 , - 2 2 , , 2 2 , ! , N 2 2 - ! 2 ! H 2 , 2 , ! , 2 ' , 2 2 N 2 ' " , 2 ! , ' , 2 2
l,\ Save the file on your local hard disk as "Whatever.csv", checking "Save as File" with "None"8 , 2 , 2 , ! , 2 2 0 2 2 ! 2 - , 2 , ! 2 2 ' 2 , ' ( _ 2 , , 2 - ! , ' 2 ( , 2 - , 2 2 1 ( 8 , 3 , , ( 7 , ( H 2 ( H 2 3 , ( 2
l
compression. , 2 N 3 ! , ' ' 2 2
2
l --
2
, -- - 2
R, The program = 2 , 2 ! 3 1 ! , N - # 2
RK statestocodes.pl( ! 2 ! , ' ! 3 , 2 8 , ' 8 - 2
RS will translate a column of state names (upper or lower case) into the 2 character H ! , 2 ' , , , , 2 3 N 2 2 ! ' , , 2 , N , ' ! 2 2 3 , " 2 ! 2 H , ! - , ' , ! 2 2 2 , 2 , 2 , ! - , , ! ( 2
, USPS abbreviations.mH 8 8 8 , 2 2 ! , 2 , 2 2 '
2
h --
2
8, -- 2
,M To make the insertion (loading) of data, either use programs of the style of n= 2 N , 2 , 2 , 2 ' , ! 2 2 ! 3 , 2 2 1 ! 3 ! 2 , , , 2 , ! 2 ' , 2 ! 2 2 ! , N ' 2 ! 2 , ' 0 , 2 !
2
--
2
, -- 8 2
, For a mailing to all the conta7 2 ! , N , 3 1 2 , 2 , - 2 2 , ( 2
ct persons in 2005:m, 2 , ! ' 2 2 ' 2 2 2 2 2
2
-- 2
, SELECT8 > ; = C =
2
- 2
`contact` , 2 2 , , 2
, 2
I `email`*! , N -
2
i -
2
-L 2
w, FROM7 C H Y
2
wG - 2
wa `event`* , 3 , 2
2
w{ -
2
w -. 2
, WHERE *_ H = C = 2
YEARH = H C
2
(-!
2
- 2
eventdatetime, 2 , 2 3 , , N ,
2
= -
2
V )-! 2
w = '2005' 8 2 2 2 3
2
-L 2
], ORDER H C H = C
2
] - 2
] BYB H
2
]" - 2
]< 'email'* , N ,
2
]= -L 2
, LIMITl= [ =
2
A -
2
Z 0-2
2
-
2
,-
2
- 2
400 2 2 2
2
m -.
2
C, -- 2
, SELECT8 > ; = C =
2
- 2
`eventsernum` , 3 , 2 ' , ! 2 2 O
2
-
2
,-
2
' - 2
@ `contact`! , 2 2 , ,
2
-
2
,-
2
- 2
`email`*! , N ,
2
-
2
' ,-
2
@ - 2
Y YEARH > H C
2
j (-!
2
- 2
eventdatetime, 2 , 2 2 , , N ,
2
-
2
)-! 2
2
@ -L 2
), FROM7 C H Y
2
)G - 2
)a `event`* , 3 , 2
2
){ -
2
) -L -
՜. +,D ՜. +,h $ h p
Darwin Day Celebration a S ' (There is also a file called Essential Title l 8 @ _PID_HLINKS A $ < m a i l t o : d h a r r i s @ d a r w i n d a y . o r g K } m a i l t o : r o b e r t t k o n i g @ y a h o o . c o m R 6 h t t p : / / d e v . m y s q l . c o m / d o c / r e f m a n / 5 . 0 / e n / l o a d - d a t a . h t m l V Y 5 h t t p : / / d e v . m y s q l . c o m / d o c / m y s q l / e n / t i m e s t a m p - 4 - 1 . h t m l
! " # $ % & ' ( ) * + , - . / 0 1 2 3 4 5 6 7 8 9 : ; < = > ? @ A B C D E F G H I J K L M N O P Q R S T U V W X Y Z [ \ ] ^ _ ` a b c d e f g h i j k l m n o p q r s t u v w x z { | } ~ R o o t E n t r y F P֑0aQ D a t a
y 1 T a b l e W o r d D o c u m e n t 1 S u m m a r y I n f o r m a t i o n ( 04 D o c u m e n t S u m m a r y I n f o r m a t i o n 8 C o m p O b j q
F Microsoft Office Word Document
MSWordDoc Word.Document.8 9q