I'm getting a weird offset on my data output...

[Jim R]

I'm not even sure how to explain it.  I'm linking a team's roster to its coach.  Now I'm getting a weird offset issue when I output the roster.  It has something to do with the STATUS.

 

Down below DIV class="roster_player_list", my Status values  are 1, 2, 3, or 4, and each is then assigned a word value.  My output should look like this (without the arrow and number) :

 

Returning Starter    <---- 1

#

#

#

 

Key Returner      <--- 2

#

#

 

Varsity Newcomer    <--- 3

#

#

#

 

Key Freshmen  <--- 4

#

 

 

The last "1" (Returning Starter) disappears from my list on both of my test accounts.  I should have four Returning Starters instead of three.  I've changed a player's status from a 2 to a 1, and the list changed.  That player disappeared, the other appeared. 

 

$sql = "SELECT * FROM players as p
INNER JOIN schools as s
WHERE s.coachFirst='".$current_first."' AND s.coachLast='".$current_last."'
AND s.id = p.tid
ORDER BY status, playerLast";

$results = mysql_query($sql);



echo '<div class="roster">';

$team = mysql_fetch_assoc($results);


echo '<div class="roster_team_info">' . $current_first . ' ' . $current_last . '

<div class="school">' . $team['school'] . '</div>
<div class="coach">Coach ' .$team['coachFirst'] . ' ' . $team['coachLast'] .'</div>
<div>Sectional: ' . $team['sectional'] . '</div>
<div>Class: ' . $team['class'] . 'A</div>
';

echo '</div>';

echo '<div class="roster_player_list">';

  $currentStatus = false;  //Flag to detect change in status
    while($player = mysql_fetch_assoc($results))
    {


        if($currentStatus != $player['status'])
        {
            //Status has changed, display status header
            $currentStatus = $player['status'];
		echo '<br><b>';
	if ($currentStatus == '1')  {echo 'Returning Starters';}
	elseif ($currentStatus == '2')  {echo 'Key Returners';}
	elseif ($currentStatus == '3')  {echo 'Varsity Newcomers';}
	elseif ($currentStatus == '4')  {echo 'Key Freshmen';}
		echo '</b><br>';
          
        }
        //Display player info
        echo $player['playerFirst'] . ' ' . $player['playerLast'] . ', ' . $player['feet'] . '\'' . $player['inches'] . '",' . $player['position'] . ', ' . $player['year'] . ';<br>';

    }

[wright67uk]

Should that be <--- 1

 

[Jim R]

No. That's just there to show Returning Starters is value 1.

[PFMaBiSmAd]

$team = mysql_fetch_assoc($results); <---- you are fetching the first row of data from the result set before the start of your while(){} loop, so of course the first player information is missing.

[Jim R]

Oh...I tried a number of ways to output what's just below it in DIV roster_team_info, including just using the $result variable.  I did wonder about that, thinking of the description of that fetch, that it moves the pointer ahead.  How do I go about getting my result?  Separate query or something like affected rows?  The information in that DIV just comes from the schools data table. 

 

Oddly enough, that might answer my question in another post. 

[Jim R]

mysql_affected_rows doesn't work.  : )

 

Is there another function that will accomplish what I want, or do I have a separate query?

[AyKay47]

mysql_affected_rows doesn't work.  : )

 

Is there another function that will accomplish what I want, or do I have a separate query?

mysql_affected_rows() will only work for INSERT, UPDATE, REPACE, or DELETE queries.. not SELECT queries.. you would want to use mysql_num_rows instead.. however I don't think this is what you want... what are you trying to achieve again?

[PFMaBiSmAd]

To reference that team header information before the start of the loop, you could use mysql_result

 

Alternatively, you could fetch the first row (your existing logic), then use mysql_data_seek to return the pointer to the 0'th row.

 

 

[Jim R]

Not sure mysql_result is working.  I put...

 

$team = mysql_result($results, 0);

 

I'm not even sure how the result I got came up.  It should only get one result, so the 0 is the offset value, which means it starts at the first row.  I think I understand that correctly. 

 

JWR Bloom

2

Coach 2 2

Sectional: 2

Class: 2A

[Jim R]

This is the entire code now:

 

$sql = "SELECT * FROM players as p
INNER JOIN schools as s
WHERE s.coachFirst='".$current_first."' AND s.coachLast='".$current_last."'
AND s.id = p.tid
ORDER BY status, playerLast";

$results = mysql_query($sql);


echo '<div class="roster">';

$team = mysql_fetch_assoc($results);



echo '<div class="roster_team_info">' . $current_first . ' ' . $current_last . '

<div class="school">' . $team['school'] . '</div>
<div class="coach">Coach ' .$team['coachFirst'] . ' ' . $team['coachLast'] .'</div>
<div>Sectional: ' . $team['sectional'] . '</div>
<div>Class: ' . $team['class'] . 'A</div>
';

echo '</div>';

echo '<div class="roster_player_list">';



  $currentStatus = false;  //Flag to detect change in status
    while($player = mysql_fetch_assoc($results))
    {


        if($currentStatus != $player['status'])
        {
            //Status has changed, display status header
            $currentStatus = $player['status'];
		echo '<br><b>';
	if ($currentStatus == '1')  {echo 'Returning Starters';}
	elseif ($currentStatus == '2')  {echo 'Key Returners';}
	elseif ($currentStatus == '3')  {echo 'Varsity Newcomers';}
	elseif ($currentStatus == '4')  {echo 'Key Freshmen';}
		echo '</b><br>';
           // echo "<div><b>{$currentStatus}</b></div>\n";
        }
        //Display player info
        echo $player['playerFirst'] . ' ' . $player['playerLast'] . ', ' . $player['feet'] . '\'' . $player['inches'] . '",' . $player['position'] . ', ' . $player['year'] . ';<br>';

    }

[Jim R]

Here's the code that finally worked:

 

$sql = "SELECT * FROM players as p
INNER JOIN schools as s
WHERE s.coachFirst='".$current_first."' AND s.coachLast='".$current_last."'
AND s.id = p.tid
ORDER BY status, playerLast";

$results = mysql_query($sql);


echo '<div class="roster">';

$team = mysql_fetch_assoc($results);



echo '<div class="roster_team_info">' . $current_first . ' ' . $current_last . '

<div class="school">' . $team['school'] . '</div>
<div class="coach">Coach ' .$team['coachFirst'] . ' ' . $team['coachLast'] .'</div>
<div>Sectional: ' . $team['sectional'] . '</div>
<div>Class: ' . $team['class'] . 'A</div>
';

echo '</div>';


echo '<div class="roster_player_list">';

mysql_data_seek($results,0);

  $currentStatus = false;  //Flag to detect change in status
    while($player = mysql_fetch_assoc($results))
    {


        if($currentStatus != $player['status'])
        {
            //Status has changed, display status header
            $currentStatus = $player['status'];
		echo '<br><b>';
	if ($currentStatus == '1')  {echo 'Returning Starters';}
	elseif ($currentStatus == '2')  {echo 'Key Returners';}
	elseif ($currentStatus == '3')  {echo 'Varsity Newcomers';}
	elseif ($currentStatus == '4')  {echo 'Key Freshmen';}
		echo '</b><br>';
           // echo "<div><b>{$currentStatus}</b></div>\n";
        }
        //Display player info
        echo $player['playerFirst'] . ' ' . $player['playerLast'] . ', ' . $player['feet'] . '\'' . $player['inches'] . '",' . $player['position'] . ', ' . $player['year'] . ';<br>';

    }

 

I added the mysql_data_seek in there.  It works nicely now. 

 

Is that the most efficient way to handle multiple queries on a file?  I have other projects that have multiple queries, to the point where they are pretty daunting to change as I adapt them to my needs.  Should I make the conversion to this?  I'm even talking about the help I got on another topic dealing with While / IF nested loop too.