Help writing a query

[heraldic2]

I have once again returned to seek the guidance of you awesome guru's.

I came here before wanting help with this query and I learned that my table design was all wrong. I have since changed the table design and re-worked most of the queries I had. It works great to BTW. However I have found myself in the same place I was before when I first came to this site.

I manage a fantasy football league and on our website I would like to be able for team owners to:

1. use a drop down list to choose a team name (usually there own, but can be any) then hit the select button
   COMPLETE

2. the new page should bring up a list of all the active teams in the league and the chosen teams record against each, with the results of the last time they played each other.

Example:

Chosen team are the Green Bay Packers

6-2 against the Chicago Bears
Last time played Week 2 of 2011
Result: Chicago Bears won 21-17

2-5 against the Cleveland Browns
Last time played Week 16 of 2010
Result: Green Bay Packers won 14-3

3-4 against the Seattle Seahawks
Last time played Week 7 of 2011
Result: Green Bay Packers won 10-0

etc
etc

I have had no success in even getting close to this format.

the three tables that matter are:

CREATE TABLE `game_setup` (
	`game_id` INT(10) NOT NULL AUTO_INCREMENT,
	`week` VARCHAR(7) NOT NULL,
	`year` YEAR(4) NOT NULL,
	`home_id` INT(4) NOT NULL,
	`game_type` ENUM('Normal','Playoff','Super Bowl') NOT NULL DEFAULT 'Normal',
	PRIMARY KEY (`game_id`),
	INDEX `FK_game_setup_week` (`week`),
	INDEX `FK_game_setup_year` (`year`),
	INDEX `FK_game_setup_owners` (`home_id`),
	CONSTRAINT `FK_game_setup_owners` FOREIGN KEY (`home_id`) REFERENCES `owners` (`owner_id`) ON UPDATE CASCADE ON DELETE CASCADE,
	CONSTRAINT `FK_game_setup_week` FOREIGN KEY (`week`) REFERENCES `week` (`week`) ON UPDATE CASCADE ON DELETE CASCADE,
	CONSTRAINT `FK_game_setup_year` FOREIGN KEY (`year`) REFERENCES `year` (`year`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=609;

I know, I know I am bad when it comes to the dates, but in this rare case this format actually works the way I need things to happen.

CREATE TABLE `game_scores` (
	`team_id` INT(10) NOT NULL,
	`game_id` INT(10) NOT NULL,
	`score` FLOAT(12,1) NULL DEFAULT NULL,
	PRIMARY KEY (`team_id`, `game_id`),
	INDEX `FK_game_scores_game_setup` (`game_id`),
	CONSTRAINT `FK_game_scores_game_setup` FOREIGN KEY (`game_id`) REFERENCES `game_setup` (`game_id`) ON UPDATE CASCADE ON DELETE CASCADE,
	CONSTRAINT `FK_game_scores_owners` FOREIGN KEY (`team_id`) REFERENCES `owners` (`owner_id`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

CREATE TABLE `owners` (
	`owner_id` INT(4) NOT NULL AUTO_INCREMENT,
	`teamname` VARCHAR(50) NOT NULL,
	`active` INT(1) NOT NULL DEFAULT '0' COMMENT '0 = not active, 1 =  active, 2 = not active but coming, -1= racing, -2= NCAA',
	`firstname` VARCHAR(20) NOT NULL,
	`date_joined` DATE NOT NULL,
	`rookie` VARCHAR(25) NOT NULL,
	`division` VARCHAR(15) NOT NULL,
	`profile` TEXT NOT NULL,
	INDEX `teamname` (`teamname`),
	CONSTRAINT `owners_ibfk_1` FOREIGN KEY (`division`) REFERENCES `divisions` (`division`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=42;

As always thank you very much for any help given.

[johanafm]

heraldic2;10990786 wrote:

I know, I know I am bad when it comes to the dates, but in this rare case this format actually works the way I need things to happen.

If it's easier to handle data when it's in a certain format, you should obviously go with it. But I'd still urge you to consider using ordinary dates. There are after all functions like YEAR(), WEEK() etc. See Date and Time Functions

heraldic2;10990786 wrote:

Chosen team are the Green Bay Packers

6-2 against the Chicago Bears
Last time played Week 2 of 2011
Result: Chicago Bears won 21-17

Just start by selecting the data from your starting point, which in this case would be the owner table, since someone selected a specific team. Then continue to add in the data you need by joining in relevant tables and whatever columns you need from them. Continue to work your way through until you have everything you need.

Something along the lines of

owners
(
	`owner_id` INT(4) NOT NULL AUTO_INCREMENT,
	`teamname` VARCHAR(50) NOT NULL,
)

-- base info for selected team
SELECT orig.teamname AS origname
FROM owners AS orig
WHERE orig.owner_id = $selected_team



TABLE `game_scores`
(
	`team_id` INT(10) NOT NULL,
	`game_id` INT(10) NOT NULL,
	`score` FLOAT(12,1) NULL DEFAULT NULL,

-- add all games in which selected team participated
SELECT orig.teamname AS origname, orig_score.score AS orig_score
FROM owners AS orig
INNER JOIN game_scores orig_score ON orig.owner_id = game_scores.team_id
WHERE orig.owner_id = $selected_team


TABLE `game_setup` (
	`game_id` INT(10) NOT NULL AUTO_INCREMENT,
	-- I still recommend DATE
	game_date DATE,
	`home_id` INT(4) NOT NULL,
	`game_type` ENUM('Normal','Playoff','Super Bowl') NOT NULL DEFAULT 'Normal',

-- add game_setup data for these games
SELECT orig.teamname AS origname, orig_score.score AS orig_score, YEAR(game_date), WEEK(game_date), home_id
FROM owners AS orig
INNER JOIN game_scores orig_score ON orig.owner_id = game_scores.team_id
INNER JOIN game_setup AS gs ON gs.game_id = orig_score.game_id
WHERE orig.owner_id = $selected_team


-- and do the reverse thing to get info on the teams they played
SELECT orig.teamname AS origname, orig_score.score AS orig_score, YEAR(game_date) AS year, WEEK(game_date) AS week, home_id, target_score.score AS target_score
FROM owners AS orig
INNER JOIN game_scores AS orig_score
	ON orig.owner_id = game_scores.team_id
INNER JOIN game_setup AS gs
	ON gs.game_id = orig_score.game_id
INNER JOIN game_scores AS target_score ON
	target_score.game_id = gs.game_id AND
	target_score.team_id != orig_score.team_id
WHERE orig.owner_id = $selected_team


-- and finally add the base info for the team they played
, target.teamname AS targetname
...
INNER JOIN owners AS target ON target.owner_id = target_score.team_id

And since you want to display the score for only the last game between two teams, one easy way of doing this is with ORDER BY target.teamname, game_date. That way, when you have looped through all games for a specific target to calculate the number of wins/losses, the data from the last row contains info about the last played game.

[heraldic2]

Thank you johanafm,

I actually learned more about using table aliases in queries from your response then a few books I have read on the topic.

I have written the query:

$query = "select selected.teamname AS selected_team, selected_score.score AS selected_score, week.week, year, home_id, ". 
"target_score.score as target_score, target.teamname as targetname, week.ID ".
"from owners as selected ".
"JOIN game_scores AS selected_score ON selected.owner_id = selected_score.team_id ".
"JOIN game_setup ON game_setup.game_id = selected_score.game_id ".
"JOIN game_scores AS target_score ON target_score.game_id = game_setup.game_id AND target_score.team_id != ". "selected_score.team_id ".
"JOIN owners AS target ON target.owner_id = target_score.team_id ".
"JOIN week ON week.week = game_setup.week ".
"WHERE selected.owner_id = $thing ".
"and target.active = 1 ".
"GROUP BY target.teamname, week.ID ";
$result = mysql_query($query);
$row = mysql_fetch_array($result);

Results are good:

selected_team;selected_score;week;year;home_id;target_score;targetname;ID
Xanadu Dragons;416.6;Week 2;2011;12;369.8;America Enforcers;2
Xanadu Dragons;397.4;Week 9;2010;12;394.7;America Enforcers;9
Xanadu Dragons;357.4;Round 1;2010;1;361.6;America Enforcers;22
Xanadu Dragons;360.8;Week 4;2011;11;459.1;Battle Masters;4
Xanadu Dragons;417.0;Week 5;2010;11;301.9;Battle Masters;5
Xanadu Dragons;99.0;Week 1;2006;1;93.0;Camelot Fluffy Butts;1
Xanadu Dragons;156.0;Week 2;2007;2;97.0;Camelot Fluffy Butts;2

I have put the results of the query into an array:

$wins=0;
$losses=0;
echo "Selected team: " . $row['selected_team'] . "<br/>";
while ($row=mysql_fetch_assoc($result)) {
if ($row['selected_score'] > $row['target_score']) {
$wins++;
} else {
$losses++;
}
}
echo $row['targetname'] . "$wins - $losses <br/>";

Question how do I make it count the results from one team and then move on to the next team. I was thinking of putting the php code into a loop, but even then I am not sure as to what the if formula would be.

Thank you for your time

[johanafm]

Something like

$last_target = false;
while ($row = mysql_fetch_assoc())
{
	# Due to ORDER BY target_name in the SQL query,
	# whenever these don't match, you are starting to look at games vs another target team
	if ($last_target && $last_target['target_name'] != $row['target_name'])
	{
		# If $last_target isn't false (as it is the first time), then you need to
		#	output wins, losses etc
		#	close whatever element you opened for this group of games, such as a div element.
		# Using the score info as info for last game played works due to ORDER BY game_date DESC
		if ($last_target)
		{
			printf('
	<div>Wins: <span class="bold">&#37;d</span></div>
	<div>Losses: <span class="bold">%d</span></div>
	<div>Draws: <span class="bold">%d</span></div>
	<div>Last Game (%s): %d - % d
</div>',
				$wins,
				$losses,
				$draws,
				$last_target['game_date'],
				$last_target['orig_score'],
				$last_target['target_score']
			);	
		}

	# reset the variables you need
	$wins = $losses = $draws = 0;

	# Output headers and open a new block element
	printf('<h3>%s</h3>
<div>',
		$row['target_name']
	);
}
if ($row['orig_score'] < $row['target_score'])
	++$losses;
elseif ($row['orig_score'] == $row['target_score'])
	++$draws;
else
	++$wins;

$last_target = $row;
}
# In case you had any rows at all (i.e. not an empty result set
# you will need to output info on wins, losses etc for the last target here,
# just like you did in the if block in the while loop
if ($last_target)
{
	# same stuff ehre
}

[heraldic2]

Once again johanafm you are my hero!

One problem, while I was meddling with your code I think I messed up something as now the display ignores the first database entry. I checked the query and it returns the row, but the php does not include it.

Example
Database contains 2 rows
aa
bb

php shows only second entry
bb

$wins=0;
$losses=0;
$draws=0;
$last_target = false;
echo '<h3>' . $row['selected_team'] . '</h3>';
while ($row = mysql_fetch_assoc($result))
{
    if ($last_target && $last_target['targetname'] != $row['targetname'])
    {
        if ($last_target)
        {
            printf('

<table class="style1" border="1" width="400">
	<tr>
		<th>Opponent Name:</th>
		<th colspan="2">%s</th>
	</tr>
	<tr>
		<th>Wins</th>
		<th>Losses</th>
		<th>Draws</th>
	</tr>
	<tr>
		<td align="center">%d</td>
		<td align="center">%d</td>
		<td align="center">%d</td>
	</tr>
	<tr>
		<th>Last Game:</th>
		<td colspan="2">%s of %s: %.1f - %.1f</td>
		<tr>
</table><br/>',
				$last_target['targetname'],
                $wins,
                $losses,
                $draws,
                $last_target['week'],
                $last_target['year'],
                $last_target['selected_score'],
                $last_target['target_score']
		);

    }

    $wins = $losses = $draws = 0;


}
if ($row['selected_score'] < $row['target_score'])
    ++$losses;
elseif ($row['selected_score'] == $row['target_score'])
    ++$draws;
else
    ++$wins;

$last_target = $row;
}
if ($last_target)
{

}

then a question, that unfortunately I am having a very hard time articulating in a forum post, so instead I will ask what is a good book or a good website to learn how to do things like:

$last_target= false;
if(&last_target && &last_target['targetname'] != $row['targetname'])
{
if($last_target)
{
}
if($last_target)
{
}

as this to me is where the php code is ignoring the first row result but as it is just magic elvish speak I can't diagnose the problem.

Once again thank you very much for your time you > godzilla

[johanafm]

heraldic2;10990891 wrote:

now the display ignores the first database entry.

If you, by "ignore it" mean doesn't about the game results, then yes - all but the last row are "ignored".

heraldic2;10990786 wrote:

Example:

Chosen team are the Green Bay Packers

6-2 against the Chicago Bears
Last time played Week 2 of 2011
Result: Chicago Bears won 21-17

... which I took it to mean they had played 8 games total, which means there are 8 rows, of which you "ignore" all but the last one.

Or did I missunderstand you?

All it does with the information from the previous games is to calculate number of wins and losses total.

[heraldic2]

Sorry about not being clear. Please let me try again.

The query returns the results, and for the sake of example lets say the query returns 22 results.

The php then displays results 2-22 and does not display result 1.

So my team, lets call it Team A, played Team B 1 time, Team C 2 times, and Team D 1 time. The display will show:

Selected team: Team A

Opponent Team C
Wins 1 Losses 1 Draws 0
Last Game 12-30

Opponent Team D
Wins 1 Losses 0 Draws 0
Last Game 14-11

The display does not show (or as I said ignores) row 1 of the query results, which now that I think about it probably would have been a better way of stating the problem.

php does not display row 1 of the database query, it begins its display at row 2.

My little feeble brain thinks that it is in the loop or the start of the loop:

$last_target= false;
if(&last_target && &last_target['targetname'] != $row['targetname'])
{
if($last_target)
{
}
if($last_target)
{
}

I do hope that clears it up. It all sounds so good in my head then somewhere on its way to the keyboard it gets all muddled up.

Thank you for your time

[johanafm]

Ah I see. Seems I managed to leave behind "$last_target" in the initial if condition after I changed the handling of this, so in the first two if conditions in the while loop, just drop $last_target from the first if check

    # Removed: $last_target && 
    if ($last_target['target_name'] != $row['target_name'])
    {
        # That check is made here instead
        if ($last_target)

[heraldic2]

Thanks again johanafm,

but it still seems to leave out the first row returned by the query:

$wins=0;
$losses=0;
$draws=0;
$last_target = false;
echo '<h3>' . $row['selected_team'] . '</h3>';
while ($row = mysql_fetch_assoc($result))
{
    if ($last_target['targetname'] != $row['targetname'])
    {
        if ($last_target)
        {
            printf('

<table class="style1" border="1" width="400">
	<tr>
		<th>Opponent Name:</th>
		<th colspan="2">%s</th>
	</tr>
	<tr>
		<th>Wins</th>
		<th>Losses</th>
		<th>Draws</th>
	</tr>
	<tr>
		<td align="center">%d</td>
		<td align="center">%d</td>
		<td align="center">%d</td>
	</tr>
	<tr>
		<th>Last Game:</th>
		<td colspan="2">%s of %s: %.1f - %.1f</td>
		<tr>
</table><br/>',
				$last_target['targetname'],
                $wins,
                $losses,
                $draws,
                $last_target['week'],
                $last_target['year'],
                $last_target['selected_score'],
                $last_target['target_score']
		);

    }

    $wins = $losses = $draws = 0;


}
if ($row['selected_score'] < $row['target_score'])
    ++$losses;
elseif ($row['selected_score'] == $row['target_score'])
    ++$draws;
else
    ++$wins;

$last_target = $row;
}
if ($last_target)
{
echo "Record Not Found";
} 

If you don't mind I would like to at least try to participate in the diagnoses, since I will have your adult supervision and then won't break anything.

You set the variable, in this case $last_target to false. It could be set to anything, just not a team name.

You start the while loop that says while there is a result from the query do the loop.

Then you give the first condition:
if ($last_target['targetname'] != $row['targetname'])

translated to english is
if the variable $last_target with the query result column name of ['targetname'] does NOT equal the rows ['targetname'] do this: if ($last_target)
which in this case is form the table.

However if the variable $last_target with the query result column name of ['targetname'] IS equal to the rows['targetname'] do this other thing: if ($last_target), which in this case is echo "Record Not Found"

Then somehow the magic elvish sets the variable $last_target to the targetname and it goes through the process.

That being said I would have to say the problem lies in the first time the variable $last_target getting set to row 1's targetname and maybe this should be set outside the while loop first?

So:

$wins=0;
$losses=0;
$draws=0;
$last_target = $row['targetname'];
echo '<h3>' . $row['selected_team'] . '</h3>';
while ($row = mysql_fetch_assoc($result))
{
    if ($last_target['targetname'] != $row['targetname'])
    {
        if ($last_target)
        {
            printf('

As always thank you for taking your time to help me

[heraldic2]

Ok after further review the ruling on the field is over turned.

I tried it my way and, surprise surprise, it did not work.

Then I went thru the code piece by piece, saw where you reset the variable at the end of the first condition of the while loop to $row and tried that, with no success.

So as I don't understand the magic elvish or how it is deciding what $last_target is or when it changes it. I shall be a good padawan and sit patiently for the Master's response.

[johanafm]

heraldic2;10990993 wrote:

while ($row = mysql_fetch_assoc($result))
{
    # snip
}
# Like I said in the original post with this code - here you output the last group of data
if ($last_target)
{
    # record not found= $last_target is NOT false, so there is obviously data 
    # about games vs the last found opponent...
}

# but if last_target IS false
else
{
     # then nothing at all was found...
     echo 'no games';
}

However if the variable $last_target with the query result column name of ['targetname'] IS equal to the rows['targetname'] do this other thing: if ($last_target), which in this case is echo "Record Not Found"

No. The two are not really related. One is done inside the loop on every loop. The outside the loop deals with the last group of data (if any).

heraldic2;10990993 wrote:

Then somehow the magic elvish sets the variable $last_target to the targetname and it goes through the process.

No magic. That's the last line of code INSIDE the loop. Thus, on the 2nd iteration of the loop until the last iteration of the loop, $last_target will contain data from the 1st iteration to the last but one iteration of the loop, and after the loop it will contain data from the very last iteration.

heraldic2;10990993 wrote:

That being said I would have to say the problem lies in the first time the variable $last_target getting set to row 1's targetname and maybe this should be set outside the while loop first?

No