array help needed

[michaelowen]

I'm kinda new to arrays, but would it be something like this as a select statement:

 $rs = mysql_query('select table_id,first_name,last_name,one_points,two_points,three_points,four_points,five_points,six_points,seven_points,eight_points from table');        

$res = array();
while($q = mysql_fetch_row($rs)) $res[] = $q;        

foreach($res as $row)   {                

$name = array_shift($row);                

rsort($row);                

// sort in reverse order                

$mydatatop = array_sum(array_slice($row, 0, 7));                

// sum up 7 highest scores                

echo $first_name . $last_name . ' has achieved the total score of ' . $mydatatop . ' points';        

$sql = "UPDATE table SET overall_seven='$mydatatop' WHERE prix_id='$run_row[table_id]'";        

$result = mysql_query($sql) or die('error: '. mysql_error());
}

How would i then sort points one through eight, and total those into a variable called $mydatatop?

thank you!

-mike

[devinemke]

not sure i totally understand. are you trying to rank the overall combined scores (of all the "points" fields)? if so:

<?php
$result = mysql_query('SELECT * from table');
while ($row = mysql_fetch_assoc($result))
{
	foreach ($row as $key => $value)
	{
		if (strstr($key, '_points')) {$points[] = $value;}
	}

$sum_array[$row['table_id']] = array_sum($points);
$data_array[$row['table_id']] = $row;		 
}

arsort($sum_array);

echo 'the total combined scores are:<br><br>';
foreach ($sum_array as $key => $value)
{
	echo '
	ID: ' . $data_array[$key]['table_id'] . '<br>
	first name: ' . $data_array[$key]['first_name'] . '<br>
	last name: ' . $data_array[$key]['last_name'] . '<br>
	total score: ' . $value . '<br><br>
	';
}                 

?>

[michaelowen]

How could just only the highest 7 POINT values? I know i need to sort and then limit, but i'm a bit unsure what spot in the process I would do this.

Thanks!

-Michael

[michaelowen]

does anybody know where I put the sorting variable so that it limits the array to only the top seven "point" values.

-Michael

[paulnaj]

Hi Mike,

This will give you give you a 'total' of all points per name, ordered by that total (highest to lowest) and limited to the top seven only ...

SELECT table_id, first_name, last_name, (one_points + two_points + three_points + four_points + five_points + six_points + seven_points + eight_points) as total
FROM table
ORDER BY total DESC
LIMIT 0, 7

Paul.

[michaelowen]

That's not what i need. I need a total of the highest 7 point values of 10 point values listed PER entrant.

I don't need to rank them overall, i need to break this down per individual.

For example:
Bob Smith ran 10 races, and has 10 different point totals. I will then need to only total the highest 7 values from HIS races, and enter that value as $mydatatop. I will then rank the overall ranking of $mydatatop.

Thanks,

-michael

[devinemke]

$result = mysql_query('SELECT * from table'); 
while ($row = mysql_fetch_assoc($result)) 
{ 
    foreach ($row as $key => $value) 
    { 
        if (strstr($key, '_points')) {$points[] = $value;} 
    }

	$total = 0;
	for ($i = 0; $i < 7; $i++)
	{
		$total += $points[$i];
	}

	echo $row['first_name'] . ' ' . $row['first_name'] . '<br>
	the total for the highest 7 values = ' . $total . '<br><br>';		
}

[michaelowen]

This still doesn't work. It gives the same number about for ALL entrants.

$result = mysql_query('SELECT * from table);
while ($row = mysql_fetch_assoc($result))
{
    foreach ($row as $key => $value)
    {
        if (strstr($key, '_points')) {$points[] = $value;}
    }

    $total = 0;
    for ($i = 0; $i < 7; $i++)
    {
        $total += $points[$i];
    }

    echo $row['first_name'] . ' ' . $row['last_name'] . '<br>
    the total for the highest 7 values = ' . $total . '<br><br>';
    $sql = "UPDATE table SET overall_seven='$total' WHERE table_id='$row[table_id]'";
    $result = mysql_query($sql) or die('error: '. mysql_error());

}

there are TEN point values (i.e. one_points, two_points,etc..) and i need to sort them and total the hightest 7 values into the variable $overall_seven.

any ideas?

-Michael

[devinemke]

$result = mysql_query('SELECT * from table');
while ($row = mysql_fetch_assoc($result))
{
	$points = array();
	foreach ($row as $key => $value)
	{
		if (strstr($key, '_points')) {$points[] = $value;}
	}
	rsort($points);
	$total = 0;
	for ($i = 0; $i < 7; $i++)
	{
		$total += $points[$i];
	}
	echo $row['first_name'] . ' ' . $row['first_name'] . '<br>
	the total for the highest 7 values = ' . $total . '<br><br>';
}

[michaelowen]

i can only get this to do the first entry in my table (which works correctly!), but then i get an error:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

however it successfully updates the first entry.

$result = mysql_query('SELECT table_id,first_name,last_name,one_points,two_points,three_points,four_points,five_points,six_points,seven_points,eight_points,nine_points,ten_points from table);
while ($row = mysql_fetch_assoc($result))
{
    foreach ($row as $key => $value){
        if (strstr($key, '_points')) {
        $points[] = $value;
    } }
    rsort($points);
    $total = 0;
    for ($i = 0; $i < 7; $i++)
    {
        $total += $points[$i];
    }
    echo $row['first_name'] . ' ' . $row['last_name'] . '<br>
    the total for the highest 7 values = ' . $total . '<br><br>';
    $sql = "UPDATE table SET overall_seven='$total' WHERE table_id='$row[table_id]'";
    $result = mysql_query($sql) or die('error: '. mysql_error());
}

is this looping properly?

-mike