The Multi-Year Science Fiction Magazine Database Extravaganza Endlessly Continues II

Screen shot of the revised main page on my testing version. As much as I hated to do so, I removed the Galaxy cover graphic as being too distracting. Now I have a lot more "real estate"  for displaying a magazine's data. I will continue to display the Galaxy cover as the opportunity arises and where it will fit-in.

Other enhancements, from the prior version:

1. Edit issue and story data directly from the interface.
2. Edit the names of the story author and the cover artist from the interface.
3. Add the names of new authors and new cover artists from the interface.
4. Add missing stories to an issue from the interface.

Previously, to edit and/or add certain data such as those identified above, had to be done through phpMyAdmin.

The Multi-Year Science Fiction Magazine Database Extravaganza Endlessly Continues

I now have a rudimentary version of a Science Fiction Magazine Database that works through your (internet) browser. The benefit of this approach is that it works on your home computer and/or LAN, does not require specialized database software such as Base or MS Access, and (at some future point) will be internet ready. However, it does require that you have MySQL and Apache (LAMP/WAMP) operating on a computer. There is still much work to be done.

Opening Screen

The image above is the opening (main) screen.  From the opening screen one can do an author or story search. Additionally, one can display all magazine issues or filter by magazine.

Eventually, subsequent screens (as you can see below) will need to be redesigned to remove the Galaxy cover as it tends to be distracting in subsequent screens.

Note: The "Author" field contains a hyper-link. Clicking on it will display all stories written by that author.

Listing of Each Magazine Issue in the Database

My apologies to those reading other magazines. I have just been tracking Analog. The image below displays some of the F&SF magazines that have already been entered. Please be assured that this database can handle all magazines.

Note: The "Magazine Name" field contains a hyper-link. Clicking on it will display all stories for that particular issue.

Filtered Issue List

Selection of an Author

Display of all Stories Written by the Author Selected

Selection of a Magazine Issue

Display the Contents of the Selected Issue

Listing of All Stories Having "Pluto" in Their Title

Note: In the screen above there are hyper-links for both the author and magazine issue. Consequently, one can lookup either the author stories or the contents of the magazine issue.

This project still needs a lot of work. For example, I have not yet gotten around to developing editing screens. Turns out editing is much more complicated than simple data retrieval. Editing, when it is done, has been done through phpMyAdmin which works directly with MySQL.

Additionally, it seems that I will need to learn JavaScript for certain actions. Drat, yet another programming language to learn!!!

This project is essentially for my self edification. A more extensive and complete source of information is located at the Internet Speculative Fiction Database (ISFDB).

I anticipate that this browser based approach will be the final rendition in the development of this database. But then one should never say never. Eventually, I intend to complete it. But don't hold your breath.

Should you have any comments, please leave them.

Finally - Science Fiction Magazine Database Ported to Linux

Finally, I have the Science Fiction magazine database working under Linux (Ubuntu 14.04 and LibreOffice  4.3.3.2). Now I no longer need MS Access for maintaining the database. Completing it has proven to have been more complicated and taken a lot longer than expected. Steep learning curve. Prior posts on this topic.

I do not anticipate embellishing the current rendition, even though it is quite rudimentary.  The next phase will be learning PHP, HTML, and CSS so that the magazine database can be accessed through a web browser. Another multi-year extravaganza?

This project is pretty much a learning experience effort. Much of the data can be readily found on "The Internet Speculative Fiction Database". I would not have been able put this project together without the assistance provide by the "Access2Base" website and the volunteers at the "Apache Open Office Forum". Thank-you.

The Main Form

"Show Author Stories" button pressed.

When the "Show Author Stories" button is pressed, all stories associated with that author are displayed, even if in magazines other than Analog.

"Find an Author" button pressed.

When the button "Find an Author" is pressed, you can initiate a search to display all stories associated with authors possessing the search string.

"Find a Story" button pressed.

When the button "Find a Story" is pressed, all stories containing the search string will be displayed.

"Add an Author" button pressed.

Occasionally, stories have more than one author. The form above allows traditional authors to be assigned to a story.

Science Fiction Magazine Database Update IV

Time for an update. Another progress report on developing an Open Source free database.  Click here to see earlier posts on this topic. Really not much has changed on the surface. You can't tell by the looks. I had a brief flurry of intense creativity following my upgrade to Ubuntu 12.04. Still haven't got used to the new Unity interface. Grumble.

I have now uploaded all the magazine data to MySQL, which is on the Ubuntu computer. MySQL constitutes the "Back-End", were the data now resides. MS Access is still being used, now as the "Front-End" (user interface). This allows me to continue to keep the magazine data up-to-date.

There were a few challenges in getting MS Access and MySQL to work together. Those issues have been apparently overcome. I have also developed an extremely primitive webpage. Eventually, this would allow users to access the data through their web browser thereby eliminating MS access as the user interface. Since I know very little concerning HTML and PHP, its going to be a while.

As I have expressed in the past, this is pretty much a personal learning exercise.  This data is already freely available on the Internet Speculative Fiction Database (ISFDB).  Makes my project somewhat redundant. The ISFDB is a great resource.  Cover art can also be viewed here.

Below is the introductory, screen which remains essentially unchanged from prior version.

Introductory Screen

Below is how a magazine would be displayed by issue.

Magazine Screen

Below is the primitive webpage. A lot of learning remains.

Primitive Webpage

Below is the author dump that you get when you click on the button "Connect to MySQL".  The good news is that my browser connects to the Ubuntu computer and can interact with it (also PHP and MYSQL)  to display the data below.

 

Sample Author Listin

Science Fiction Database Screen Shots

Screen shots of the Microsoft Access version of the database.  Though I am adding data, I do not expect to be enhancing the programming. Click on the images to see them better.

Please be sure to checkout the Internet Speculative Fiction Database (ISFDB).  Makes my project somewhat redundant. The ISFDB is a great resource.  Cover art can also be viewed here.

Main Screen

October Issue

Story Listing by an Author

UPDATE (3/18/2012) If you are a regular longtime reader of Analog or another SF magazine and wish to have a copy please email your address. I will have to (snail) mail the database on a CD, assuming that there aren't too many requests. The database is just over 82 Megabytes in size. You will need MS Access and you will need to tell me what version of MS Access you have. Familiarity with Access would also be recommended, since the user interface is sparse with minimal user protection for making dumb mistakes.  If you find errors and missing data - I would be interested in fixing them. Please email me. If you send any corrections or additions, please provide a copy of the table of contents.

Analog Update III

I finished importing Dave Baranyi's MS Excel data into MS Access.  Over 15,600 stories were imported from Analog, Galaxy, Worlds of If, Fantastic, and the Magazine of Fantasy and Science Fiction.  This was quite a bit of data entry work on the part of Dave Baranyi.

Please be aware that the data was imported from MS Excel into MS Access through a "dumb" program.  There is little in the way of error detection and correction.  I have fixed a few (very few) glaring errors, but there are many more. Besides the errors, there is quite a bit of missing data.  Specifically, the covers images.  There are over 2,000 cover images that would have to be added. With over 15,600 stories, it's going to be excruciatingly difficult to weed out the "bad" data.

If you are a regular longtime reader of Analog or another SF magazine and wish to have a copy please email your address. I will have to (snail) mail the database on a CD, assuming that there aren't too many requests. The database is just over 82 Megabytes in size. You will need MS Access and you will need to tell me what version of MS Access you have. Familiarity with Access would also be recommended, since the user interface is sparse with minimal user protection for making dumb mistakes.  If you find errors and missing data - I would be interested in fixing them. Please email me. If you send any corrections or additions, please provide a copy of the table of contents.

THE FUTURE: In theory, I will be working on making this database available through "Open Office" (using BASE) and "MySQL", so I do not intend to make further MS Access program updates. Still need to fix errors and add missing data though! So send in any corrections or missing data with photocopies of the table of contents.

Progress on developing a LINUX version has been much much slower than anticipated.  At least having an MS Access version permits data maintenance and allows others to use the database in the interim.

A SIDE NOTE: The Internet Speculative Fiction Database has a more in-depth database of information.  To a degree what I am doing is duplicative. Hopefully there is sufficient differentiation to justify this project.  Any thoughts?

SAMPLE IMAGES:  Below is the image of the main screen. (Click on the image for a better view.)

Below is the image when you select a specific magazine, such as Analog.

Below are two additional images. Double clicking on an authors name will show all stories written by the author; no matter the magazine. In this case, the stories of Harry Turtledove for both Analog and the Magazine of Fantasy and Science Fiction.

You can also do an author or story search. In this case the string "Chandler" was used as an author search term. Again, it will display results for all magazines.

Analog Update II

I've taken a breather from trying to develop an Analog database in "Open Office", made a quick (well really not so quick) MS Access version of the database.  Prior posts on my slow progress here.  Still faced with a steep learning curve for Open Office's database "Base".  Haven't even gotten to MySQL or PHP yet.  The MS Access version has been updated through the March 2011 edition, except for the March 20011 cover art.

If you have MS Access, I can provide a copy of the database.  Leave a message on the Analog readers forum. You would need to specify what version of MS Access that you have. The data was imported from an Excel spreadsheet developed by Dave Baranyi through the use of a dumb program. That means no error detection or correction. Obviously there will be data correction issues.  If you find any errors let me know. (I tend to have a lot of typos.)

Additionally, there are over 900 issues of Analog.  Consequently a lot of data is "missing", such as cover art, editorials, and the alternate view simply because the data has not yet been entered.  Gets boring entering data.  There is also the issue of the availability of old issues to verify the data.  Overtime, this data can be slowly added to the database.

Below is the opening screen. To the right there are a set of buttons that allow you provide a variety of search and editing tasks. (Click on the images to see them better.)




















When you double click on an author, all stories written by that author Pop-Up.  In this case Richard Lovett.  The thirteenth name down.

You can also search for a story or an author based on a partial string. In the picture below, all stories that have the string "solar" in their title are displayed.

One thing to realize, is that much of the information is already available on the Internet, so pursuing a standalone database, as I am doing, is somewhat duplicative. Below is an image from the Internet Speculative Fiction Database.

If someone has the ability to provide a central download site that would be beneficial.  Then everyone would have access to the most recent version. In theory, I won't be pursing upgrading the database in MS Access except for adding new data. Learning Base has been frustratingly slow. Also I have not even gotten to MySQL yet.  At least the MS Access version will serve as a temporary solution.  In the meantime I hope that you will be able to enjoy and utilize the MS Access version.

Analog Update

I have been able to link the cover art to the magazine issue.  This is the spreadsheet that Dave Baranyi developed and which I have imported into BASE, which is part of "Open Office". Please be aware that this project is still very incomplete.  At the rate I am going it will be quite a while before I am finished.


You may also be interested in my post here, which gets into one of those little itty bitty technical irritations. Combo Box - Display Multiple Columns

Analog Science Fiction And Fact Database

Analog Science Fiction and Fact, at one time had a database, but in the course of events it dissolved into the void. Fortunately, Dave Baranyi has been keeping up the index on an Excel Spreadsheet. This is seventy (70) + years of data!

For several years, as an occasional project, I have been working on my own personal database of Science Fiction Stories. In the hopes of getting the Analog stories back on-line, sometime in the future, I have developed an Access database based on Dave's spreadsheet. This database is a prototype, there is still a lot of work to do, mainly getting the database moved over to MySQL (which I do not yet know) and learning PHP. So it will be a long time before this really becomes usable, in terms of the internet.

Below is the opening screen. This is not meant to be "pretty", since this is a prototype. The Analog cover to the left is meant to show that the cover art can be added when this gets closer to being completed. Some of the boxes that are currently visible will disappear in the final version (whenever it gets done) since they are only needed temporarily for programming purposes.

If any Analog subscribers would like a copy of the database leave me a message on the readers forum. (Click on the images to see them better.)

When you click on an authors name, all stories by that author in Analog are displayed.

Below is a screen image, from my existing book database, that cycles the cover art with the magazine issue.

Hopefully, the current Analog project can be expanded to include all magazines, especially those out of print. Sample below from my existing book database.

Science Fiction Book Database

I developed a database to track my books, so that I would not end-up buying duplicate books. Eventually, I expanded it to list all books/stories/magazines that an author appeared in my database. Currently the database has just over 600 entries in it.

This is one of those perpetual projects that you dabble in every once in a while. Given the emergence of the Internet and reference sites such as Wikipedia , The Internet Speculative Fiction Database, and SCIFIPEDIA there is nothing magical about the database other than as a continued Access learning experience. As time permits, I will continue to dabble with it.

Below is the opening screen. (Click on the image, you will get a better quality image.)
Below is the screen shot for all the stories appearing in "Space Opera".

Below is a screen shot of the June 1953 edition of Astounding.

Here is the screen shot of the "secret ingredient", a display of all stories/books that the author appears in. Actually just the stories entered into the database since not all stories have been entered. Here you can see that Bertram Chandler appeared in November 1978 issue of "Amazing" and the August 1975 issue of "Galaxy". This is a feature that does not seem to exist in other databases, which I find quite surprising. I also have found this to be frustrating in terms of being able to locate stories when visiting bookstores.

For the the technological groupies - below is the SQL code that makes the screen shot above possible to display all the books/stories/magazines that an author appears in. I hope that others can use this as a means of expanding the capabilities of any existing database. The lack of this capability is a major shortcoming. This is what computers and databases are supposed to do!!!
----------------------------------------------------------------------------------------
SELECT Null AS [STORY],[TITLE] AS [BOOK],[SFAUTHORID] AS [AUTHORID],[BOOKID], NULL as [TYPE1]
FROM QALPHA WHERE SFAUTHORID=FIELD34

UNION ALL SELECT [STORY],[TITLE] AS [BOOK],[SFAUTHORID] AS [AUTHORID],[BOOKID], NULL as [TYPE1]
FROM QCOMPILATION WHERE SFAUTHORID=FIELD34
ORDER BY [AUTHORID], [STORY],[BOOK]

UNION ALL SELECT [STORY],[TITLE] AS [BOOK],[STAUTHORID] AS [AUTHORID],[BOOKID], NULL as [TYPE1]
FROM QANTHOLOGY WHERE STAUTHORID=FIELD34
ORDER BY [AUTHORID], [STORY], [BOOK]

UNION ALL SELECT [MSTORY],[MAGAZINE]&": "&[MMONTH]&" " & [MYEAR] AS [BOOK],[MSAUTHORID] AS [AUTHORID],[ISSUEID], "M" as [TYPE1]
FROM QMAGSTORYLIST WHERE MSAUTHORID=FIELD34
ORDER BY [AUTHORID], [STORY], [BOOK];
------------------------------------------------------------------------------------------------
If anyone is interested in receiving a copy, you can let me know by sending your mailing address to ortaias@hotmail.com. The database runs under Microsoft Access 2000. It is also too big to fit on a CD so it has to be sent on a DVD. The database currently contains just over 600 books. Its free and if there are not too many requests (such as less than 10) I wouldn't mind footing the bill for sending it out in the US. Of course, I would encourage a postage paid return envelope that is padded and can hold a DVD.

Now for the disclaimer, it ain't perfect! If you don't know Access, you may have some problems. I've tried to make it idiot proof, but as one saying goes idiots are very ingenious.

As a closing note, the ability to integrate books and magazines in a manner that allows one to see all entries for an author would be a major accomplishment. It would be interesting to pursue such a project and to have it posted on the web.