array sort help needed

A

Anonymous

Guest
I have a table that includes many entrants that have EACH competed in 10 events. Each event has a specific point total. What i'm trying to do is calculate the highest 7 values for EACH participant and enter that total into a variable called $overall_seven.

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

        $overall = 0;
        for ($i = 0; $i < 7; $i++)
        {
            $overall+= $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='$overall' 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
 
I suggest have a table with colums id, first_name, last_name
Make the id primary and auto-increment

Then have another table with the columns id, activity, points
The id will be the id of the person the activity and points relate to

That way, you could just have something like this
Code: 
$result_player = mysql_query('SELECT * FROM Participants);
while ($row = mysql_fetch_array($result_player)) {
    $query  =  'SELECT * FROM Activities';
    $query .= ' WHERE ID=' . $row['id'];
    $query .= ' ORDER BY Points DESC';
    $query .= ' LIMIT 0,7';
    $result_activities = mysql_query($query);
    $total_pts = 0;
    while ($act_row = mysql_fetch_array($result_activies)) {
        $total_pts += $actrow['points'];
    }
    echo '<br><br>';
    echo $row['first_name'] . ' ' . $row['last_name'] . ';
    echo '<br> the total for the highest 7 values = ' . $total_pts; 
}

That might work.
 
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.

Code: 
$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
 
Code: 
$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);
Click to expand...
there is a typo you have not closed the single quote...[/code]
 
I still think my way is easier.
It is easier.
You don't have to do all that extra coding to sort the array yourself.
There are more stuff you can do with it. (example: top 5 people who scored the highest in a certain events)

But of course, I do not know what you are going to do with this, so maybe your way will work fine with what you are doing.
 
I defiately agree, however the full database is already built, and i will only be doing this one conversion and then shutting it down. Next year, I will be following your ideas.

However, why isn't my current coding looping properly? it works great for the first entry, and then breaks (as noted).

help help.

-mike
 
because you are setting $result again in the loop

Code: 
    $sql = "UPDATE table SET overall_seven='$total' WHERE table_id='$row[table_id]'"; 
    $result = mysql_query($sql) or die('error: '. mysql_error());

You could try another variable in place of result
 
this elminates the error, but it still only correctly works on the first entry. It gives totally wrong numbers starting with the 2nd entry.

Is the looping syntax correct?

-Mike
 
Code: 
    foreach ($row as $key => $value){ 
        if (strstr($key, '_points')) { 
        $points[] = $value; 
    } }

I think you need to delete $points (maybe setting it to NULL will work) at the end of the loop so it will start a new one instead of continuing the old points[]
 
You must log in or register to reply here.
Back
Top