Times Colonist Transcript Database

In the database, deleted all the records in the topics table that had no instances pointing to them (either never had any or the topic had been collapsed into another topic, e.g. ChHx into Chinese History).

Checked all the ids for the about to be deleted topics to ensure no instances pointing to them in the articles table. Found only one instance of a pointer to id 148 (originally coded as "X" by author), which I deleted from the field in the articles record.

Updated topics.txt, and sent JL and email saying if he wanted to collapse any more topics, I'm pretty confident we could do it without problem, though we are no longer able to use the text files to rebuild the db content if necessary, as they contain pointers to non-existant topic id's, though a bunch of global search and replaces would solve that.

Discovered I had not incorporated the 1896 data into the database, so did that.
Extracted text from .rtf file provided by JL.
Ran it through the process described in HowToProcessColonistFiles.txt on my Mac, with particular attention to normalizing the contractions Leona uses a lot in the transcripts, and to assigning the correct topic numbers in place of the topic codes she uses.
To import into the database, noticed that the transcript and cemetary fields were not in the order the db expected and also that the current version of mySQL requires a carriage return at the end of the last line (the previous one did not), so had to redo the upload after sorting those issues out. Settings for upload are found in sql_for_load_data.txt file on my Mac.
Tidied up the file topics.txt which contains all the topic codes and numerical ids
Created the contractions.txt file which contains all Leona's contractions and standardarized plain english substitutions.

filter by topic code, in particular second and subsequent code of records which contain more than one topic code

process data for 1896 and 1897 and upload into database - done Dec. 2007 / Jan 2007

article 52989 has topics field 148,91 article 54104 has topics field 92,148 SELECT * FROM articles WHERE 148 IN (articles.topic) returns record 52989 SELECT * FROM articles WHERE 91 IN (articles.topic) returns no records SELECT * FROM articles WHERE 92 IN (articles.topic) returns record 54104 Still have to figure out a way to find the nth item in that field

On David's suggestion, I used a syntax like this:
SELECT * FROM articles LEFT JOIN topics ON topics.topicId = articles.topic
instead of the previous syntax:
SELECT * FROM articles,topics with a complicated AND clauses in the WHERE clause
as the COUNT query encompassing both tables exploded with the old syntax.

Currently the topics machinery presents the topics in alphabetical order and allows the user to select as many as they want (using an OR operator between each). It successfully handles one argument, but can not handle more than one argument.
What I think I'll do is use an "IS IN" operator to handle multiple arguments coming from the query interface.

Put plain-english values in the name field for each record in the topics table.

Added the following code:
Gold 151

These codes are redundant, so any future instances should be remapped as follows:
Artist 3 --> 2
Ch 31 --> 30
Grk 55 --> 53
LgHx 64 --> 63
Naturalized 81 --> 80
Postal 96 --> 93
RCNX 102 --> 101
RRHx 108 --> 107
School 111 --> 43
SkHx 113 --> 112
SVet 117 --> 115
Teacher 120 --> 43
Vet 132 --> 128
VetLetter 133 --> 135
VXL 139 --> 136
XV 150 --> 138

These codes had no instances, so I'm going to remove them from the table of topics:
BD 19 remove
C 27 remove
Death 37 remove
DM 40 remove
HuHx 57 remove
JT 61 remove
LUSH 69 remove
Misc 74 remove
RW 109 remove
X 148 remove
For now, I've preceded them with "XXX-" so they sort to the bottom of the list. I'll actually delete them when I'm sure no problems will result from doing so.

Put GUI elements in for selecting Topics (still haven't updated topic list).
Rudimentary code to take one selected value and write where clause based on that to hidden form element (similar to other filters, but for now passing in the string rather than the index int value).
Modified search.php to include that information in the SQL statement, but get a "can't find topics table" error. When I copy and paste the identical query into the phpMyAdmin sql query window, the query works.

SELECT collections.collectionName, newspapers.newspaperName, authors.authorName, topics.*, articles.* FROM collections,newspapers,authors,topics,articles WHERE articles.transcript LIKE '%teach%' AND (topics.topicName = 'Education') AND collections.collectionId = articles.collectionId AND newspapers.newspaperId = articles.newspaperId AND authors.authorId = articles.authorId AND topics.topicId = articles.topic ORDER BY pubDate ASC LIMIT 20

David suggests using a FROM clause on the articles table with an explicit LEFT JOIN argument for each of the additional tables with an ON clause equivalent to the current AND statement instead of the current FROM with all the tables and numerous AND statements.

Will try that tomorrow.

Went through listing of topics, normalizing duplicates, removing items Leona said were errors, and adding entry for Gold. Also thinking about ways to present the list of topics alphabetically to user - may use an alpha field or may just rely on sorting. I'll do some tests and see.

went through list of topics provided by Leona, assigned new index numbers based on alphabetical order of full text entries and excluding those topics which she instructed should not be included, and adding one new topic "Gold". Emailed her for confirmation before actually making any changes in db. Assuming she's ok, then I'll need to renumber all the current values (and there may be more than one in the topic field of any given record) to a temp value and then renumber those temp values to the final values (which in the case of those topics being eliminated will be null

I need to:
- renumber the remaining topics
- in the database do a number of search and replaces so that the values in the "topic" field for each record are correct
- put the text for each topic into the appropriate table.
Then ready to go back to code for query and reporting on this field.