[Koha] Report Help - Extracting XML

[Grobani, Daniel]

Hmm, it works fine for me. It looks like it might have choked on the first tab--try replacing all the tabs I introduced to spaces.

Thanks for your reminder about the <<>> and sorry I removed them. I remember reading about that now, but because that works differently in our, um, flavor, it didn't stick.

Daniel

-----Original Message-----
From: Nicole Engard [mailto:nengard at gmail.com] 
Sent: Thursday, July 28, 2011 5:02 PM
To: Grobani, Daniel
Cc: koha at lists.katipo.co.nz
Subject: Re: [Koha] Report Help - Extracting XML

Okay - this:


select count(statistics.datetime) as circs, CONCAT('<a
href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.title,'</a>')
AS Title, CONCAT('<a
href=\"http://catalog.mysiteca.gov/cgi-bin/koha/opac-detail.pl?biblionumber=',biblio.biblionumber,'\">http://catalog.mysiteca.gov/cgi-bin/koha/opac-detail.pl?biblionumber=',biblio.biblionumber,'</a>')
as URL, biblio.author, items.location,
extractValue(biblioitems.marcxml,"collection/record/datafield[@tag='520'][1]/subfield[@code='a']")
FROM            statistics
JOIN            items ON (items.itemnumber=statistics.itemnumber)
LEFT JOIN       biblio ON (biblio.biblionumber=items.biblionumber)
LEFT JOIN       biblioitems ON (biblioitems.biblioitemnumber =
items.biblioitemnumber)
WHERE           DATE(statistics.datetime) BETWEEN '2011-01-01' AND '2011-07-01'
                       AND statistics.itemnumber IS NOT NULL
                       AND location = 'Shelving Location Code'
GROUP BY        biblio.biblionumber
ORDER BY        circs DESC
LIMIT           10

Gets this:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '       statistics JOIN            items ON (items.itemnumber=statistic' at line 4


Also, a tip - those <<>> sections I had in there are runtime parameters for Koha (a neat trick that is documented on the wiki and in the manual - in case you removed them cause you didn't know what they were).


On Thu, Jul 28, 2011 at 7:41 PM, Daniel Grobani <dgrobani at samuelmerritt.edu> wrote:
> Try this (I also did some light cleanup):
>
> SELECT          COUNT(statistics.datetime) AS circs,
>                CONCAT(' \"/cgi- ',biblio.title,' ') AS Title,
>                CONCAT(' \"http://mycatalogv/cgi- 
> http://catalog.losgatosca.gov/cgi-bin/koha/opac-detail.pl?biblionumber=',biblio.biblionumber,'
> ') AS URL,
>                biblio.author,
>                items.location,
>
> extractValue(biblioitems.marcxml,"collection/record/datafield[@tag='52
> 0'][1]/subfield[@code='a']")
> FROM            statistics
> JOIN            items ON (items.itemnumber=statistics.itemnumber)
> LEFT JOIN       biblio ON (biblio.biblionumber=items.biblionumber)
> LEFT JOIN       biblioitems ON (biblioitems.biblioitemnumber =
> items.biblioitemnumber)
> WHERE           DATE(statistics.datetime) BETWEEN 'yyyy-mm-dd' AND 'yyyy-mm-dd'
>                        AND statistics.itemnumber IS NOT NULL
>                        AND location = 'Shelving Location Code'
> GROUP BY        biblio.biblionumber
> ORDER BY        circs DESC
> LIMIT           10
>
>
> -----
> Cheers,
> Daniel Grobani
> Library Technology Specialist
> John A. Graziano Memorial Library
> Samuel Merritt University
>
> --
> View this message in context: 
> http://koha.1045719.n5.nabble.com/Report-Help-Extracting-XML-tp4640604
> p4644634.html Sent from the Koha - Discuss mailing list archive at 
> Nabble.com.
> _______________________________________________
> Koha mailing list  http://koha-community.org Koha at lists.katipo.co.nz 
> http://lists.katipo.co.nz/mailman/listinfo/koha
>