ACPL’s “New and Upcoming” OPAC List

upcoming-titles-screenshot1

The Athens County Public Libraries’ OPAC has a display of selections from our “New and Upcoming” book list. Although it is displayed within a Koha page, the content is brought in from an outside Koha. These are the pieces that put it all together:

  • A list of new titles, maintained in a separate MySQL database via its own web interface
  • A php script to pull out and display a random selection of titles from the list
  • Markup for an iframe added to Koha’s opacmainuserblock system preference

Set up the Database

ACPL originally built the infrastructure for maintaining book lists as part of the library’s public web site. The intention was to enable librarians to build any kind of book list. There are two tables, bookshelf and bookshelfitems:

bookshelfitems
Field Type Null Key Default Extra
shelfitemid int(11) PRI NULL auto_increment
shelfid int(11) YES NULL
adddate date YES NULL
title varchar(255) YES NULL
authorfirst varchar(80) YES NULL
authorlast varchar(80) YES
biblionumber int(11) 0
isbn varchar(20)
sortorder tinyint(4) YES NULL

The bookshelfitems table contains the individual items that appear on a list. The sortorder field is not required. ACPL uses it to define a custom sort order for other book lists created for our site.

bookshelf
Field Type Null Key Default Extra
shelfid int(11) PRI NULL auto_increment
shelftitle varchar(255) YES NULL
description text YES NULL
bannerimage varchar(150) YES NULL
dateadded date YES NULL
displayfrom date YES NULL
displayto date 0000-00-00
category tinyint(4) YES NULL

The bookshelf table contains the names and details of each individual list. This is not strictly necessary if you’re only maintaining one list. The description, bannerimage, category and date-related columns are not required.

In the case of the new book list, a list with shelftitle “New and Upcoming” was created. Then individual items were added to bookshelfitems which specified the shelfid of the “New and Upcoming” list.

Retrieve the Data

The next step is to pull the relevant data from the list for display. Create a new PHP file which does a straightforward query of the database:


Of course your WHERE clause should specify the id of your new book shelf

<table><tr>

<td>
&lt;a target=&quot;_parent&quot;
href=&quot;http://path/to/koha/opac-detail.pl?biblionumber=

"&gt;&lt;img src=&quot;http://images.amazon.com/images/P/.01._TZZZZZZZ_PU_PU-5_.jpg" alt="" /&gt;</a>
&lt;a target=&quot;_parent&quot;
href=&quot;http://path/to/koha/opac-detail.pl?biblionumber=
"&gt;
</a>


By &lt;a target=&quot;_parent&quot; href=&quot;http://path/to/koha/opac-search.pl?q=au:
,%20"&gt;</a>.

</td>

</tr></table>

And of course you should change the URLs to point to your OPAC. As you can see ACPL pulls book cover images from Amazon. That could be modified to point to a different source if your library chooses. When you access your new PHP file directly you should see your titles:

upcoming-titles-screenshot21

Add to your OPAC

Log in to the Administrative interface of Koha, go to System Preferences, and edit the OpacMainUserBlock preference. This is the essence of the markup that ACPL uses:

<h4>Selections From the New and Upcoming Titles List:</h4>

Possible Improvements

  • iframe height is fixed. The 21 em height of the iframe is a compromise, attempting to set a height that will fit all but the longest titles without creating too much white space. This doesn’t always work.
  • Dynamic awareness of overflow in smaller displays. If your browser window is 800 pixels wide or smaller, the third title will be cut off by the overflow:hidden CSS property. A great improvement would be to add Javascript that would check the width of the iframe on page load and remove the last cover completely so that no partial covers were displayed.
  • Gracefully handle missing covers. Not every title we list has a corresponding cover image on Amazon.com. There are many techniques available for handling this. The same javascript method Koha uses (adapted from the link article) could probably be applied here.

16 thoughts on “ACPL’s “New and Upcoming” OPAC List

  1. nicomo

    Hi Owen,
    How do you pull the records from Koha itself? Or are they manually entered using this tool’s UI and copying and pasting the relevant information from Koha?
    Couldn’t there be a way to patch Koha to do this? Something like : go to an item, flag it as “new and upcoming”, etc… Maybe from the “lists” functionnality in the staff interface?
    Just a few random sunday-morning thoughts…

  2. oleonard Post author

    The staff member has to look up the item and copy the biblionumber. When they paste the biblionumber into the interface for creating a new list item it pulls the title, author, and isbn data from Koha. So there is duplication of data going on between Koha and the separate database containing the new title list.

    I’d love to be able to be able to do it all from within Koha, but can’t write it or sponsor it myself. As far as I know there’s not any kind of title-level authorized value that could be used to mark something with a flag as you describe.

  3. Erik Lewis

    Hi Owen,
    Followed your directions on this but I seem to be missing something. This is the error I’m getting.

    Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /usr/share/koha/opac/htdocs/newbook.php on line 17

    I need a clue, before I spend the rest of the afternoon on it.

    This is the code of my newbook.php

    <a target="_parent"
    href="http://path/to/koha/opac-detail.pl?biblionumber=

    “><img src="http://images.amazon.com/images/P/.01._TZZZZZZZ_PU_PU-5_.jpg” alt=”” />
    <a target="_parent"
    href="http://path/to/koha/opac-detail.pl?biblionumber=
    “>

    By <a target="_parent" href="http://path/to/koha/opac-search.pl?q=au:
    ,%20″>.

  4. oleonard Post author

    Erik, the error you quote is a generic PHP error which occurs when your query fails. It could be any number of things. Have you created the required MySQL tables for storing your book data? Have you populated the tables with book data based on your Koha collection? Have you successfully connected to your MySQL database via PHP?

  5. Erik Lewis

    Owen,
    Thanks for the quick response.

    I have created the tables.
    I have populated the tables.
    I have successfully connected to the MySQL database with PHP.

    I’m out of my own ideas right now.

  6. oleonard Post author

    Looks like it might be a typo in my code? What if you change this line:

    php while($row = mysql_fetch_array($alllidsresult)){

    to this:

    php while($row = mysql_fetch_array($newtitles_result )){

    ?

  7. Erik Lewis

    Thanks again for your assistance. Got a quick question for you. I used your java script to move the cover images to the left side in the search results. Now I’ve got a column of blank space on the right side. It was probably always there, but I’m wondering if there was away to shift everything to the right so I could have that column on the left hand side for a navigation menu. Probably a disjointed statement while the coffee is taking effect.

  8. Mohibuzzaman Zico

    What does line number 09 means? I mean, if i enter the jpg of a given book image, how will my system find the image of other books? As i can see, you put 01._TZZZZZZZ_PU_PU-5_.jpg there in your code. I need help.

  9. tance

    how to popullate the database?
    where to put this code in upcoming.php?

    im beginner in koha and programing

    1. oleonard Post author

      I don’t recommend trying to implement this if you’re a beginner. The purpose of the article is to give the reader a general understanding of the method we used and to give ideas to those wanting to implement a similar system for themselves.

  10. ahmad

    can any body explain with picture or print screen how can do New and Upcoming on opac i try to do it but it not coming with me.

    with my regards

    ahmad nasser
    Egypt – Cairo
    futureuniversity in Egypt

  11. oleonard Post author

    ahmad: This feature depends on a fair amount of programming outside of Koha to support it. This post was intended to give a sense of what was possible. Unfortunately it can’t act as a start-to-finish tutorial.

  12. Arslan Farooq

    Owen be nice, and write a step by step tutorial for this.

    Current article is like telling us there is this great dish you cooked, and you showed us its pictures how it looks like. And then you tell us that although this is great, but the rest of us can’t cook it if we are not an expert in MySQL and PHP 🙂

  13. Arslan Farooq

    … and in “Set up the Database”, you say “There are two tables, bookshelf and bookshelfitems:”.

    Do we create these tables and the database? Can’t we use the Koha database and query it for required information?

  14. kate

    Where do we store this sql part?..Could you specify the path or something?
    I am a beginner to this…
    if there is any other way (for beginners) to do this, could you specify the steps?

Comments are closed.