4 # A script to fetch the ratings of a given title, using the isbn number
5 # Initially just books, but ill expand to handle dvd's and cd's as well
7 # uses a new table, ratings, pipe the ratings.sql script into mysql to create the table.
15 my $dbh = C4::Context->dbh();
18 "SELECT isbn,biblioitemnumber,biblionumber FROM biblioitems where isbn is NOT NULL and isbn <> ''
20 my $sth = $dbh->prepare($query);
22 while ( my $data = $sth->fetchrow_hashref() ) {
23 $data->{'isbn'} =~ s/\-//g;
24 $data->{'isbn'} =~ s/ +//g;
27 # isbn must appear without spaces or -
28 my $url = "http://www.amazon.com/exec/obidos/search-handle-url/index%3Dbooks%26field-isbn%3D";
29 $url .= $data->{'isbn'};
30 my $ua = LWP::UserAgent->new;
31 my $content = $ua->get($url)->content;
37 if ( $content =~ /alt="(.*?) out of 5 stars"/ ) {
43 # first check we dont already have a rating, if so, and its different update it
44 # otherwise insert a new rating
45 my $query2 = "SELECT * FROM ratings WHERE biblioitemnumber=?";
46 my $sth2 = $dbh->prepare($query2);
47 $sth2->execute( $data->{'biblioitemnumber'} );
48 if ( my $ratings = $sth2->fetchrow_hashref() ) {
49 if ( $rating ne $ratings->{'rating'} ) {
51 "UPDATE ratings SET rating=? WHERE biblioitemnumber=?";
52 my $sth3 = $dbh->prepare($query3);
53 $sth3->execute( $rating, $data->{'biblioitemnumber'} );
59 "INSERT INTO ratings (rating,biblioitemnumber,biblionumber) VALUES (?,?,?)";
60 my $sth3 = $dbh->prepare($query3);
62 $rating, $data->{'biblioitemnumber'},
63 $data->{'biblionumber'}