PHP MySql Query into Array

[lbombardier]

A bit of a newb question here; I'm building a little application using PHP and MySql and have a question on how best to perform a specific function...

I need to select the last 20 records form a table, and with those 20 records I need to take the 10 with the lowest value and add them together to come out wiht a single number. My table only has 2 columns: ID and Number.

I can easily get the 20 lowest using:

$query_rsLimited = "SELECT * FROM test ORDER BY testID DESC LIMIT 20";
$rsLimited = mysql_query($query_rsLimited, $ConnClub) or die(mysql_error());
$row_rsLimited = mysql_fetch_assoc($rsLimited);
$totalRows_rsLimited = mysql_num_rows($rsLimited);

My question is how best to get the 10 highest 'Number' values out of the 20 records and sum them.

Help?!

[joelgreen]

1. copy those top 20 values to array

2. sort($array)
[php.net...]

3. loop through first 10 adding amounts

[lbombardier]

Thanks, that's what I suspected. I can definitely handle the PHP sorting and summing, but how do I add the Sql results into an array based on my query noted above?

[phpsir]

$query_rsLimited = "SELECT * FROM test ORDER BY testID DESC LIMIT 20";
$rsLimited = mysql_query($query_rsLimited, $ConnClub) or die(mysql_error());

while($row=mysql_fetch_assoc($rsLimited))
{
$newarray[]=$row
}

$newarray is you need
sort it and sum last 10 is better

[tomda]

1/ Only output fields you need in your query (replace * by name of fields required).
2/ Use array_push to create your array

$newarray = array();
$query_rsLimited = "SELECT id, value FROM test ORDER BY testID DESC LIMIT 20";
$rsLimited = mysql_query($query_rsLimited, $ConnClub) or die(mysql_error());

while($row=mysql_fetch_assoc($rsLimited)) {array_push($newarray, $row);}

[lbombardier]

Thanks for everyone's help. Just as a point of closure, here's how I did it based on all feedback received:

$query_rsLimited = "SELECT * FROM test ORDER BY testID DESC LIMIT 20";
$row_rsLimited = mysql_fetch_assoc($rsLimited);

while($row_rsLimited=mysql_fetch_assoc($rsLimited))
{
$newarray[]=$row_rsLimited['testNum'];
}
sort($newarray);
array_splice($newarray, 10);
$total = array_sum($newarray);
echo $total;

Works great!
Cheers.

[Angelis]

Isnt there an easier away to do it and have the query string work out the value for you so you can just just do a mysql_result() on the query string?

[coopster]

If the version is MySQL >= 4.1 then a subquery would do that, yes.

[lbombardier]

I think I spoke too soon....it seems that when using the LIMIT 20 I only get 19 records. I tested it with 5 and 10, same result. (4 & 9 results found) It doesn't pick up on the first record, in this case testID 20. (Being that I'm sorting DESC)

[eelixduppy]

You have to remove this line directly after your query:

$row_rsLimited = mysql_fetch_assoc($rsLimited); #remove this!

It is advancing the internal pointer before the while loop, hence missing the first result ;)

[lbombardier]

Perfect thanks!

[Angelis]

I think I spoke too soon....it seems that when using the LIMIT 20 I only get 19 records. I tested it with 5 and 10, same result. (4 & 9 results found) It doesn't pick up on the first record, in this case testID 20. (Being that I'm sorting DESC)

Shouldn't it be record 0 - 19?

[coopster]

No, to clarify, all 20 rows were being returned, it's just that the first row was being retrieved outside of the main processing loop.

When using the LIMIT keyword with only one argument, the argument constrains the number of rows returned, in this case 20. If you use two arguments, then the first argument is the offset of the initial row, which is indeed 0 (not 1), but the second argument still specifies the maximum number of rows to return. You don't need to specify both arguments but if you were to use both in this case, you would specify

LIMIT 0, 20

in your SELECT [dev.mysql.com] statement.