php code does not display final result

[heraldic2]

I have a database that contains results of fantasy football games.  I have a query that returns the results of the games team X played against the other teams in the league.  The query works perfect in the database.

 

Results are

Opponent Name: America Enforcers

Wins Losses Draws

1             1             0

Last Game: Week 2 of 2011: 416.6 - 369.8

 

the query and the php code cycle thru each team that team X has played and brings up team X's record and the results of the last game played.

 

My problem is that the code stops one team short.  So Team X played teams A, B, C, D, E

but the code only returns teams A, B, C, D

E is left off. 

 

I can't for the life of me figure out what silly mistake I made.

 

<?php 
include_once('../other/functions.php');
$con = mysql_connect($hostname, $username, $password) 
OR DIE ('Unable to connect to database! Please try again later.');
$db = mysql_select_db($dbname, $con);
$thing = $_GET['thing'];
$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, year, week.ID ";
$result = mysql_query($query);
$row = mysql_fetch_array($result);
$result1 = mysql_query($query);
$row1 = mysql_fetch_array($result1);
if (!$result) {
    die('Invalid query: ' . mysql_error());
}
$wins=0;
$losses=0;
$draws=0;
$last_target = false;
echo '<h2>' . $row1['selected_team'] . ' vs. Active Teams</h2>';
while ($row = mysql_fetch_assoc($result))
{
    if ($last_target['targetname'] != $row['targetname'])
    {
        if ($last_target)
        {
            printf('
<table border="1" width="600">
<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;
}

?>

 

 

Any ideas on how to make the magic elvish be good???

Thank you for your time.

 

[smerny]

You sure it's not the A that's getting left out, rather than the E?

 

 

$last_target = false;
echo '<h2>' . $row1['selected_team'] . ' vs. Active Teams</h2>';
while ($row = mysql_fetch_assoc($result))
{
    if ($last_target['targetname'] != $row['targetname'])
    {
        if ($last_target)
        {

$last_target will always be false the first time through

 

 

Edit: nevermind, I see you are pulling the last target rather than the row... so thats the problem..

 

your loop is going through n while your data is going through n-1... so the last time through, lets say the 5th time through.. you're displaying the 4th row.. but there is no 6th row so it wont go through a 6th time (which would show the 5th row data)

[heraldic2]

Thank you smerny for your reply.

 

I understand what you are telling me about my while loop logic, however I am not at all sure as to how to fix it.

 

I am thinking that I need to re-write the query so that:

$i = 0

while $row < $i

run code

 

$i = $i + 1

 

Am I at least in the ball park?

[PFMaBiSmAd]

Your code is fetching the first row before the start of your while(){} loop, so of course you are missing one row of the result set. You are also executing the query twice, why are you doing that?

$result = mysql_query($query); // execute query once
$row = mysql_fetch_array($result); // fetch the first row from the result set and advance the result pointer to the next row
$result1 = mysql_query($query); // execute the query again
$row1 = mysql_fetch_array($result1); // fetch the first row from the result set and advance the result pointer to the next row

 

 

[heraldic2]

Thank you for your response PFMaBiSmAd

 

As for $row and $row1 I was working on a different idea for something not dealing with the current problem.

 

As I am a total n00b at this I hate to question you, but if the result were getting a row before the while loop starts then wouldn't the problem be that the first result is not being displayed not the last result?

[Jessica]

your loop is going through n while your data is going through n-1... so the last time through, lets say the 5th time through.. you're displaying the 4th row.. but there is no 6th row so it wont go through a 6th time (which would show the 5th row data)

[PFMaBiSmAd]

As already stated, you are fetching the first row of data from the result set before the start of your while(){} loop. The first pass through the while(){} loop then fetches the 2nd row of he result set.

 

You should also only execute the query once. To reset the result pointer back to the first row in the result set after you get the data to produce the ...  vs. Active Teams output, use mysql_data_seek($result,0);

 

[PFMaBiSmAd]

$result = mysql_query($query); // execute query once
if (!$result) {
    die('Invalid query: ' . mysql_error());
}
// assume the query matched at least one row
$row = mysql_fetch_array($result); // get data for the ... vs. Active Teams output
$wins=0;
$losses=0;
$draws=0;
$last_target = false;
echo '<h2>' . $row['selected_team'] . ' vs. Active Teams</h2>';
mysql_data_seek($result,0);
while ($row = mysql_fetch_assoc($result))

[heraldic2]

Thank you PFMaBiSmAd for your advice.

 

I tried what you said and I still get the same results, Team E is left off the list.

 

 

Thank you very much for trying though.  Twas a mighty effort.

[xyph]

jesirose has told you. You're constantly referencing the $last_target array, which contains the previous row's results.

 

Since $last_target isn't defined until the end of the first loop, the last row gets populated at the end of the last loop. When the last loop ends, it doesn't loop again to display the last row's data that you just populated $last_target with.

 

Your design is completely bonkers. I would honestly scrap what you have and plan it out before you begin to code

[heraldic2]

Thanks to all.

 

I will go re-design the code.