What is this?

A quick and dirty attempt (based on some existing code that I use for the dynamic version of the BSFA Magazine Index, and also to display the contents of my SF magazine collection) to display the ISFDB MySQL database.

Release Notes

What it does

  1. Displays list of magazines by initial letter
  2. Displays list of authors by initial letter of surname. See Issues #4.
  3. Displays contents of a magazine issue
  4. Displays items by selected author
  5. Links to authors & publications on items work
  6. Search page now works (10/1/05)
  7. Reviews & interviews now listed within magazine issue (12/1/05)
  8. Display interview list for author (14/1/05)
  9. Decode the different short fiction lengths in issue contents (22/1/05)
  10. Show notes after titles (22/01/05)
  11. Display biographic data for author (23/01/05)
  12. Display awards data for author (23/01/05)
  13. Handle ^ in author names and titles (23/01/05)
  14. Display interviews conducted by author (for 20050129 schema) (30/01/05)
  15. Display reviews written by author (for 20050129 schema) (30/01/05)

What it doesn't (currently) do

  1. Decode special character sequences in note text
  2. Display interviews conducted by author (for pre-20050129 schema)
  3. Display interviews of author (for post-20050128 schema)
  4. Display awards data for items - currently shows list of ids of awards
  5. Reviews not indicated against items
  6. Display reviews written by author (for pre-20050129 schema)
  7. Display reviews of author's books (for post-20050128 schema)
  8. Detailed info about individual magazines isn't shown. This isn't in the database, and on the online ISFDB only exists on the static HTML page for that magazine
  9. Decode the issue/pub tag under an item into something readable

Installation

Pre-Requisites

What to do

Assuming that you already have web server, PHP and MySQL all working together:

  1. Download the ISFDB MySQL Database from the ISFDB web site, and use it to load the ISFDB data into a new database
  2. Download the code (from http://www.michaelcross.me.uk/devarea/)
  3. Create a web site called eg isfdb within the web server, and unzip the code into it
  4. Create the magazines table in the isfdb database using the magazines.sql file. NB This does now have the full complement of magazine titles.
  5. Edit the MySQLVars.php3 file to reflect the details of your installation (eg MYHOST, MYUSER, MYPASSWORD, SCHEMAVER)
  6. Point your browser at /isfdb (or whatever you called your site)

Issues

1. Parsing the magazine issue tag
  -   Most of the magazines have issue tags of the form pppmmmyy or pppmmmyyyy, where ppp is the magazine's prefix, mmm indicates the month (usually) of the issue, and yy/yyyy is the year. NB ppp and mmm aren't fixed length. Some of them are just pppnnn, where nnn is a variable length number which sometimes(?) is issue number and year.
 
2. Selecting magazine titles using the beginning chars of the pubs tag
  -   Some magazines have prefixes which are the same as the beginning of other magazines and/or publications, so searching for that magazine's tag finds extra pubs entries.
 
3. Ordering magazine issues within a year
  -   See 1 above for details of issues tag formats.

One answer would be to process the magazine issues data in the pubs table with some code and generate an extra column containing the issue's sequence number within the year.
 
4. Selecting Authors using the first letter of their surname
  -   The author table has 2 forms of the author's name:
  1. canonical - the normal form (eg David Brin)
  2. legalname - the author's true name in 'surname, forenames' form (eg Brin, David)
Selecting on the canonical form for the first letter of the surname is problematical, so, for the time being, I'm selecting on the first letter of the legalname column. This works well, unless someone's legalname is significantly different from their canonical name. eg Piers Anthony's legalname is 'Jacob, Piers Anthony Dillingham' so he, unfortunately, is listed on the J page.

Also, there are over 29,000 entries in the authors table whose legalname is blank, so they won't appear on the alphabetical author lists!

One answer would be to process the data in the authors table with some code and generate an extra column containing the first letter of the surname.
 
5. Sorting Authors' names
  -  
 
6. Sorting Titles
  -  
 
7. Listing pub tags under each title becomes unwieldy when there are a lot of them. This often happens for books
  -   May only show them for non-book items, and have a link to a separate publishing info page for those?