[Koha] Report Help - Extracting XML

[Nicole Engard]

:) No problem! I actually had to remove them to test on the command line anyway.

You are right! It does work :) thanks - will share on the wiki with a
dual credit :)

Nicole

On Thu, Jul 28, 2011 at 8:21 PM, Grobani, Daniel
<DGrobani at samuelmerritt.edu> wrote:
> 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
>>
>