[SOLVED] While Loop Dilemma

[hank9481]

I have a table that I want to pull selected data from and output into a table. The problem is, right now, only 1 record of data is being pulled, but much more should be. I'm not sure what I am doing wrong as the while loop should be ongoing.

 

http://mblsim.com/form_hof.php

 

Can someone help me figure this one out?

 

<td colspan="2"> <strong>Career Batting

                                        Stats<br>

                                        </strong>

                                        <table width="90%" border="0" cellpadding="0" cellspacing="0" bgcolor="#000000">

                                         

                                            <? $result = mysql_query("SELECT P.last_name, P.first_name, COUNT(BS.year) AS y, SUM(BS.ab) AS ab, SUM(BS.h) AS h, SUM(BS.hr) AS hr, SUM(BS.r) AS r, SUM(BS.rbi) AS rbi, SUM(BS.sb) AS sb, H.as, H.gg, H.ws, H.mvp, H.roy FROM players P JOIN hall H ON P.player_id=H.player_id JOIN players_career_batting_stats BS ON P.player_id=BS.player_id WHERE BS.split_id=1 AND BS.league_id=100 AND P.position<>1 ORDER BY last_name ASC") or die(mysql_error());

while ($row = mysql_fetch_assoc($result)) {

print "<tr><td><table width=100% border=0 cellspacing=1 cellpadding=5>

                                                <tr bgcolor=#FFFFFF>

                                                  <td><strong>Player</strong></td>

                                                  <td><div align=center><strong>Years</strong></div></td>

                                                  <td><div align=center><strong>Avg</strong></div></td>

                                                  <td><div align=center><strong>Hits</strong></div></td>

                                                  <td><div align=center><strong>HR</strong></div></td>

                                                  <td><div align=center><strong>RBI</strong></div></td>

                                                  <td><div align=center><strong>Runs</strong></div></td>

                                                  <td><div align=center><strong>SB</strong></div></td>

                                                  <td><div align=center><strong>AS</strong></div></td>

                                                  <td><div align=center><strong>GG</strong></div></td>

                                                  <td><div align=center><strong>WS</strong></div></td>

                                                  <td><div align=center><strong>MVP</strong></div></td>

                                                  <td><div align=center><strong>ROY</strong></div></td>

                                                </tr>

                                                <tr bgcolor=#FFFFFF>

                                                  <td>".$row['last_name'].", ".$row['first_name']."</td>

                                                  <td><div align=center>".$row['y']."</div></td>

                                                  <td><div align=center>".$row['h'] / $row['ab']."</div></td>

                                                  <td><div align=center>".$row['h']."</div></td>

                                                  <td><div align=center>".$row['hr']."</div></td>

                                                  <td><div align=center>".$row['rbi']."</div></td>

                                                  <td><div align=center>".$row['r']."</div></td>

                                                  <td><div align=center>".$row['sb']."</div></td>

                                                  <td><div align=center>".$row['as']."</div></td>

                                                  <td><div align=center>".$row['gg']."</div></td>

                                                  <td><div align=center>".$row['ws']."</div></td>

                                                  <td><div align=center>".$row['mvp']."</div></td>

                                                  <td><div align=center>".$row['roy']."</div></td>

                                                </tr>

                                              </table></td></tr>";  } ?>

                                         

                                        </table>

[TomNomNom]

Hi there :-)

 

It seems likely that the problem is with your query rather than your PHP.

 

Could you run the query from something such as phpMyAdmin to make sure that it does return more than one row?

 

If the problem is with your query, I can't really help without seeing the data in the tables. That is, other than to suggest you try a different type of join (left, right, outer etc) dependant on what your data looks like.

 

[hank9481]

Hi there :-)

 

It seems likely that the problem is with your query rather than your PHP.

 

Could you run the query from something such as phpMyAdmin to make sure that it does return more than one row?

 

If the problem is with your query, I can't really help without seeing the data in the tables. That is, other than to suggest you try a different type of join (left, right, outer etc) dependant on what your data looks like.

 

 

You are correct as using PHPMyAdmin does the same thing; however, I plugged different versions of joins and still got the same result. What is it you need to see because I am at a loss?

[TomNomNom]

You are correct as using PHPMyAdmin does the same thing; however, I plugged different versions of joins and still got the same result. What is it you need to see because I am at a loss?

 

Could you provide an SQL dump of the structure and data of each table involved? (you can email me it if you'd rather not post it publicly - PM me for my address). The output from phpMyAdmin's export is fine.

 

I'd like to analyse the data in the tables to figure out what's happening :-)

[TomNomNom]

Try swapping your query out for this one:

 

SELECT
  P.last_name,
  P.first_name,
  COUNT(BS.year) AS y,
  SUM(BS.ab) AS ab,
  SUM(BS.h) AS h,
  SUM(BS.hr) AS hr,
  SUM(BS.r) AS r,
  SUM(BS.rbi) AS rbi,
  SUM(BS.sb) AS sb,
  H.as,
  H.gg,
  H.ws,
  H.mvp,
  H.roy
FROM
  players P
LEFT JOIN
  hall H
ON
  P.player_id = H.player_id
LEFT JOIN
  players_career_batting_stats BS
ON
  P.player_id = BS.player_id
WHERE
  BS.split_id = 1
AND
  BS.league_id = 100
AND
  P.position <> 1
GROUP BY
  P.last_name,
  P.first_name
ORDER BY
  last_name
ASC