Why does this only show one record?

jcbarr · July 24, 2006

Okay here is the code;

[code]<?php
$sql = "SELECT * FROM tradesa WHERE TEAM2='$team'";
$query = mysql_query($sql);
while ($respond = mysql_fetch_assoc($query)) {
echo "<tr><td bgcolor=#FFFFCC>";
if ($respond['PLAYER9']) {
$sql = "SELECT * FROM batrating WHERE PID='{$respond['PLAYER9']}'";
$query = mysql_query($sql);
$player = mysql_fetch_array($query);

echo $player['pos'];
echo " ";
echo $player['firsname'];
echo " ";
echo $player['lastname'];
}
if ($respond['PLAYER10']) {
echo " ";
$sql = "SELECT * FROM batrating WHERE PID='{$respond['PLAYER10']}'";
$query = mysql_query($sql);

$player = mysql_fetch_array($query);
echo $player['pos'];
echo " ";
echo $player['firsname'];
echo " ";
echo $player['lastname'];
}
if ($respond['PLAYER11']) {
echo " ";
$sql = "SELECT * FROM batrating WHERE PID='{$respond['PLAYER11']}'";
$query = mysql_query($sql);

$player = mysql_fetch_array($query);
echo $player['pos'];
echo " ";
echo $player['firsname'];
echo " ";
echo $player['lastname'];;
}
if ($respond['PLAYER12']) {
echo " ";
$sql = "SELECT * FROM batrating WHERE PID='{$respond['PLAYER12']}'";
$query = mysql_query($sql);

$player = mysql_fetch_array($query);
echo $player['pos'];
echo " ";
echo $player['firsname'];
echo " ";
echo $player['lastname'];
}
if ($respond['PLAYER13']) {
echo " ";
$sql = "SELECT * FROM batrating WHERE PID='{$respond['PLAYER13']}'";
$query = mysql_query($sql);

$player = mysql_fetch_array($query);
echo $player['pos'];
echo " ";
echo $player['firsname'];
echo " ";
echo $player['lastname'];
}
if ($respond['PLAYER14']) {
echo " ";
$sql = "SELECT * FROM batrating WHERE PID='{$respond['PLAYER14']}'";
$query = mysql_query($sql);

$player = mysql_fetch_array($query);
echo $player['pos'];
echo " ";
echo $player['firsname'];
echo " ";
echo $player['lastname'];
}
if ($respond['PLAYER15']) {
echo " ";
$sql = "SELECT * FROM batrating WHERE PID='{$respond['PLAYER15']}'";
$query = mysql_query($sql);

$player = mysql_fetch_array($query);
echo $player['pos'];
echo " ";
echo $player['firsname'];
echo " ";
echo $player['lastname'];
}
if ($respond['PLAYER16']) {
echo " ";
$sql = "SELECT * FROM batrating WHERE PID='{$respond['PLAYER16']}'";
$query = mysql_query($sql);

$player = mysql_fetch_array($query);
echo $player['pos'];
echo " ";
echo $player['firsname'];
echo " ";
echo $player['lastname'];
}
echo "</td>";
echo "<td bgcolor=#FFFFCC>";
if ($respond['PITCHER9']) {
$sql = "SELECT * FROM pitrating WHERE PID='{$respond['PITCHER9']}'";
$query = mysql_query($sql);
$PITCHER = mysql_fetch_array($query);

echo $PITCHER['pos'];
echo " ";
echo $PITCHER['firsname'];
echo " ";
echo $PITCHER['lastname'];
}
if ($respond['PITCHER10']) {
echo " ";
$sql = "SELECT * FROM pitrating WHERE PID='{$respond['PITCHER10']}'";
$query = mysql_query($sql);

$PITCHER = mysql_fetch_array($query);
echo $PITCHER['pos'];
echo " ";
echo $PITCHER['firsname'];
echo " ";
echo $PITCHER['lastname'];
}
if ($respond['PITCHER11']) {
echo " ";
$sql = "SELECT * FROM pitrating WHERE PID='{$respond['PITCHER11']}'";
$query = mysql_query($sql);

$PITCHER = mysql_fetch_array($query);
echo $PITCHER['pos'];
echo " ";
echo $PITCHER['firsname'];
echo " ";
echo $PITCHER['lastname'];;
}
if ($respond['PITCHER12']) {
echo " ";
$sql = "SELECT * FROM pitrating WHERE PID='{$respond['PITCHER12']}'";
$query = mysql_query($sql);

$PITCHER = mysql_fetch_array($query);
echo $PITCHER['pos'];
echo " ";
echo $PITCHER['firsname'];
echo " ";
echo $PITCHER['lastname'];
}
if ($respond['PITCHER13']) {
echo " ";
$sql = "SELECT * FROM pitrating WHERE PID='{$respond['PITCHER13']}'";
$query = mysql_query($sql);

$PITCHER = mysql_fetch_array($query);
echo $PITCHER['pos'];
echo " ";
echo $PITCHER['firsname'];
echo " ";
echo $PITCHER['lastname'];
}
if ($respond['PITCHER14']) {
echo " ";
$sql = "SELECT * FROM pitrating WHERE PID='{$respond['PITCHER14']}'";
$query = mysql_query($sql);

$PITCHER = mysql_fetch_array($query);
echo $PITCHER['pos'];
echo " ";
echo $PITCHER['firsname'];
echo " ";
echo $PITCHER['lastname'];
}
if ($respond['PITCHER15']) {
echo " ";
$sql = "SELECT * FROM pitrating WHERE PID='{$respond['PITCHER15']}'";
$query = mysql_query($sql);

$PITCHER = mysql_fetch_array($query);
echo $PITCHER['pos'];
echo " ";
echo $PITCHER['firsname'];
echo " ";
echo $PITCHER['lastname'];
}
if ($respond['PITCHER16']) {
echo " ";
$sql = "SELECT * FROM pitrating WHERE PID='{$respond['PITCHER16']}'";
$query = mysql_query($sql);

$PITCHER = mysql_fetch_array($query);
echo $PITCHER['pos'];
echo " ";
echo $PITCHER['firsname'];
echo " ";
echo $PITCHER['lastname'];
}
echo "</td>";
echo "<td bgcolor=#FFFFCC>";
if ($respond['PICK9']) {
$sql = "SELECT * FROM picks WHERE ID='{$respond['PICK9']}'";
$query = mysql_query($sql);
$pick = mysql_fetch_array($query);
echo

$pick['YEAR'];
echo " ";
echo $pick['ROUND'];
echo " ";
echo $pick['TEAM'];
}
if ($respond['PICK10']) {
echo " ";
$sql = "SELECT * FROM picks WHERE ID='{$respond['PICK10']}'";
$query = mysql_query($sql);
$pick = mysql_fetch_array($query);

echo $pick['YEAR'];
echo " ";
echo $pick['ROUND'];
echo " ";
echo $pick['TEAM'];
}
if ($respond['PICK11']) {
echo " ";
$sql = "SELECT * FROM picks WHERE ID='{$respond['PICK11']}'";
$query = mysql_query($sql);
$pick = mysql_fetch_array($query);

echo $pick['YEAR'];
echo " ";
echo $pick['ROUND'];
echo " ";
echo $pick['TEAM'];
}
if ($respond['PICK12']) {
echo " ";
$sql = "SELECT * FROM picks WHERE ID='{$respond['PICK12']}'";
$query = mysql_query($sql);
$pick = mysql_fetch_array($query);

echo $pick['YEAR'];
echo " ";
echo $pick['ROUND'];
echo " ";
echo $pick['TEAM'];
}
if ($respond['PICK13']) {
echo " ";
$sql = "SELECT * FROM picks WHERE ID='{$respond['PICK13']}'";
$query = mysql_query($sql);
$pick = mysql_fetch_array($query);

echo $pick['YEAR'];
echo " ";
echo $pick['ROUND'];
echo " ";
echo $pick['TEAM'];
}
if ($respond['PICK14']) {
echo " ";
$sql = "SELECT * FROM picks WHERE ID='{$respond['PICK14']}'";
$query = mysql_query($sql);
$pick = mysql_fetch_array($query);

echo $pick['YEAR'];
echo " ";
echo $pick['ROUND'];
echo " ";
echo $pick['TEAM'];
}
if ($respond['PICK15']) {
echo " ";
$sql = "SELECT * FROM picks WHERE ID='{$respond['PICK15']}'";
$query = mysql_query($sql);
$pick = mysql_fetch_array($query);

echo $pick['YEAR'];
echo " ";
echo $pick['ROUND'];
echo " ";
echo $pick['TEAM'];
}
if ($respond['PICK16']) {
echo " ";
$sql = "SELECT * FROM picks WHERE ID='{$respond['PICK16']}'";
$query = mysql_query($sql);
$pick = mysql_fetch_array($query);

echo $pick['YEAR'];
echo " ";
echo $pick['ROUND'];
echo " ";
echo $pick['TEAM'];
}
echo "</td>";
echo "<td align=center>TO</td>";
echo "<td align=center>";
echo $respond['TEAM1'];
echo "</td>";
echo "<td align=center>IN EXCHANGE FOR</td>";
echo "<td bgcolor=#FFFFCC>";
if ($respond['PLAYER1']) {
$sql = "SELECT * FROM batrating WHERE PID='{$respond['PLAYER1']}'";
$query = mysql_query($sql);
$player = mysql_fetch_array($query);

echo $player['pos'];
echo " ";
echo $player['firsname'];
echo " ";
echo $player['lastname'];
}
if ($respond['PLAYER2']) {
echo " ";
$sql = "SELECT * FROM batrating WHERE PID='{$respond['PLAYER2']}'";
$query = mysql_query($sql);

$player = mysql_fetch_array($query);
echo $player['pos'];
echo " ";
echo $player['firsname'];
echo " ";
echo $player['lastname'];
}
if ($respond['PLAYER3']) {
echo " ";
$sql = "SELECT * FROM batrating WHERE PID='{$respond['PLAYER3']}'";
$query = mysql_query($sql);

$player = mysql_fetch_array($query);
echo $player['pos'];
echo " ";
echo $player['firsname'];
echo " ";
echo $player['lastname'];
}
if ($respond['PLAYER4']) {
echo " ";
$sql = "SELECT * FROM batrating WHERE PID='{$respond['PLAYER4']}'";
$query = mysql_query($sql);

$player = mysql_fetch_array($query);
echo $player['pos'];
echo " ";
echo $player['firsname'];
echo " ";
echo $player['lastname'];
}
if ($respond['PLAYER5']) {
echo " ";
$sql = "SELECT * FROM batrating WHERE PID='{$respond['PLAYER5']}'";
$query = mysql_query($sql);

$player = mysql_fetch_array($query);
echo $player['pos'];
echo " ";
echo $player['firsname'];
echo " ";
echo $player['lastname'];
}
if ($respond['PLAYER6']) {
echo " ";
$sql = "SELECT * FROM batrating WHERE PID='{$respond['PLAYER6']}'";
$query = mysql_query($sql);

$player = mysql_fetch_array($query);
echo $player['pos'];
echo " ";
echo $player['firsname'];
echo " ";
echo $player['lastname'];
}
if ($respond['PLAYER7']) {
echo " ";
$sql = "SELECT * FROM batrating WHERE PID='{$respond['PLAYER7']}'";
$query = mysql_query($sql);

$player = mysql_fetch_array($query);
echo $player['pos'];
echo " ";
echo $player['firsname'];
echo " ";
echo $player['lastname'];
}
if ($respond['PLAYER8']) {
echo " ";
$sql = "SELECT * FROM batrating WHERE PID='{$respond['PLAYER8']}'";
$query = mysql_query($sql);

$player = mysql_fetch_array($query);
echo $player['pos'];
echo " ";
echo $player['firsname'];
echo " ";
echo $player['lastname'];
}
echo "</td>";
echo "<td bgcolor=#FFFFCC>";
if ($respond['PITCHER1']) {
$sql = "SELECT * FROM pitrating WHERE PID='{$respond['PITCHER1']}'";
$query = mysql_query($sql);
$PITCHER = mysql_fetch_array($query);

echo $PITCHER['pos'];
echo " ";
echo $PITCHER['firsname'];
echo " ";
echo $PITCHER['lastname'];
}
if ($respond['PITCHER2']) {
echo " ";
$sql = "SELECT * FROM pitrating WHERE PID='{$respond['PITCHER2']}'";
$query = mysql_query($sql);

$PITCHER = mysql_fetch_array($query);
echo $PITCHER['pos'];
echo " ";
echo $PITCHER['firsname'];
echo " ";
echo $PITCHER['lastname'];
}
if ($respond['PITCHER3']) {
echo " ";
$sql = "SELECT * FROM pitrating WHERE PID='{$respond['PITCHER3']}'";
$query = mysql_query($sql);

$PITCHER = mysql_fetch_array($query);
echo $PITCHER['pos'];
echo " ";
echo $PITCHER['firsname'];
echo " ";
echo $PITCHER['lastname'];
}
if ($respond['PITCHER4']) {
echo " ";
$sql = "SELECT * FROM pitrating WHERE PID='{$respond['PITCHER4']}'";
$query = mysql_query($sql);

$PITCHER = mysql_fetch_array($query);
echo $PITCHER['pos'];
echo " ";
echo $PITCHER['firsname'];
echo " ";
echo $PITCHER['lastname'];
}
if ($respond['PITCHER5']) {
echo " ";
$sql = "SELECT * FROM pitrating WHERE PID='{$respond['PITCHER5']}'";
$query = mysql_query($sql);

$PITCHER = mysql_fetch_array($query);
echo $PITCHER['pos'];
echo " ";
echo $PITCHER['firsname'];
echo " ";
echo $PITCHER['lastname'];
}
if ($respond['PITCHER6']) {
echo " ";
$sql = "SELECT * FROM pitrating WHERE PID='{$respond['PITCHER6']}'";
$query = mysql_query($sql);

$PITCHER = mysql_fetch_array($query);
echo $PITCHER['pos'];
echo " ";
echo $PITCHER['firsname'];
echo " ";
echo $PITCHER['lastname'];
}
if ($respond['PITCHER7']) {
echo " ";
$sql = "SELECT * FROM pitrating WHERE PID='{$respond['PITCHER7']}'";
$query = mysql_query($sql);

$PITCHER = mysql_fetch_array($query);
echo $PITCHER['pos'];
echo " ";
echo $PITCHER['firsname'];
echo " ";
echo $PITCHER['lastname'];
}
if ($respond['PITCHER8']) {
echo " ";
$sql = "SELECT * FROM pitrating WHERE PID='{$respond['PITCHER8']}'";
$query = mysql_query($sql);

$PITCHER = mysql_fetch_array($query);
echo $PITCHER['pos'];
echo " ";
echo $PITCHER['firsname'];
echo " ";
echo $PITCHER['lastname'];
}
echo "</td>";
echo "<td bgcolor=#FFFFCC>";
if ($respond['PICK1']) {
$sql = "SELECT * FROM picks WHERE ID='{$respond['PICK1']}'";
$query = mysql_query($sql);
$pick = mysql_fetch_array($query);
echo

$pick['YEAR'];
echo " ";
echo $pick['ROUND'];
echo " ";
echo $pick['TEAM'];
}
if ($respond['PICK2']) {
echo " ";
$sql = "SELECT * FROM picks WHERE ID='{$respond['PICK2']}'";
$query = mysql_query($sql);
$pick = mysql_fetch_array($query);

echo $pick['YEAR'];
echo " ";
echo $pick['ROUND'];
echo " ";
echo $pick['TEAM'];
}
if ($respond['PICK3']) {
echo " ";
$sql = "SELECT * FROM picks WHERE ID='{$respond['PICK3']}'";
$query = mysql_query($sql);
$pick = mysql_fetch_array($query);

echo $pick['YEAR'];
echo " ";
echo $pick['ROUND'];
echo " ";
echo $pick['TEAM'];
}
if ($respond['PICK4']) {
echo " ";
$sql = "SELECT * FROM picks WHERE ID='{$respond['PICK4']}'";
$query = mysql_query($sql);
$pick = mysql_fetch_array($query);

echo $pick['YEAR'];
echo " ";
echo $pick['ROUND'];
echo " ";
echo $pick['TEAM'];
}
if ($respond['PICK5']) {
echo " ";
$sql = "SELECT * FROM picks WHERE ID='{$respond['PICK5']}'";
$query = mysql_query($sql);
$pick = mysql_fetch_array($query);

echo $pick['YEAR'];
echo " ";
echo $pick['ROUND'];
echo " ";
echo $pick['TEAM'];
}
if ($respond['PICK6']) {
echo " ";
$sql = "SELECT * FROM picks WHERE ID='{$respond['PICK6']}'";
$query = mysql_query($sql);
$pick = mysql_fetch_array($query);

echo $pick['YEAR'];
echo " ";
echo $pick['ROUND'];
echo " ";
echo $pick['TEAM'];
}
if ($respond['PICK7']) {
echo " ";
$sql = "SELECT * FROM picks WHERE ID='{$respond['PICK7']}'";
$query = mysql_query($sql);
$pick = mysql_fetch_array($query);

echo $pick['YEAR'];
echo " ";
echo $pick['ROUND'];
echo " ";
echo $pick['TEAM'];
}
if ($respond['PICK8']) {
echo " ";
$sql = "SELECT * FROM picks WHERE ID='{$respond['PICK8']}'";
$query = mysql_query($sql);
$pick = mysql_fetch_array($query);

echo $pick['YEAR'];
echo " ";
echo $pick['ROUND'];
echo " ";
echo $pick['TEAM'];
}
echo "</td>";
echo "<td>";
echo "<a href=trade_console.php?team=";
echo $url;
echo "&ID=";
echo $respond['ID'];
echo "&REJECT=Y>REJECT</A>";
echo " ";
echo "<a href=trade_console.php?team=";
echo $url;
echo "&ID=";
echo $respond['ID'];
echo "&ACCEPT=Y>ACCEPT</A>";
}

?>

</table>
[/code]

Each trade offered to the team can have a possibility of 24 things being offered. Each trade to that team is a separate row in the tradesa table. Any row that has this teams name in the TEAM2 should be shown on this page. The problem is that it will only show one trade at a time. Once they are accepted or rejected (deleted from the table) the next one in line will show up.

I don't know why it is doing this. Any help on this would be great.

P.S. - I know that my code might be clunky...sorry for the long read...

trq · July 25, 2006

What is that mess? You REALLY need to read up on database normalization. While your at it... learning to format your code so its readable would also make people more willing to help.

jcbarr · July 25, 2006

Yeah....I figured that was going to be a problem.

Thanks for at least attempting to read through the mess.

jcbarr · July 25, 2006

If anyone would like to read through all that and offer up suggestions that would be great. The code works, just the while loop isn't working...

Its only printing one row when there are mutliple rows it should find in the table.

trq · July 25, 2006

[quote]If anyone would like to read through all that and offer up suggestions that would be great.[/quote]
How about you try and format it so its readable? Also... following my suggestion of reading up on database normalization would be very benificial for you. The idea of having fields named PLAYER01, PLAYER02 ..... PLAYER10 etc etc is just rediculous.

As for your problem. How many records do you expect the first query to return? What do you get if you put...

[code=php:0]
echo mysql_num_rows($query);
[/code]

Just before your while() ?

jcbarr · July 25, 2006

When I echo the number of rows it says 2, but still only shows one offer.

I will reformat it so it is easier to read as soon as I get home, and if I can I will just edit the post instead of posting again.

I see what you are saying about the database, I should probably just have a table with offers and ids, then a table that holds the players and assigns them team and offer ids.

I really want to get this working for my league in a hurry is why I haven't reworked the database yet...

trq · July 25, 2006

Sorry... I just copy and pasted your code into my editor and was about to have a go at cleaning it up, but really.... there is 186 lines of code there that is so poorly formatted it hurts my eyes, I just cant see what your trying to do. Id'e like to help, but not today.

Looking at it briefly. If you fixed your database design Im sure you could reduce all that code to about 15 lines.

jcbarr · July 25, 2006

Okay I used a program to clean it up and hopefully make it easier to read.

trq · July 25, 2006

Yeah... I cleaned this up myself to take a better look but honestly, Besides the redundant code which could be shortended by using [i]for[/i] loops....

[code=php:0]
for ($i = 9; $i == 16; $i++ ) {
if ($respond['PLAYER'.$i]) {
$sql = "SELECT * FROM batrating WHERE PID='".$respond['PLAYER'.$i]."'";
$query = mysql_query($sql);
$player = mysql_fetch_array($query);
echo $player['pos'];
echo " ";
echo $player['firsname'];
echo " ";
echo $player['lastname'];
}
}
[/code]

it still doesn't make much sense.

What are you expecting you if() statements to do?

[code=php:0]
if ($respond['PLAYER9']) {
[/code]

What makes $respond['PLAYER9'] ever equal TRUE? The fact that it contains data? Im telling you, your whole problem stems from a poor database design. You say you want to get this working quickly? Fix the DB and most of your problems will desolv.

I really want to help, but Im just struglling a little. Maybe explain exactly what it is you want to do aqnd show us your database schema?

jcbarr · July 25, 2006

the PID is all that is placed into the tradesa table. The script then pulls all of their data out of batrating or pitrating and displays it in the tables.

The if statements are ture if there is data in the field, as not every trade involves all fields. Some only use 1 while others will use all of them.

I don't want them echoed if they do not contain any data. The problem is that pitchers and batters are kept in two tables due to the output from the simulation program that I am using.

trq · July 25, 2006

Damn... I think the solution is quite simple. Within your while() loop you are overiding the $query variable (which incidently is a poor variable name choice) with the new queries result resource. This intrun kills your outer loop.

jcbarr · July 25, 2006

Wow....thanks.

I really appreciate you being so patient with me, and I am certainly going to work on redesigning the database structure.

Once again thanks!!

trq · July 25, 2006

Does that meen its working as expected? i really can't believe I didn't see that earlier.

ps; Also... take a look at my for() loop example above.... might save you some code.

jcbarr · July 25, 2006

Yes, it is working perfectly now. Displays all trades for each team instead of just one at a time.

I am going to look at your for loop and I'm also going to restructure the database. I just needed to get it up and working cause the season is in progress. Now I can created another version while they use this one.

Sign In

Why does this only show one record?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information