[Koha] Coverflow installation problems

[Alvaro Cornejo]

Hi Quỳnh,

There were a couple of errors in your query.

Try this:

---------
SELECT b.biblionumber, SUBSTRING_INDEX(m.isbn, ' ', 1) AS isbn, b.title

FROM items i

LEFT JOIN biblioitems m USING (biblioitemnumber)

LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)

WHERE DATE_SUB(CURDATE(),INTERVAL 180 DAY) <= i.dateaccessioned AND m.isbn
IS NOT NULL AND m.isbn != ""

ORDER BY rand()

LIMIT 7
----------

I´ve tried in my system (19.11) system and it works now.

Regards

Alvaro


|----------------------------------------------------------------------------------------|
 Stay safe / Cuídate/  Reste sécurisé
*7* Switch off as you go / Apaga lo que no usas /  Débranchez au fur et à
mesure.
 *q *Recycle always / Recicla siempre / Recyclez toujours
 P Print only if absolutely necessary / Imprime solo si es necesario /
Imprimez seulement si nécessaire





Le sam. 6 juin 2020 à 20:51, Quỳnh Vũ Đỗ <quynhvd at thanglong.edu.vn> a
écrit :

> Hello everyone,
> I am using a test database of our Library on Koha 19.11.06 (just
> auto-upgraded from 19.11.05) on Ubuntu 16.04.6 LTS.
> In the process of customizing the OPAC and installing the COverflow plugin
> as described at:
> https://bywatersolutions.com/education/coverflow-blog-post-update
> I have created the sql report by copying and paste the described content on
> that page:
>
> Report SQL:
>
> SELECT b.biblionumber, SUBSTRING_INDEX(m.isbn, ‘ ‘, 1) AS isbn, b.title
>
> FROM items i
>
> LEFT JOIN biblioitems m USING (biblioitemnumber)
>
> LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
>
> WHERE DATE_SUB(CURDATE(),INTERVAL 180 DAY) <= i.dateaccessioned AND
> m.isbn IS NOT NULL AND m.isbn != ”
>
> ORDER BY rand()
>
> LIMIT 7
>
>
> The only modification I have brought to the original code was to change the
> INTERVAL from 30 DAY to 180 DAY as the last operation made on that database
> was made on 25th February 2020.
> On running the report I got the following error message:
> "The database returned the following error:
> Incorrect parameters in the call to native function 'substring_index'
> Please check the log for further details"
>
> So I checked the log at /var/log/koha/library/intranet-error.log which
> indicated:
>
> [Sun Jun 07 08:21:27.385286 2020] [cgi:error] [pid 30474] [client
> 127.0.0.1:39572] AH01215: [Sun Jun  7 08:21:27 2020] guided_reports.pl:
> \r:
> /usr/share/koha/intranet/cgi-bin/reports/guided_reports.pl, referer:
> http://localhost:8080/cgi-bin/koha/reports/guided_reports.pl
> [Sun Jun 07 08:21:27.385426 2020] [cgi:error] [pid 30474] [client
> 127.0.0.1:39572] AH01215: [Sun Jun  7 08:21:27 2020] guided_reports.pl:
> LIMIT ?, ?" with ParamValues: 0=0, 1=7] at
> /usr/share/koha/intranet/cgi-bin/reports/guided_reports.pl line 1001.:
> /usr/share/koha/intranet/cgi-bin/reports/guided_reports.pl, referer:
> http://localhost:8080/cgi-bin/koha/reports/guided_reports.pl
> [Sun Jun 07 08:21:27.385605 2020] [cgi:error] [pid 30474] [client
> 127.0.0.1:39572] AH01215: [Sun Jun  7 08:21:27 2020] guided_reports.pl:
> DBD::mysql::st fetchrow_arrayref failed: fetch() without execute() [for
> Statement "SELECT b.biblionumber, SUBSTRING_INDEX(m.isbn, \xe2\x80\x98
> \xe2\x80\x98, 1) AS isbn, b.title\r:
> /usr/share/koha/intranet/cgi-bin/reports/guided_reports.pl, referer:
> http://localhost:8080/cgi-bin/koha/reports/guided_reports.pl
> [Sun Jun 07 08:21:27.385660 2020] [cgi:error] [pid 30474] [client
> 127.0.0.1:39572] AH01215: [Sun Jun  7 08:21:27 2020] guided_reports.pl:
> FROM items i\r: /usr/share/koha/intranet/cgi-bin/reports/guided_reports.pl
> ,
> referer: http://localhost:8080/cgi-bin/koha/reports/guided_reports.pl
> [Sun Jun 07 08:21:27.385745 2020] [cgi:error] [pid 30474] [client
> 127.0.0.1:39572] AH01215: [Sun Jun  7 08:21:27 2020] guided_reports.pl:
> LEFT JOIN biblioitems m USING (biblioitemnumber)\r:
> /usr/share/koha/intranet/cgi-bin/reports/guided_reports.pl, referer:
> http://localhost:8080/cgi-bin/koha/reports/guided_reports.pl
> [Sun Jun 07 08:21:27.385834 2020] [cgi:error] [pid 30474] [client
> 127.0.0.1:39572] AH01215: [Sun Jun  7 08:21:27 2020] guided_reports.pl:
> LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)\r:
> /usr/share/koha/intranet/cgi-bin/reports/guided_reports.pl, referer:
> http://localhost:8080/cgi-bin/koha/reports/guided_reports.pl
> [Sun Jun 07 08:21:27.385966 2020] [cgi:error] [pid 30474] [client
> 127.0.0.1:39572] AH01215: [Sun Jun  7 08:21:27 2020] guided_reports.pl:
> WHERE DATE_SUB(CURDATE(),INTERVAL 180 DAY) <= i.dateaccessioned AND m.isbn
> IS NOT NULL AND m.isbn != \xe2\x80\x9d\r:
> /usr/share/koha/intranet/cgi-bin/reports/guided_reports.pl, referer:
> http://localhost:8080/cgi-bin/koha/reports/guided_reports.pl
> [Sun Jun 07 08:21:27.386024 2020] [cgi:error] [pid 30474] [client
> 127.0.0.1:39572] AH01215: [Sun Jun  7 08:21:27 2020] guided_reports.pl:
> ORDER BY rand()\r: /usr/share/koha/intranet/cgi-bin/reports/
> guided_reports.pl, referer:
> http://localhost:8080/cgi-bin/koha/reports/guided_reports.pl
> [Sun Jun 07 08:21:27.386072 2020] [cgi:error] [pid 30474] [client
> 127.0.0.1:39572] AH01215: [Sun Jun  7 08:21:27 2020] guided_reports.pl:
> \r:
> /usr/share/koha/intranet/cgi-bin/reports/guided_reports.pl, referer:
> http://localhost:8080/cgi-bin/koha/reports/guided_reports.pl
> [Sun Jun 07 08:21:27.386321 2020] [cgi:error] [pid 30474] [client
> 127.0.0.1:39572] AH01215: [Sun Jun  7 08:21:27 2020] guided_reports.pl:
> LIMIT ?, ?" with ParamValues: 0=0, 1=7] at
> /usr/share/koha/intranet/cgi-bin/reports/guided_reports.pl line 839.:
> /usr/share/koha/intranet/cgi-bin/reports/guided_reports.pl, referer:
> http://localhost:8080/cgi-bin/koha/reports/guided_reports.pl
>
> As I have limited knowledge in SQL coding, I am unsure of what to do (I
> remember to have tested that SQL code with Koha 16.05 and it worked then).
>
> Does anyone have met that problem and possibly have a hint for resolution?
> Thanks
> =================
> M. Vũ Đỗ Quỳnh (Ph.D)
> Trường Đại học Thăng Long - Université de Thang Long - Thang Long
> University (Hanoi, Vietnam)
> Phó Hiệu Trưởng, phụ trách HTQT - Vice-recteur chargé de la coopération
> internationale - Vice-Rector in charge of International Cooperation
> Web: http://thanglong.edu.vn/
> _______________________________________________
>
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>