Join CPHA

CPHA Annual Conference

Preparing abstracts and presentations

Presentations and abstracts are kept in the resources database (MySQL) and accessed by programs (PHP) kept at http://resources.cpha.ca/CPHA/Conf/Code. NOTE: There are many inconsistencies in how the conference abstract and presentation data are kept in the database, due to changes in requirements every year. Some rethinking and cleaning up is advised!

Database tables and fields

The tabes used are:

Main: This table contains one record for every presentation, and is the main information source for the presentation.
Details: This table contains the abstract in earlier conferences.
Persons: This is the table in which all person's names are fully entered. There is only one entry per person, regardless of how many presentations (or other things, e.g., publications) the person is making (and regardless of the year of the publication)
AuthorRels: Links the person(s) to the presentation. Tyhe sequence in which the authors/presenters are listed is also kept here.
URIrels: Contains the links to the abstracts and presentations. There can be several for each presentation. Contains the ID in Main.
Times: Contains one entry for every time slot in the conference that is used for a presentation. Date and time is in two languages.
Sessions: Contains the information for each session in which presentations are made. This includes the time slot, the type of session, the room assigned and the name in two languages.

The table keys are:

Main: ID (Ayy-###, where yy is the 2-digit year)
Persons: PersonKey (integer)
Times: SlotID (integer)
Sessions: SessionNo (character 6)

As well, the following fields contain the 4-digit year of the conference, which is often required to uniquely select an item: Main.Unit, Times.Unit and Sessions.Unit, and must be used to uniquely identify records in Times and Sessions.

The tables are linked as follows:

Details.ID ties the abstract to the record in Main.
AuthorRels.MainKey and AuthorRels.AuthorKey ties the authors/presenters to the record in Main.
Main.SessionNo and Sessions.SessionNo ties the session to the record in Main.
Sessions.SlotID and Times.SlotID ties the sessions to a time slot.

The following fields are used to sequence information in displays:

Main.Sequence: order of presentations within a session.
Times.SlotID and Sessions.SessionNo: Order in which sessions are listed
AuthorRels.Sequence: Order in which authors and presenters are listed with a presentation.

table name	field name	contents
Main	ID	A unique ID given to any one presenter's session. format: Ayy-nnn, where yy is the 2-digit year, and nn is a 3-digit unique number. Starting in 2009, when EventsPro was first used, nnn matches the EventsPro field SPDOC.Speak_ID (Speakers ID), which EventsPro also uses to name the abstract files it creates.
	BelongsTo	Always set to Conf
	Unit	Always set to the 4-digit year
	TitleE	English title if present. Use entities instead of special characters.
	TitleF	French title if present. Use entities instead of special characters.
	Unit	Set to the four-digit year of the conference
	Status	Always set to O
	ShowLang	Always set to B
	Lang	Set to E for English title, F for French title
	SessionNo	Set to the key value in table Sessions when this item is assigned to a session
	TitleESort	Set to the upper case characters of the value in field TitleE. Accented characters and entities are replaced with appropriate letters. Leading English articles and leading special characters are removed.
	TitleFSort	Set to the upper case characters of the value in field TitleF. Accented characters and entities are replaced with appropriate letters. Leading French articles and leading special characters are removed.
	Sequence	An integer identifying the order in which this presentation is to be displayed in relation to others in the same session. Otherwise, set to 1.
Persons	PersonKey	A unique integer ID given to the presenter. Duplicate persons can be removed from the database using program DuplicateCheck.php.
	LastName	The person's last or family name. Use entities instead of special characters.
	OtherNames	Other names and or initials, if known. Use entities instead of special characters.
	NameSort	Upper case version of name, for sorting and searching purposes. Accented characters and entities are replaced with appropriate letters. If both LastName and OtherNames fields contain values, this field is formed using "LastName, OtherNames".
	Honorific	Optionally contains such values as 'Dr', 'Dre', 'Ms.', etc., in the language of the person.
AuthorRels	AuthorKey	The key of the record describing the presenter in table Persons
	MainKey	The key of the record describing the presentation in table Main.
	Sequence	An integer identifying the order in which this presenter is to be displayed with the others when there are multiple persons associated with one presentation. Otherwise, set to 1.
URIrels	MainKey	The key of the record describing the presentation in table Main.
	URIType	Always set to Conf
	ItemType	Set to 'PDF', 'PPT', 'DOC', etc.
	URI	Set to the item's filename. This is always lower case, and consists of: for presentations: the value of MainKey in lower case, followed by a one-letter language code, followed by the the file type extension in lower case. e.g. a09-125e.ppt for abstracts: the value of MainKey in lower case, followed by 'a', followed by a one-letter language code, followed by the file type extension in lower case. e.g. a09-125ae.pdf The language code is: e: English f: French b: bilingual m: mixed (some in English, some in French, but nothing in both. See example)
	Sequence	An integer identifying the order in which this presentation file is to be displayed with the others when there are multiple files associated with one presentation. Otherwise, set to 1.
Times	SlotID	The key of the record describing the time slot. Typically assigned: 1 to 9: concurrent oral abstracts 10 to 30: all other time slots for opening, plenary, planned and closing presentations 900 to 910: poster presentations
	Unit	Set to the four-digit year of the conference
	DateandTimeEn	The date and time, as it is to be displayed on the website in English
	DateandTimeFr	The date and time, as it is to be displayed on the website in French
Sessions	SessionNo	The key of the record describing the session. Typically assigned: concurrent oral abstract sessions: two letters representing the stream or priority area, and one digit representing the sequence. e.g. PH3 opening, plenary, planned and closing presentations: 900 to 999. poster presentations: 800 to 899.
	Unit	Set to the four-digit year of the conference
	Type	set to the type of presentation: O: oral abstract presentations P: poster presentations T: plenary presentations. In 2009, also includes all other planned presentations S: all other planned presentations, except 2009
	Session	In spite of its original intention, this field is used for the sequence, when listing presentations (there are occassionally more than one file to a presentation. Otherwise, set to 1.
	SessionNameEn	The name of the session, as it is to be displayed on the website in English
	SessionNameFr	The name of the session, as it is to be displayed on the website in French
	SlotID	The key of the record in table Times describing the time slot.
	Room	The room number
	SortNameE	Value in SessionNameEn converted to upper case
	SortNameF	Value in SessionNameFr converted to upper case

Data retrieved from the EventsPro database

A spreadsheet, presentations.csv, is created by running an EventsPro utility report (EventsPro » Utilities » Speakers » Export Speaker's Data - Ed Brandon) and stored with the conference data in resources (http://resources.cpha.ca/CPHA/Conf/Data/presentations.csv). This CSV file is manually updated to include

the language of the abstract
the language of the presentation
the language of the title in the program
All special characters are converted to entities
non-breaking space entities are inserted as appropriate (e.g. before ":" in French titles)
Single and double apostrophes are converted to appropriate left and right entities
Titles entered in all upper case are converted to normal title mode capitalization, based on language.
Session codes are converted to 2 characters and one digit as required, to match the codes in table Sessions

Programs used to load the data (pre conference)

These programs reside in http://resources.cpha.ca/CPHA/Conf/Code.

program name	purpose
Build2009_A.php	Build the entries for the oral abstract presentations in tables Times and Sessions. The data is hard-coded in the program. SessionNo values must match those used in the CSV file described above.
Build2009_B.php	Build the entries for the oral abstract presentations in tables Main, URIrels, Persons, and AuthorRels. The data is obtained from the CSV file (http://resources.cpha.ca/CPHA/Conf/Data/presentations.csv). The program ignores the first record (column headings), and all records without a session code (abstracts not approved)
Build2009_C.php	Build the entries for the planned presentations in table Sessions. The data is hard-coded in the program. The values for table Times are used from those built in program Build_A.php, and therefore not handled here.
Build2009_D.php	Build the entries for the planned presentations in tables Persons, AuthorRels, URIrels and Main. The data is hard-coded in the program.
Build2009_E.php	Updates the data in table URIrels. The data is obtained from what is already in the database and a DOS-level directory listing file, http://resources.cpha.ca/CPHA/Conf/Data/dir.txt, described below.
Build2009_F.php	Build the entries for the plenary presentations in tables Sessions, Persons, AuthorRels, URIrels and Main. The data is hard-coded in the program.
Build2009_Delete.php	Delete all records created by the above 6 programs, especially during testing. NOTE: This program does not delete records from tables Persons, and AuthorRels. See notes below under Complete list of steps in preparing the data to delete these.

Loading the presentation and abstract files

It is best to first prepare all the files (PowerPoint, PDF, etc.) in one directory on your hard drive. This directory should contain nothing else.

Obtain the abstract presentation files from the EventsPro folder containing the PDF versions.
The files are renamed as necessary, to conform to the naming conventions:
- for presentations: the value of MainKey in lower case, followed by a one-letter language code, followed by the file type extension in lower case
- for abstracts: the value of MainKey in lower case, followed by 'a', followed by a one-letter language code, followed by the file type extension in lower case
The language code is:
- e: English
- f: French
- b: bilingual
- m: mixed (some in English, some in French, but nothing in both. See example)
To help with locating French, mixed and bilingual presentations, use Windows Explorer to search for é, è and/or à. The files so located should be checked to ensure that they are not English presentations containing French names of persons or institutions.
After all the files are properly named, a DOS DIR command is run, with the resultant file called dir.txt. This file is edited to remove all lines that do not describe the presentation and abstract files, and then loaded into http://resources.cpha.ca/CPHA/Conf/Data/dir.txt
The files (PowerPoint, PDF, etc.) are loaded into http://resources.cpha.ca/CPHA/Conf/Data.

Complete list of steps in preparing the data

Use these steps iteratively, as required, until everything is loaded correctly.

Edit the hard-coded session information as required in programs Build2009_A.php, Build2009_C.php, Build2009_D.php and Build2009_F.php
Load the presentation and abstract files, renamed as necessary, into http://resources.cpha.ca/CPHA/Conf/Data, as described above.
obtain the presentations.csv file from EventsPro/Utilities, edit it as required (described above), and load it into http://resources.cpha.ca/CPHA/Conf/Data
obtain the dir.txt directory listing file, edit it as required, and load it into http://resources.cpha.ca/CPHA/Conf/Data
Edit programs Build2009_A.php, Build2009_B.php, Build2009_C.php, Build2009_D.php, Build2009_E.php, Build2009_F.php and Build2009_Delete.php, to reference the test or production databases as required:
- activate the appropriate include file, and comment out the other, to access the desired database. The PHP statements are always found under "// Connect to the database".
- Set variable $Year to the 4-digit year of the conference.
- The programs that update the Persons table, ( Build2009_B.php and Build2009_F.php), add them using the next available key. This value is kept in variable $AuthorKey in these programs. The next available key is obtained after the connection is made to the database. During testing, it is convenient to set these to high values (9xxx), and this is done in a statement close behind that in which the next available key is obtained. Comment out this statement if going to production, otherwise ensure this statement is active in these programs.
Delete the records created in the database if this is a rerun. Proceed as follows:
- Delete all records in table Persons created for this conference in the previous run. Check table URIrels, looking for all entries with MainKey starting with "Axx-", where xx is the low-order 2 digits of the conference year.
- Run program Build2009_Delete.php to delete all other records
Run programs Build2009_A.php, Build2009_B.php, Build2009_C.php, Build2009_D.php, Build2009_E.php and Build2009_F.php, in this order, to (re)load the data.

Displaying abstracts and presentations

The following programs are used to display the presentations and abstracts (post conference). The inconsistencies referenced above apply here too, and a review of how these are displayed would be useful. The 2009 display programs are derived from others used with 2007 and 2008. e.g. Presenations_2009_e.php is derived from Presentations_e.php.

Main Programs

These programs reside in http://resources.cpha.ca/CPHA/Conf/Code.

program name	purpose	where called
Presentations_2009_e.php, Presentations_2009_f.php	Used as the main display for the presentations. Users can search by author, or access presentations by session or by key priority area.	external reference plugin on conference site's "presentations" page.
MainPresentations_2009_e.php, MainPresentations_2009_f.php	Displays the plenary and planned (thematic) presentations. There are no abstracts for these.	Presentations_2009_e.php, Presentations_2009_f.php
SessionPresentations_2009_e.php, SessionPresentations_2009_f.php	Lists the oral abstract presentations for a particular session.	MainPresentations_2009_e.php, MainPresentations_2009_f.php
PosterIndex_e.php, PosterIndex_f.php	Lists and links to the poster presentation abstracts, by date. NOT USED IN 2009.	Presentations_e.php, Presentations_f.php

Parameters passed to and between programs

Passed parameter	Program variable	Description
y	$Year	4-digit year
l	$LangUser	The language of display
name	$Name	name being serached for by end user, otherwise an empty string.
order	$Order	either stream' or 'time'. Is the unit of display for sessions. Stream = key priority area or conference track. Time = One time slot. Used with next item.
session	$Session	If $Order=='stream', this is the code of the conference stream. $Order=='time', this is the time slot code.
r	$Rooms	true or false. Display room information.
f	$Form	The format in which one presentation is to be displayed.
Ian	$Ian	If true, format items for Ian to use in preparing the final program.

Conference-specific functions called by the programs

These functions reside in http://resources.cpha.ca/CPHA/Conf/includes.

code name	purpose	where called
OneItemLinks_2009_e.txt, OneItemLinks_2009_f.txt	Format the links to the abstract and presentation files.	Presentations_2009_e.php, Presentations_2009_f.php, MainPresentations_2009_e.php, MainPresentations_2009_f.php, SessionPresentations_2009_e.php, SessionPresentations_2009_f.php
Format_2009_e.txt, Format_2009_f.txt	Format one presentation item, with link	Presentations_2009_e.php, Presentations_2009_f.php, MainPresentations_2009_e.php, MainPresentations_2009_f.php, SessionPresentations_2009_e.php, SessionPresentations_2009_f.php
Conf_head_2009_e.txt, Conf_head_2009_f.txt	web page header info	Presentations_2009_e.php, Presentations_2009_f.php, MainPresentations_2009_e.php, MainPresentations_2009_f.php, SessionPresentations_2009_e.php, SessionPresentations_2009_f.php
Authors.txt	Format one presenter	OneItemLinks_2009_e.txt, OneItemLinks_2009_f.txt

CPHA generic functions called by the programs

These functions reside in http://resources.cpha.ca/_shared/includes.

code name	purpose	where called
open_db.txt	Open the MySQL database. This code also includes all functions to access the data via ODBC SQL statement calls.	Presentations_2009_e.php, Presentations_2009_f.php, MainPresentations_2009_e.php, MainPresentations_2009_f.php, SessionPresentations_2009_e.php, SessionPresentations_2009_f.php
ToUpper.txt	Use to convert the user's typed last name to upper case to locate it.	Presentations_2009_e.php, Presentations_2009_f.php
GetReaders_e.txt, GetReaders_f.txt	Used to display the reader information at the bottom of a page (get Adobe, etc.)	Presentations_2009_e.php, Presentations_2009_f.php, MainPresentations_2009_e.php, MainPresentations_2009_f.php, SessionPresentations_2009_e.php, SessionPresentations_2009_f.php
PageFoot.txt	final HTML code for the web page.	Presentations_2009_e.php, Presentations_2009_f.php, MainPresentations_2009_e.php, MainPresentations_2009_f.php, SessionPresentations_2009_e.php, SessionPresentations_2009_f.php