Need Help with grouping of query results

[cdoyle]

i've been trying to search for a solution on what I want to do,  but not sure if I'm looking for the correct term

 

Here is what I want to do,  I'm creating a page in my game that allow users to see a history of their spying records of other players.  Instead of just a long list of results,  I would like to group the results by the player they spied on. 

 

example:

 

Instead of this a normal table of results.

 

player 1  stat 1,  stat 2,  date

player 2  stat 1,  stat 2,  date

player 1  stat 1,  stat 2,  date

player 1  stat 1,  stat 2,  date

 

 

what I want it to look like is this,  all the results for a player spied on grouped together.

 

player 1

Date:  stat 1, stat 2,

Date:  Stat 1, stat 2

 

 

Player 2

Date:  Stat 1,  Stat 2

 

Anyone have any ideas how to display the results like this? 

[hakimserwa]

not that clear can you post your code

[cdoyle]

Hi,

I don't have any code yet,  I've been trying to find examples of what I want to do but not coming up with anything.

 

basically I want to query my table but instead of just displaying everything row after row.  I want all the data for a specific player to be grouped together,  and do this for each player a person may have spied on.   

[Jessica]

In the query, order by date. Then when looping through, store the last date you showed and the current date. When they no longer match, start a new row and output the date.

[Psycho]

In the query, order by date. Then when looping through, store the last date you showed and the current date. When they no longer match, start a new row and output the date.

 

@Jesirose, you had the right solution, but I think you specified the wrong field. The OP is wanting to show a header whenever the "player" changes.

 

@cdoyle,

 

The solution is basically what jesirose stated, but change it to apply to the player name. Here is some mock code:

$query = "SELECT spied_player, stat_1, stat_2, date
          FROM stat_table
          WHERE spying_player = '$playerID'
          ORDER BY spied_player, date";
$result = mysql_query($query);

$last_spied_player = false; //Flag to track change in spied player
while($row = mysql_fetch_assoc($result))
{
    //Display spied player name if different than last record
    if($last_spied_player !== $row['spied_player'])
    {
        //Add double line break if not first spied player
        if($last_spied_player !== false) { echo "<br><br>\n"; }
        //Set flag
        $last_spied_player = $row['spied_player'];
        //Display player header
        echo "<b>{$last_spied_player}</b><br>\n";
    }

    //Display spied details
    echo "Date: {$row['date']}, {$row['stat_1']}, {$row['stat_2']}<br>"
}

[cdoyle]

In the query, order by date. Then when looping through, store the last date you showed and the current date. When they no longer match, start a new row and output the date.

 

@Jesirose, you had the right solution, but I think you specified the wrong field. The OP is wanting to show a header whenever the "player" changes.

 

@cdoyle,

 

The solution is basically what jesirose stated, but change it to apply to the player name. Here is some mock code:

$query = "SELECT spied_player, stat_1, stat_2, date
          FROM stat_table
          WHERE spying_player = '$playerID'
          ORDER BY spied_player, date";
$result = mysql_query($query);

$last_spied_player = false; //Flag to track change in spied player
while($row = mysql_fetch_assoc($result))
{
    //Display spied player name if different than last record
    if($last_spied_player !== $row['spied_player'])
    {
        //Add double line break if not first spied player
        if($last_spied_player !== false) { echo "<br><br>\n"; }
        //Set flag
        $last_spied_player = $row['spied_player'];
        //Display player header
        echo "<b>{$last_spied_player}</b><br>\n";
    }

    //Display spied details
    echo "Date: {$row['date']}, {$row['stat_1']}, {$row['stat_2']}<br>"
}

 

This worked perfect!

thank you everyone for your help.

 

Chris

[cdoyle]

Hi,

Sorry to have to bump this,  but it's not quite working 100% and I'm not sure why.

I noticed that if you spy on the same person within a few minutes of each other,  they will group like they are supposed too.

 

but if you spy on someone say an hour later,  it no longer groups that data with the previous one.  It will create a new grouping,  but I don't see in the code why it would do this.

 

I'm using ADODB that's why my code looks a little different,  but it should work the same.  Here is what I have.

 

 $gethistory = $db->execute("SELECT p.username, s.ID, p.id as playerid, s.sp y_id, s.strength, s.defense, s.speed, s.money, s.timestamp, s.weapon, s.armor FROM spy_log s 
   INNER JOIN players p on s.spy_id = p.id
   WHERE s.player_id=?", array($player->id));

 

   $last_spied_player = false; //Flag to track change in spied player

            if ($gethistory->recordcount() == 0) {
                echo "You have not spied on anyone yet, <a href='members_all.php'>Select a player and hire a spy to get some information</a>";
            } else {
                while ($result = $gethistory->fetchrow()) {

                    //Display spied player name if different than last record
                    if ($last_spied_player !== $result['username']) {
                        //Add double line break if not first spied player
                        if ($last_spied_player !== false) {
                            echo "<br><br>\n";
                        }
                        //Set flag
                        $last_spied_player = $result['username'];
                        //Display player header
                        echo "<strong>{$last_spied_player}</strong><br/>";
                    }

                    //Display spied details
                    echo "<span style='font-size: 10px; '>" . date('m-d-Y', $result['timestamp']) . " Strength: " . number_format($result['strength']) . " Defense: " . number_format($result['defense']) . " Speed: " . number_format($result['speed']) . " Cash $" . number_format($result['money']) . "</span> <a href='spyhistory.php?act=delete&ID=" . $result['ID'] . "'>Delete</a><br>";
                }
            }
    }
  

 

Do you see anything that would cause it to not group items that were saved later on in time?

[Christian F.]

I don't, but more to the point I don't see anything that groups results at all. Which means that this, and thus your problem, actually happens in another part of your code.

Also, posting an example of the results you get, and an example of what you want it to be, would be very helpful. Lets us actually see what the problem is, rather than trying to guess and imagine it based upon your description of it.

[PFMaBiSmAd]

You need to ORDER BY the playerid and date (as shown in the sample code given) so that rows for the same player are together in the result set.

[cdoyle]

You need to ORDER BY the playerid and date (as shown in the sample code given) so that rows for the same player are together in the result set.

 

Thank You,  I must have removed the Order by when i was troubleshooting it,  and forgot to put it back.

 

It's all working now.