From 04a108482dafd286a1c6bd8c4becc8cf56e1a46a Mon Sep 17 00:00:00 2001 From: Henri-Damien LAURENT Date: Wed, 3 Oct 2007 19:01:01 +0200 Subject: [PATCH] BUG FIXING : Using LEFT JOIN in replacement of implicit inner joins. WARN NOT FULLY TESTED Signed-off-by: Chris Cormack --- C4/Acquisition.pm | 5 +++-- C4/Biblio.pm | 25 +++++++++++---------- C4/Bookfund.pm | 13 +++++------ C4/Circulation.pm | 2 +- C4/Log.pm | 30 +++++++++++-------------- C4/Members.pm | 20 +++++++++-------- C4/Overdues.pm | 57 ++++++++++++++++++++++------------------------- C4/Reserves.pm | 21 ++++++++--------- C4/Serials.pm | 48 ++++++++++++++++++++------------------- C4/Stats.pm | 6 ++--- C4/Suggestions.pm | 3 +-- 11 files changed, 114 insertions(+), 116 deletions(-) diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm index 39218f18e8..12861bc117 100644 --- a/C4/Acquisition.pm +++ b/C4/Acquisition.pm @@ -819,9 +819,10 @@ sub GetParcel { aqorders.listprice, aqorders.rrp, aqorders.ecost - FROM aqorders,aqbasket + FROM aqorders + LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber - WHERE aqbasket.basketno=aqorders.basketno + WHERE AND aqbasket.booksellerid=? AND aqorders.booksellerinvoicenumber LIKE \"$code\" AND aqorders.datereceived= \'$datereceived\'"; diff --git a/C4/Biblio.pm b/C4/Biblio.pm index 6f5e280da8..95d5a31ef6 100644 --- a/C4/Biblio.pm +++ b/C4/Biblio.pm @@ -784,11 +784,11 @@ sub GetItemsInfo { my ( $biblionumber, $type ) = @_; my $dbh = C4::Context->dbh; my $query = "SELECT *,items.notforloan as itemnotforloan - FROM items, biblio, biblioitems + FROM items + LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber + LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber LEFT JOIN itemtypes on biblioitems.itemtype = itemtypes.itemtype WHERE items.biblionumber = ? - AND biblioitems.biblioitemnumber = items.biblioitemnumber - AND biblio.biblionumber = items.biblionumber ORDER BY items.dateaccessioned desc "; my $sth = $dbh->prepare($query); @@ -801,10 +801,9 @@ sub GetItemsInfo { my $datedue = ''; my $isth = $dbh->prepare( "SELECT issues.*,borrowers.cardnumber,borrowers.surname,borrowers.firstname - FROM issues, borrowers + FROM issues LEFT JOIN borrowers ON issues.borrowernumber=borrowers.borrowernumber WHERE itemnumber = ? - AND returndate IS NULL - AND issues.borrowernumber=borrowers.borrowernumber" + AND returndate IS NULL" ); $isth->execute( $data->{'itemnumber'} ); if ( my $idata = $isth->fetchrow_hashref ) { @@ -1263,10 +1262,10 @@ sub GetBiblioFromItemNumber { my ( $itemnumber ) = @_; my $dbh = C4::Context->dbh; my $sth = $dbh->prepare( - "SELECT * FROM biblio,items,biblioitems - WHERE items.itemnumber = ? - AND biblio.biblionumber = items.biblionumber - AND biblioitems.biblioitemnumber = items.biblioitemnumber" + "SELECT * FROM items + LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber + LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber + WHERE items.itemnumber = ?" ); $sth->execute($itemnumber); @@ -1652,10 +1651,12 @@ sub GetMarcBiblio { $marcxml =~ s/\x1f//g; $marcxml =~ s/\x1d//g; $marcxml =~ s/\x0f//g; - $marcxml =~ s/\x0c//g; + $marcxml =~ s/\x0c//g; # warn $marcxml; my $record = MARC::Record->new(); - $record = MARC::Record::new_from_xml( $marcxml, "utf8",C4::Context->preference('marcflavour')) if $marcxml; + + $record = eval {MARC::Record::new_from_xml( $marcxml, "utf8",C4::Context->preference('marcflavour'))} if ($marcxml); + if ($@) {warn $@;} # $record = MARC::Record::new_from_usmarc( $marc) if $marc; return $record; } diff --git a/C4/Bookfund.pm b/C4/Bookfund.pm index a86841b01e..4368ef5cf7 100644 --- a/C4/Bookfund.pm +++ b/C4/Bookfund.pm @@ -139,9 +139,9 @@ sub GetBookFunds { if ( $branch ne '' ) { $strsth = " SELECT * - FROM aqbookfund,aqbudget - WHERE aqbookfund.bookfundid=aqbudget.bookfundid - AND startdatenow() AND (aqbookfund.branchcode='' OR aqbookfund.branchcode= ? ) GROUP BY aqbookfund.bookfundid ORDER BY bookfundname"; @@ -149,10 +149,9 @@ sub GetBookFunds { else { $strsth = " SELECT * - FROM aqbookfund, - aqbudget - WHERE aqbookfund.bookfundid=aqbudget.bookfundid - AND startdatenow() GROUP BY aqbookfund.bookfundid ORDER BY bookfundname "; diff --git a/C4/Circulation.pm b/C4/Circulation.pm index 0adedac1ed..71a9dc12f5 100644 --- a/C4/Circulation.pm +++ b/C4/Circulation.pm @@ -611,7 +611,7 @@ sub itemissues { # Find the last 3 people who borrowed this item. $sth2 = $dbh->prepare( - "SELECT * FROM issues, borrowers + "SELECT * FROM issues LEFT JOIN borrowers ON issues.borrowernumber = borrowers.borrowernumber WHERE itemnumber = ? AND returndate IS NOT NULL diff --git a/C4/Log.pm b/C4/Log.pm index 098149be31..a308d225ea 100644 --- a/C4/Log.pm +++ b/C4/Log.pm @@ -118,11 +118,10 @@ sub displaylog { if ($modulename eq "catalogue"){ $strsth="select action_logs.timestamp, action_logs.action, action_logs.info, borrowers.cardnumber, borrowers.surname, borrowers.firstname, borrowers.userid,"; $strsth .= "biblio.biblionumber, biblio.title, biblio.author" ;#if ($modulename eq "acqui.simple"); - $strsth .= " FROM borrowers,action_logs "; - $strsth .= ",biblio " ;#if ($modulename eq "acqui.simple"); + $strsth .= " FROM action_logs LEFT JOIN borrowers ON borrowers.borrowernumber=action_logs.user"; + $strsth .= " LEFT JOIN biblio ON action_logs.object=biblio.biblionumber " ;#if ($modulename eq "acqui.simple"); - $strsth .=" WHERE borrowers.borrowernumber=action_logs.user"; - $strsth .=" AND action_logs.module = 'cataloguing' AND action_logs.object=biblio.biblionumber ";# if ($modulename eq "acqui.simple"); + $strsth .=" WHERE action_logs.module = 'cataloguing' ";# if ($modulename eq "acqui.simple"); if (@filters) { foreach my $filter (@filters) { if ($filter->{name} =~ /user/) { @@ -138,13 +137,11 @@ sub displaylog { } } } elsif ($modulename eq "acqui") { - $strsth="select action_logs.timestamp, action_logs.action, action_logs.info, borrowers.cardnumber, borrowers.surname, borrowers.firstname, borrowers.userid,"; - $strsth .= "biblio.biblionumber, biblio.title, biblio.author" ;#if ($modulename eq "acqui.simple"); - $strsth .= "FROM borrowers,action_logs "; - $strsth .= ",biblio " ;#if ($modulename eq "acqui.simple"); - - $strsth .=" WHERE borrowers.borrowernumber=action_logs.user"; - $strsth .= "AND action_logs.module = 'cataloguing' AND action_logs.object=biblio.biblionumber ";# if ($modulename eq "acqui.simple"); + $strsth=qq|select action_logs.timestamp, action_logs.action, action_logs.info, borrowers.cardnumber, borrowers.surname, borrowers.firstname, borrowers.userid, + biblio.biblionumber, biblio.title, biblio.author + FROM action_logs LEFT JOIN borrowers ON borrowers.borrowernumber=action_logs.user + LEFT JOIN biblio ON action_logs.object=biblio.biblionumber + WHERE action_logs.module = 'cataloguing' |;# if ($modulename eq "acqui.simple"); if (@filters){ foreach my $filter (@filters){ if ($filter->{name} =~ /user/){ @@ -160,12 +157,11 @@ sub displaylog { } } } elsif ($modulename eq "members"){ - $strsth="select action_logs.timestamp, action_logs.action, action_logs.info, borrowers.cardnumber, borrowers.surname, borrowers.firstname, borrowers.userid,"; - $strsth .= "bor2.cardnumber, bor2.surname, bor2.firstname, bor2.userid,"; - $strsth .= "FROM borrowers,action_logs,borrowers as bor2 "; - - $strsth .=" WHERE borrowers.borrowernumber=action_logs.user"; - $strsth .= "AND action_logs.module = 'members' AND action_logs.object=bor2.borrowernumber ";# if ($modulename eq "acqui.simple"); + $strsth=qq|SELECT action_logs.timestamp, action_logs.action, action_logs.info, + borrowers.cardnumber, borrowers.surname, borrowers.firstname, borrowers.userid, + bor2.cardnumber, bor2.surname, bor2.firstname, bor2.userid + FROM action_logs LEFT JOIN borrowers ON borrowers.borrowernumber=action_logs.user LEFT JOIN borrowers as bor2 ON action_logs.object=bor2.borrowernumber + WHERE action_logs.module = 'members' |;# if ($modulename eq "acqui.simple"); if (@filters){ foreach my $filter (@filters){ if ($filter->{name} =~ /user/){ diff --git a/C4/Members.pm b/C4/Members.pm index bb8223955d..8ba07a4ed9 100644 --- a/C4/Members.pm +++ b/C4/Members.pm @@ -1016,11 +1016,13 @@ sub GetAllIssues { my $dbh = C4::Context->dbh; my $count = 0; my $query = -"Select *,items.timestamp AS itemstimestamp from issues,biblio,items,biblioitems - where borrowernumber=? and - items.biblioitemnumber=biblioitems.biblioitemnumber and - items.itemnumber=issues.itemnumber and - items.biblionumber=biblio.biblionumber order by $order"; + "Select *,items.timestamp AS itemstimestamp from + issues + LEFT JOIN items on items.itemnumber=issues.itemnumber + LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber + LEFT JOIN biblioitems ON items.biblioitemnumber=biblioitems.biblioitemnumber + where borrowernumber=? + order by $order"; if ( $limit != 0 ) { $query .= " limit $limit"; } @@ -1040,11 +1042,11 @@ sub GetAllIssues { # large chunk of older issues data put into table oldissues # to speed up db calls for issuing items if ( C4::Context->preference("ReadingHistory") ) { - my $query2 = "SELECT * FROM oldissues,biblio,items,biblioitems + my $query2 = "SELECT * FROM oldissues + LEFT JOIN items ON items.itemnumber=oldissues.itemnumber + LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber + LEFT JOIN biblioitems ON items.biblioitemnumber=biblioitems.biblioitemnumber WHERE borrowernumber=? - AND items.biblioitemnumber=biblioitems.biblioitemnumber - AND items.itemnumber=oldissues.itemnumber - AND items.biblionumber=biblio.biblionumber ORDER BY $order"; if ( $limit != 0 ) { $limit = $limit - $count; diff --git a/C4/Overdues.pm b/C4/Overdues.pm index 9306ed005c..a22483aaa4 100644 --- a/C4/Overdues.pm +++ b/C4/Overdues.pm @@ -153,11 +153,11 @@ sub checkoverdues { my @overdueitems; my $count = 0; my $sth = $dbh->prepare( - "SELECT * FROM issues,biblio,biblioitems,items - WHERE items.biblioitemnumber = biblioitems.biblioitemnumber - AND items.biblionumber = biblio.biblionumber - AND issues.itemnumber = items.itemnumber - AND issues.borrowernumber = ? + "SELECT * FROM issues + LEFT JOIN items ON issues.itemnumber = items.itemnumber + LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber + LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber + WHERE issues.borrowernumber = ? AND issues.returndate is NULL AND issues.date_due < ?" ); @@ -460,8 +460,7 @@ sub UpdateFine { # I think this else-clause deals with the case where we're adding # a new fine. my $sth4 = $dbh->prepare( - "select title from biblio,items where items.itemnumber=? - and biblio.biblionumber=items.biblionumber" + "select title from biblio LEFT JOIN items ON biblio.biblionumber=items.biblionumber where items.itemnumber=?" ); $sth4->execute($itemnum); my $title = $sth4->fetchrow_hashref; @@ -518,9 +517,9 @@ sub BorType { my ($borrowernumber) = @_; my $dbh = C4::Context->dbh; my $sth = $dbh->prepare( - "Select * from borrowers,categories where - borrowernumber=? and -borrowers.categorycode=categories.categorycode" + "SELECT * from borrowers + LEFT JOIN categories ON borrowers.categorycode=categories.categorycode + WHERE borrowernumber=?" ); $sth->execute($borrowernumber); my $data = $sth->fetchrow_hashref; @@ -1050,9 +1049,9 @@ C<$date_due> contains the date of item return sub CheckExistantNotifyid { my($borrowernumber,$date_due) = @_; my $dbh = C4::Context->dbh; - my $query = qq|SELECT notify_id FROM issues,accountlines + my $query = qq|SELECT notify_id FROM accountlines + LEFT JOIN issues ON issues.itemnumber= accountlines.itemnumber WHERE accountlines.borrowernumber =? - AND issues.itemnumber= accountlines.itemnumber AND date_due = ?|; my $sth=$dbh->prepare($query); $sth->execute($borrowernumber,$date_due); @@ -1155,18 +1154,17 @@ sub GetOverduesForBranch { accountlines.notify_level, items.location, accountlines.amountoutstanding - FROM issues,borrowers,biblio,biblioitems,itemtypes,items,branches,accountlines + FROM accountlines + LEFT JOIN issues ON issues.itemnumber = accountlines.itemnumber AND issues.borrowernumber = accountlines.borrowernumber + LEFT JOIN borrowers ON borrowers.borrowernumber = accountlines.borrowernumber + LEFT JOIN items ON items.itemnumber = issues.itemnumber + LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber + LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber + LEFT JOIN itemtypes ON itemtypes.itemtype = biblioitems.itemtype + LEFT JOIN branches ON branches.branchcode = issues.branchcode WHERE ( issues.returndate is null) AND ( accountlines.amountoutstanding != '0.000000') AND ( accountlines.accounttype = 'FU') - AND ( issues.borrowernumber = accountlines.borrowernumber ) - AND ( issues.itemnumber = accountlines.itemnumber ) - AND ( borrowers.borrowernumber = issues.borrowernumber ) - AND ( biblio.biblionumber = biblioitems.biblionumber ) - AND ( biblioitems.biblionumber = items.biblionumber ) - AND ( itemtypes.itemtype = biblioitems.itemtype ) - AND ( items.itemnumber = issues.itemnumber ) - AND ( branches.branchcode = issues.branchcode ) AND (issues.branchcode = ?) AND (issues.date_due <= NOW()) ORDER BY borrowers.surname @@ -1207,18 +1205,17 @@ sub GetOverduesForBranch { accountlines.notify_level, items.location, accountlines.amountoutstanding - FROM issues,borrowers,biblio,biblioitems,itemtypes,items,branches,accountlines + FROM accountlines + LEFT JOIN issues ON issues.itemnumber = accountlines.itemnumber AND issues.borrowernumber = accountlines.borrowernumber + LEFT JOIN borrowers ON borrowers.borrowernumber = accountlines.borrowernumber + LEFT JOIN items ON items.itemnumber = issues.itemnumber + LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber + LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber + LEFT JOIN itemtypes ON itemtypes.itemtype = biblioitems.itemtype + LEFT JOIN branches ON branches.branchcode = issues.branchcode WHERE ( issues.returndate is null ) AND ( accountlines.amountoutstanding != '0.000000') AND ( accountlines.accounttype = 'FU') - AND ( issues.borrowernumber = accountlines.borrowernumber ) - AND ( issues.itemnumber = accountlines.itemnumber ) - AND ( borrowers.borrowernumber = issues.borrowernumber ) - AND ( biblio.biblionumber = biblioitems.biblionumber ) - AND ( biblioitems.biblionumber = items.biblionumber ) - AND ( itemtypes.itemtype = biblioitems.itemtype ) - AND ( items.itemnumber = issues.itemnumber ) - AND ( branches.branchcode = issues.branchcode ) AND (issues.branchcode = ? AND items.location = ?) AND (issues.date_due <= NOW()) ORDER BY borrowers.surname diff --git a/C4/Reserves.pm b/C4/Reserves.pm index a2c9d3a2a4..9d0061cfc9 100644 --- a/C4/Reserves.pm +++ b/C4/Reserves.pm @@ -430,9 +430,9 @@ sub GetReserveFee { my $dbh = C4::Context->dbh; my $const = lc substr( $constraint, 0, 1 ); my $query = qq/ - SELECT * FROM borrowers,categories + SELECT * FROM borrowers + LEFT JOIN categories ON borrowers.categorycode = categories.categorycode WHERE borrowernumber = ? - AND borrowers.categorycode = categories.categorycode /; my $sth = $dbh->prepare($query); $sth->execute($borrowernumber); @@ -447,9 +447,8 @@ sub GetReserveFee { # first find biblioitem records my @biblioitems; my $sth1 = $dbh->prepare( - "SELECT * FROM biblio,biblioitems - WHERE (biblio.biblionumber = ?) - AND (biblio.biblionumber = biblioitems.biblionumber)" + "SELECT * FROM biblio LEFT JOIN biblioitems on biblio.biblionumber = biblioitems.biblionumber + WHERE (biblio.biblionumber = ?)" ); $sth1->execute($biblionumber); while ( my $data1 = $sth1->fetchrow_hashref ) { @@ -612,10 +611,10 @@ sub CheckReserves { # Look up the item by itemnumber my $query = " SELECT items.biblionumber, items.biblioitemnumber, itemtypes.notforloan - FROM items, biblioitems, itemtypes - WHERE items.biblioitemnumber = biblioitems.biblioitemnumber - AND biblioitems.itemtype = itemtypes.itemtype - AND itemnumber=$qitem + FROM items + LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber + LEFT JOIN itemtypes ON biblioitems.itemtype = itemtypes.itemtype + WHERE itemnumber=$qitem "; $sth = $dbh->prepare($query); } @@ -624,7 +623,9 @@ sub CheckReserves { # Look up the item by barcode my $query = " SELECT items.biblionumber, items.biblioitemnumber, itemtypes.notforloan - FROM items, biblioitems, itemtypes + FROM items + LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber + LEFT JOIN itemtypes ON biblioitems.itemtype = itemtypes.itemtype WHERE items.biblioitemnumber = biblioitems.biblioitemnumber AND biblioitems.itemtype = itemtypes.itemtype AND barcode=$qbc diff --git a/C4/Serials.pm b/C4/Serials.pm index feaae7eaf0..30b5deb4e0 100644 --- a/C4/Serials.pm +++ b/C4/Serials.pm @@ -140,12 +140,12 @@ sub GetLateIssues { if ($supplierid) { my $query = qq| SELECT name,title,planneddate,serialseq,serial.subscriptionid - FROM subscription, serial, biblio + FROM subscription + LEFT JOIN serial ON subscription.subscriptionid = serial.subscriptionid + LEFT JOIN biblio ON biblio.biblionumber = subscription.biblionumber LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id - WHERE subscription.subscriptionid = serial.subscriptionid - AND ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3) + WHERE ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3) AND subscription.aqbooksellerid=$supplierid - AND biblio.biblionumber = subscription.biblionumber ORDER BY title |; $sth = $dbh->prepare($query); @@ -153,11 +153,11 @@ sub GetLateIssues { else { my $query = qq| SELECT name,title,planneddate,serialseq,serial.subscriptionid - FROM subscription, serial, biblio + FROM subscription + LEFT JOIN serial ON subscription.subscriptionid = serial.subscriptionid + LEFT JOIN biblio ON biblio.biblionumber = subscription.biblionumber LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id - WHERE subscription.subscriptionid = serial.subscriptionid - AND ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3) - AND biblio.biblionumber = subscription.biblionumber + WHERE ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3) ORDER BY title |; $sth = $dbh->prepare($query); @@ -1431,9 +1431,9 @@ sub ReNewSubscription { my $subscription = GetSubscription($subscriptionid); my $query = qq| SELECT * - FROM biblio,biblioitems - WHERE biblio.biblionumber=biblioitems.biblionumber - AND biblio.biblionumber=? + FROM biblio + LEFT JOIN biblioitems ON biblio.biblionumber=biblioitems.biblionumber + WHERE biblio.biblionumber=? |; my $sth = $dbh->prepare($query); $sth->execute( $subscription->{biblionumber} ); @@ -1828,7 +1828,8 @@ this function delete an issue which has $serialseq and $subscriptionid given on sub DelIssue { my ( $dataissue) = @_; my $dbh = C4::Context->dbh; - ### TODO Add itemdeletion. Should be in a pref ? + ### TODO Add itemdeletion. Would need to get itemnumbers. Should be in a pref ? + my $query = qq| DELETE FROM serial WHERE serialid= ? @@ -2050,9 +2051,9 @@ sub getsupplierbyserialid { my $dbh = C4::Context->dbh; my $sth = $dbh->prepare( "SELECT serialid, serial.subscriptionid, aqbooksellerid - FROM serial, subscription - WHERE serial.subscriptionid = subscription.subscriptionid - AND serialid = ? + FROM serial + LEFT JOIN subscription ON serial.subscriptionid = subscription.subscriptionid + WHERE serialid = ? " ); $sth->execute($serialid); @@ -2077,9 +2078,9 @@ sub check_routing { my ($subscriptionid) = @_; my $dbh = C4::Context->dbh; my $sth = $dbh->prepare( -"SELECT count(routingid) routingids FROM subscriptionroutinglist, subscription - WHERE subscription.subscriptionid = subscriptionroutinglist.subscriptionid - AND subscription.subscriptionid = ? ORDER BY ranking ASC +"SELECT count(routingid) routingids FROM subscription LEFT JOIN subscriptionroutinglist + ON subscription.subscriptionid = subscriptionroutinglist.subscriptionid + WHERE subscription.subscriptionid = ? ORDER BY ranking ASC " ); $sth->execute($subscriptionid); @@ -2240,9 +2241,10 @@ sub getroutinglist { my $dbh = C4::Context->dbh; my $sth = $dbh->prepare( "SELECT routingid, borrowernumber, - ranking, biblionumber FROM subscriptionroutinglist, subscription - WHERE subscription.subscriptionid = subscriptionroutinglist.subscriptionid - AND subscription.subscriptionid = ? ORDER BY ranking ASC + ranking, biblionumber + FROM subscription + LEFT JOIN subscriptionroutinglist ON subscription.subscriptionid = subscriptionroutinglist.subscriptionid + WHERE subscription.subscriptionid = ? ORDER BY ranking ASC " ); $sth->execute($subscriptionid); @@ -2724,8 +2726,8 @@ sub itemdata { my ($barcode) = @_; my $dbh = C4::Context->dbh; my $sth = $dbh->prepare( - "Select * from items,biblioitems where barcode=? - and items.biblioitemnumber=biblioitems.biblioitemnumber" + "Select * from items LEFT JOIN biblioitems ON items.biblioitemnumber=biblioitems.biblioitemnumber + WHERE barcode=?" ); $sth->execute($barcode); my $data = $sth->fetchrow_hashref; diff --git a/C4/Stats.pm b/C4/Stats.pm index d6a0e9ceac..4988ea8419 100644 --- a/C4/Stats.pm +++ b/C4/Stats.pm @@ -96,9 +96,9 @@ sub TotalPaid { my ( $time, $time2, $spreadsheet ) = @_; $time2 = $time unless $time2; my $dbh = C4::Context->dbh; - my $query = "SELECT * FROM statistics,borrowers - WHERE statistics.borrowernumber= borrowers.borrowernumber - AND (statistics.type='payment' OR statistics.type='writeoff') "; + my $query = "SELECT * FROM statistics + LEFT JOIN borrowers ON statistics.borrowernumber= borrowers.borrowernumber + WHERE (statistics.type='payment' OR statistics.type='writeoff') "; if ( $time eq 'today' ) { $query = $query . " AND datetime = now()"; } diff --git a/C4/Suggestions.pm b/C4/Suggestions.pm index 90543d6c05..b5503be28e 100644 --- a/C4/Suggestions.pm +++ b/C4/Suggestions.pm @@ -275,9 +275,8 @@ sub CountSuggestion { else { my $query = qq | SELECT count(*) - FROM suggestions,borrowers + FROM suggestions LEFT JOIN borrowers ON borrowers.borrowernumber=suggestions.suggestedby WHERE status=? - AND borrowers.borrowernumber=suggestions.suggestedby AND (borrowers.branchcode='' OR borrowers.branchcode =?) |; $sth = $dbh->prepare($query); -- 2.20.1