Retrieve field with highest value from a MySQL database using PHP

[twebman84]

Here's my table, which is named appearances:

 

playerID | G_C | G_1b | G_2b | G_3b | G_ss | G_lf | G_cf | G_rf | G_p | G_dh |

aaronha01, 0, 0, 0, 0, 0, 0, 0, 150, 0, 0

aaronha01, 0, 0, 0, 30, 0, 0, 10, 110, 0, 0

willite01, 0, 0, 0, 0, 0, 141, 3, 1, 0, 0

willite01, 0, 0, 0, 0, 0, 122, 33, 2, 1, 0

 

and so on

 

This table shows the games played by certain baseball players in their seasons at certain positions (each record is a different season). For example, the first record shows that the player with an id of aaronha01 played 150 games at G_lf (left field). And so on.

 

For each player (unique playerID) I want to find the position they played the most games at. So, I want to find the sum of all the fields for each unique playerID and then retrieve the column (position) with the highest number in it. I then want to convert that field name to something useful for displaying via PHP. For example if G_ss is the column with the highest total for that unique playerID I want the query results to show "shortstop" not "G_ss"

 

If someone can help me with this, I will have some work for them. I am looking for someone to build a data entry interface for me to be able to update this database myself.

 

Thanks 

[aliento]

query and SELECT all the data then with php make the calculations and print the max. this caqnt be done with a sqql qquery

[codefossa]

Add them up and put the results in their own column so you don't have to add it up in the queries.  Then just use max and limit 1 then echo out your result.

[Barand]

Redesign your table.

 

Columns : PlayerID, season, position, games.

 

eg

 

| playerID  | Season | Position | Games  |

+-----------|--------|----------|--------|

| aaron01  |  1    |  G_rf    |  150  |     

| aaron01  |  2    |  G_rf    |  110  |     

| aaron01  |  2    |  G_cf    |  10  |     

| aaron01  |  2    |  G_3b    |  30  |

[twebman84]

I made a new table, which has this structure:

 

| playerID  | G_c | G_1B | G_2b  |

+-----------|--------|----------|--------|

| aaron01  |  0    |  10    |  600  |     

| allen01  |  0    |  550    |  35  |   

 

So how do I return the field NAME that contains the highest numerical value? I don't want the value itself, I want the FIELD NAME.

[Christian F.]

Please re-read Barand's post, as you obviously misunderstood something.

[Psycho]

Although Brands suggestion is the right one for a properly normalized structure, if this is the only situation where you need to compare data across these fields, you could just do the logic in PHP. Just query all the columns and find the max for each record accordingly.

 

EDIT: Just realized there might be a slightly more efficient method. When processing each record: Strip out the username using array_shift(), then get the max() value from the rest of the array, lastly get the position based upon that max value.

 

$query = "SELECT * FROM appearances";
$result = mysql_query($query);

while($row = mysql_fetch_assoc($result))
{
    $playerID = array_shift($row); //User ID must be 1st value in list
    $top_count = max($row);
    $top_position = array_seach($top_count);

    echo "Player ID: {$playerID}, Top Position: {$top_position}, Times Played: {$top_count}<br>\n";
}

[Barand]

IF normalized, you can also create a "position" table to translate position codes

 

 

code  |  pos_name

 

G_ss  | shortstop

G_lf  | leftfield

G_3b  | 3rd Base

 

etc