PHP MySQL Attempting To Load Data

Question

I have a multi-column table that holds scores for games. The format is that each score has a gameid number attached to it. I then find the highest score for each gameid and display it. The problem is that if a game is deleted and the the gameid numbers are no longer consecutive, it will display 2 of the same high score for the game. An example would be a score of 5000 for gameid 3, 6000 for gameid 4, and 3200 for gameid 5. If I delete gameid 4, the output would be 5000-3, 5000-3, 3200-5.I obviously don't want it to output 2 of the same score. Below is the PHP code I use to do this:

<?php

echo '<table cellpadding="0" class="content" cellspacing="0" width="100%" >';
echo '<tr><th>Game</th><th>Initials</th><th>Score</th><th>Date</th></tr>';

include("includes/sqlconnect.inc");
include("includes/functions.php");
$idnum=1;

    $allscores = mysql_query("SELECT * FROM scores");
    $highid = array(
    );
    while($row = mysql_fetch_array( $allscores ))
    {
    $idnums=$row['gameid'];
    $highid[]=$idnums;      
    }
while($idnum<=max($highid))
{
    $allscores = mysql_query("SELECT * FROM scores WHERE gameid ='$idnum'");
    $array = array(
    );
    while($row = mysql_fetch_array( $allscores ))
    {
    $score=$row['score'];
    $scoreid=$row['scoreid'];
    $array[$scoreid]=$score;      
    }
    $high=doublemax($array);
    $nameid = mysql_query("SELECT scoreid,playerid,score FROM scores WHERE scoreid='$high[i]'");
    while($row = mysql_fetch_array($nameid))
    {
    $player=$row['playerid'];
    }
    $dateid = mysql_query("SELECT scoreid,date FROM scores WHERE scoreid='$high[i]'");
    while($row = mysql_fetch_array($dateid))
    {
    if($row['date']=="")
    {
            $date="Unknown";
    }
    else
    {
            $date=$row['date'];
    }
    }
    $name = mysql_query("SELECT playerid,player_initials FROM players WHERE playerid='$player'");
    while($row = mysql_fetch_array($name))
    {
    $initials=$row['player_initials'];
    }
    $gamename = mysql_query("SELECT gameid,gamename FROM games WHERE gameid='$idnum'");
    while($row = mysql_fetch_array($gamename))
    {
    $game=$row['gamename'];
    }

    $idnum++;

            echo "<tr>";
            echo '<td>'.$game.'</td>';
            echo '<td>'.$initials.'</td>';
            echo '<td>'.number_format($score).'</td>';
            echo '<td>'.$date.'</td>';      
    echo "</tr>";

    }

echo "</table>";

?>

The data is: 5000 3, 6000 4, 3200 5. You delete 6000 4. How come the result now is 5000 3, 5000 3, 3200 5? — Andrius Naruševičius
– Andrius Naruševičius, Commented Nov 13, 2012 at 7:19

Andrius Naruševičius · Accepted Answer · 2012-11-13 07:26:01Z

1

You are doing it way too difficult. Your query should look like:

SELECT
    game_id,
    MAX(score)
FROM
    scores

and then if you want to display more info about that game and not want duplicates, do it like this or so:

SELECT
    h.game_id,
    h.score,
    (
        SELECT 
            /*for example*/ date_played 
        FROM 
            scores 
        WHERE 
            game_id = h.game_id AND 
            score = h.score 
        LIMIT 1
    ) date_played
FROM
(
    SELECT
        game_id,
        MAX(score) score
    FROM
        scores
) hiscores h

In short: mysql is the tool to get data in the form you want. Do not overuse php where it is completely unnecessary.

answered Nov 13, 2012 at 7:26

Andrius Naruševičius

8,6289 gold badges53 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rick Calder · Accepted Answer · 2012-11-13 07:29:55Z

0

The problem is you're basing the number of outputs on the id rather than the number of rows of data returned.

Rather than getting the max score later get that during your initial query

 $allscores = mysql_query("SELECT field1, field2, MAX(score) FROM scores GROUP BY gameid"); // I don't know the field names you need obviously place those instead of field1 etc.

Then base your main loop on a foreach rather than a while.

answered Nov 13, 2012 at 7:29

Rick Calder

18.8k3 gold badges27 silver badges41 bronze badges

Collectives™ on Stack Overflow

PHP MySQL Attempting To Load Data

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related