how to find the greatest number from an array

Question

Hi

This is the books table on db;

book_ID             writer_ID
--------           -----------
1                      10
2                      10
3                      10
4                      10
5                      10

This is the rates table on the db,

book_ID    rate
-------   --------
1          4
2          3
2          5
2          1
2          4
3          5
4          2
4          5
4          2
4          4
5          3

now, i have the writer_ID at first, and i have to find all book_ID (connected to that writer_ID) and the average rates of each book_ID from the rates table. finally, i have to find the greatest rate average and its book_ID

this is my code

$query="SELECT * FROM books WHERE seller_id ='$id'";
$result = mysql_query($query);

while ($info = mysql_fetch_array($result)) {

//getaveragerate is the function that returns average of the rates from rates table
$arr = array(ID => $info['book_ID'], average => getaveragerate($info['book_ID']));

}


$greatest_average_and_books_id_number = max($arr); // dont know how to get highest average and its ID together from array

that is my question, sorry but english is not my native language, i am trying my best to explain my problem. sometimes i cant and i just stuck.

thanks for understanding.

Answer 1

Or just let the database do it for you:

SELECT max(fieldname) FROM rates WHERE id='34'

Answer 2

If you are limited as to which functions you can perform (ie using some CRUD class):

SELECT * FROM rates WHERE id='34' ORDER BY id DESC LIMIT 1

Answer 3

You haven't told us what fields from the database will be returned by your query. It also looks like you're filtering (WHERE clause) on key column, which should only return one record. Therefore you can strip out everything you have there and only put:

$greatest_record = 34;

No need for a query at all!

With a little more information on what you're doing and what fields you're expecting:

$query = "SELECT id, rate FROM rates";
$result = mysql_query($query);
$myarray = array();
$greatest_number = 0;
while ($row = mysql_fetch_array($result)) {
    myarray[] = $row; // Append the row returned into myarray
    if ($row['id'] > $greatest_number) $greatest_number= $row['id'];
}

// Print out all the id's and rates
foreach ($myarray as $row_num => $row) {
    print "Row: $row_num - ID: {$row['id']}, Rate: {$row['rate']} <br>";
} 

print "Highest ID: $greatest_number";

Note that we maintained what was the greatest number at each row returned from the database, so we didn't have to loop through the $myarray again. Minor optimization that could be a huge optimization if you have tens of thousands of rows or more.

This solution is on the basis that you actually need to use the ID and RATE fields from the database later on, but want to know what the largest ID is now. Anyone, feel free to edit my answer if you think there's a better way of getting the greatest_number from the $myarray after it's generated.

---

Update:

You're going to need several queries to accomplish your task then.

The first will give you the average rate per book:

SELECT 
    book_id,
    avg(rate) as average_rate
FROM Rates 
GROUP BY book_id

The second will give you the max average rate:

SELECT 
    max(averages.average_rate), 
    averages.book_id
FROM (
        SELECT 
            book_id,
            avg(rate) as average_rate
        FROM Rates 
        GROUP BY book_id
     ) 
     as averages
WHERE averages.average_rate = max(averages.average_rate)

This will give you a list of books for a given writer:

SELECT book_id
FROM Books
WHERE writer_id = $some_id

Don't try to do everything in one query. Mixing all those requirements into one query will not work how you want it to, unless you don't mind many very near duplicate rows.

I hope you can use this update to answer the question you have. These SQL queries will give you the information you need, but you'll still need to build your data structures in PHP if you need to use this data some how. I'm sure you can figure out how to do that.