:) 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 <[email protected]> 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:[email protected]] > Sent: Thursday, July 28, 2011 5:02 PM > To: Grobani, Daniel > Cc: [email protected] > 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 <[email protected]> > 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 [email protected] >> http://lists.katipo.co.nz/mailman/listinfo/koha >> > _______________________________________________ Koha mailing list http://koha-community.org [email protected] http://lists.katipo.co.nz/mailman/listinfo/koha
