Can this be done in all One query?

[jdubwelch]

I got 2 tables:

 

TABLE: "users"

game_total_id

user_id

game_id

W

L

FW

FL

FPTS

OTW

OTL

OTPTS

PTS

 

 

TABLE "game_totals"

user_id

first_name

last_name

email

password

 

I'm having to use 2 functions to get user specific information from them.  I'm trying to the the record from the last five games (ie 3-2) and the result of the last  game (W or L).

Is there a way I can do it in all one query without the functions?

 

<?php
function getLastFive ($uid) {

$query = "SELECT *  FROM `game_totals` gt WHERE user_id = $uid  ORDER BY `game_id`  DESC LIMIT 0, 5";
$db_query = mysql_query ($query) or die (mysql_error());

$w = 0;
$l = 1; 

while ($row = mysql_fetch_array ($db_query)) {

	$w = $w + $row[W];
	$l = $l + $row[L];

}

$record = $w . '-' . $l;

return $record;

}

function getLastGame ($uid) {

$query = "SELECT *  FROM `game_totals` gt WHERE user_id = $uid  ORDER BY `game_id`  DESC LIMIT 1";
$db_query = mysql_query ($query) or die (mysql_error());

$row = mysql_fetch_array ($db_query);

if ($row[W] == 1) {
	$LG = 'W';
} else {
	$LG = 'L';
}

return $LG;

}

$query = "SELECT u.user_id, CONCAT(u.first_name,  ' ', u.last_name ) AS name, CONCAT(SUM(gt.W),'-', SUM(gt.L)) AS record, SUM(gt.PTS) AS pts, CONCAT(SUM(gt.FW),'-', SUM(gt.FL)) AS F, CONCAT(SUM(gt.OTW),'-', SUM(gt.OTL)) AS OT 
FROM game_totals gt
INNER JOIN users u ON u.user_id = gt.user_id
GROUP BY (u.user_id)
ORDER BY (pts) DESC";

$db_query = mysql_query ($query) or die (mysql_error());

while ($row = mysql_fetch_array ($db_query)) {

$Last5Games = getLastFive ($row[user_id]);
$LastGame = getLastGame ($row[user_id]);

echo "<tr class=\"$rowcolor\">
    <td>[ $rank ] </td>
    <td>$row[name]</td>
    <td>$row[pts]</td>
    <td>$row[record]</td>
    <td>$row[F]</td>
    <td>$row[OT]</td>
    <td>$LastGame</td>
    <td>$Last5Games</td>
  </tr>\n";

}

?>

 

 

[fenway]

I'm trying to the the record from the last five games (ie 3-2) and the result of the last  game (W or L).

Is there a way I can do it in all one query without the functions?

Not really... if you aggregate the group of five, you can't then ask mysql to un-aggregate them.