Mini MySQL Lesson

by Nicole C. on Jun 21, 2011

I got a great ticket today and after spending about 20 minutes answering it I thought that it might be a good answer to share with you all! So a partner went to the Koha Wiki to get a report. She chose the weeding tool by Kathy Rippel. She copied it and pasted it in to her Koha installation and found that the report ran, but didn’t produce any results. The reason for this is that while the report on the wiki is syntactically correct, it includes some things that need to be changed based on values at your library.

So, let’s start with the report as it stands on the wiki:

SELECT CONCAT( '<a href=\"/cgi-bin/koha/cataloguing/additem.pl?biblionumber=', biblio.biblionumber,
'\">', items.barcode, '</a>' ) AS 'Barcode', items.itemcallnumber, biblio.title, biblio.copyrightdate AS
'Copyright', items.dateaccessioned AS
'Accessioned', items.itype, items.issues, items.renewals, (IFNULL(items.issues, 0)+IFNULL(items.renewals, 0)) AS
Total_Circ, items.datelastborrowed, items.itemlost, items.onloan, items.damaged, items.itemnotes
FROM items
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio ON
(biblioitems.biblionumber=biblio.biblionumber)
WHERE items.itype='ITYPE' AND items.holdingbranch='BRANCHCODE' AND (items.itemcallnumber LIKE '37%' OR
items.itemcallnumber LIKE '38%' OR items.itemcallnumber LIKE '39%')
ORDER BY items.itemcallnumber

There are a few things in that report that need changing. Kathy made them stand out for you by putting two of them in all caps. The first is this:

items.itype='ITYPE'

This report is looking for a specific item type and you need to replace the ITYPE in that snippet with an actual item type code from your system. The next is similar:

items.holdingbranch='BRANCHCODE'

This part is looking for a specific branch. Kathy is in a multi-branch system and as such needs to be able to limit the report to one branch at a time. If you’re in a single branch system you can remove that part from the query altogether, if not then you need to replace the BRANCHCODE part with one of your branch codes.

The final bit that needs changing is a bit longer:

(items.itemcallnumber LIKE '37%' OR items.itemcallnumber LIKE '38%' OR items.itemcallnumber LIKE '39%')

This is looking for call numbers that start with 37, 38 or 39. If you don’t use Dewey this won’t work in your library and if you do use Dewey you might want a different group of call numbers. So given all of that I have come up with an edited report using runtime parameters (a feature that has been in Koha for a while but went undocumented until recently).


SELECT CONCAT( '<a href=\"/cgi-bin/koha/cataloguing/additem.pl?biblionumber=', biblio.biblionumber,
'\">', items.barcode, '</a>' ) AS 'Barcode', items.itemcallnumber, biblio.title, biblio.copyrightdate AS
'Copyright', items.dateaccessioned AS
'Accessioned', items.itype, items.issues, items.renewals, (IFNULL(items.issues, 0)+IFNULL(items.renewals, 0)) AS Total_Circ, items.datelastborrowed, items.itemlost, items.onloan, items.damaged, items.itemnotes
FROM items
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio ON
(biblioitems.biblionumber=biblio.biblionumber)
WHERE items.itype= <<Item type code>> AND items.holdingbranch=<<Branch code>> AND items.itemcallnumber between <<Call number between>> and <<and>>
ORDER BY items.itemcallnumber

My report replaces all the bits that needed editing so that when you copy and paste this report in to your Koha system and run it, Koha will ask you three questions:

• What Item Type Code are you searching for?
• What Branch Code are you searching for?
• What call number range are you looking at?

Using this you can run this report in any Koha library regardless of the number of branches, item types or the classification scheme that’s used.