Page 1 of 1

PHP/MySQl Count/Sum question...

Posted: Mon Jan 05, 2009 10:36 am
by Eric Praline
Hi

I have a database for a library system that records book loans. There are 2 tables, like this...

loanRecord - loanID, copyID
copyRecord - titleID, copyID, Title

Each book title may contain 1 or more copies. I am trying to create totals for the number of loans for each book title.

So far I have managed to create a query that successfully finds the copyID, titleID, Title and number of times borrowed for each borrowed copy...

Code: Select all

 
$queryID = mysql_query("SELECT COUNT(loanRecord.CopyID), loanRecord.CopyID, ID, Title
FROM loanRecord
LEFT JOIN copyRecord ON loanRecord.CopyID = copyRecord.CopyID
GROUP BY loanRecord.CopyID
ORDER BY ID,CopyID");
 
... which results in something like...

Code: Select all

 
CopyID  ID  Title           Times borrowed
000114  6   Title A 1
000287  15  Title B 2
000222  23  Title C 7
000223  23  Title C 5
000274  56  Title D 1
000275  56  Title D 2
000276  56  Title D 2
000277  56  Title D 1
 
... but am not sure of how to then total up each of the titleID's. I'm sure it's some simple SUM of the result array or something, but can't get my head round it today (first day back at work!)...

Cheers! :D

Re: PHP/MySQl Count/Sum question...

Posted: Mon Jan 05, 2009 11:25 am
by mattpointblank
Off the top of my head I don't know the code to do what you want, but in true programming forum style, here's a "you're doing it wrong!" reply:

If you have multiple copies of the same book in your database, you should store these books in a separate table and just reference them by ID number instead of repeating the titles ("Title A" etc), that way if you ever need to, say, edit a title, you only do it in one place.

Re: PHP/MySQl Count/Sum question...

Posted: Tue Jan 06, 2009 3:18 am
by Eric Praline
Yes, I know that, but I kind of inherited the data structure when I started this job, so couldn't really change it much!

It's really bugging me now, since it looks so simple a job, but I've searched t'internet and can't find anything quite right - I saw some idea about putting the results in another array and totalling up the numbers that way, but I can't see how you'd total up each 'group' of ID's... maybe there's another, easier way of doing it, or maybe even in one go with the original query.
Anyone have any pointers, please?

Re: PHP/MySQl Count/Sum question...

Posted: Tue Jan 06, 2009 3:36 am
by deejay
I would have thought you could do it working through an array of all your books using 'foreach'. then create another array() filter the results into with some type of 'if' statement using array_push to add to the array. And then total up the array with array_sum.

Hope that helps in someway, and makes sense.

Re: PHP/MySQl Count/Sum question...

Posted: Tue Jan 06, 2009 6:28 am
by Eric Praline
Well I managed to sort it, although not quite in the way I first imagined!

Code: Select all

 
$queryID = mysql_query("SELECT COUNT(loanRecord.CopyID), loanRecord.CopyID, ID, Title
                        FROM loanRecord
                            LEFT JOIN copyRecord ON loanRecord.CopyID = copyRecord.CopyID
                        GROUP BY loanRecord.CopyID
                        ORDER BY ID,CopyID");
        
$resultArray = array(); // array of results from the query
$totalsArray = array(); // array to store the totals
$currentID = 0; // counter
        
while ($row = mysql_fetch_array($queryID)) {
    $CopyID = $row['CopyID'];
    $ID = $row['ID'];
    $CopyIDcount = $row['COUNT(loanRecord.CopyID)'];
    
    $resultArray[$ID] = $CopyIDcount; // create array as key=>value ... ID=>CopyIDcount
    if ($currentID==$ID) { // if $currentID is same as previous value, we need to total up the number of times borrowed
        $totalsArray[$ID] += $CopyIDcount;
    } else { // ... otherwise there is only one loan record for this title, so the total of times borrowed is this value
        $totalsArray[$ID] = $CopyIDcount;
    }
    $currentID = $ID;
}
arsort($totalsArray); // sort value of $totalsArray in reverse order
foreach ($totalsArray as $ID => $total) {
    $queryTitle = mysql_query("SELECT Title FROM copyRecord WHERE ID = $ID");
    while ($row = mysql_fetch_array($queryTitle)) {
        $Title = $row['Title'];
    }
    echo "<tr><td valign=\"top\">".$ID."</td><td>".$Title."</td><td valign=\"top\">".$total."</td></tr>\n";
}
 
... the above code perfectly produces the data in the format I required. Cheers for the pointers guys! :D