Jump to content

Why does this only show one record?


jcbarr

Recommended Posts

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><font face=tahoma size=1>";
    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 "<br>";
        $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 "<br>";
        $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 "<br>";
        $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 "<br>";
        $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 "<br>";
        $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 "<br>";
        $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 "<br>";
        $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 "</font></td>";
    echo "<td bgcolor=#FFFFCC><font face=tahoma size=1>";
    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 "<br>";
        $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 "<br>";
        $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 "<br>";
        $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 "<br>";
        $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 "<br>";
        $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 "<br>";
        $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 "<br>";
        $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 "</font></td>";
    echo "<td bgcolor=#FFFFCC><font face=tahoma size=1>";
    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 "<br>";
        $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 "<br>";
        $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 "<br>";
        $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 "<br>";
        $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 "<br>";
        $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 "<br>";
        $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 "<br>";
        $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 "</font></td>";
    echo "<td align=center><font face=tahoma size=1>TO</font></td>";
    echo "<td align=center><font face=tahoma size=1 color=red><b>";
    echo $respond['TEAM1'];
    echo "</b></font></td>";
    echo "<td align=center><font face=tahoma size=1>IN EXCHANGE FOR</font></td>";
    echo "<td bgcolor=#FFFFCC><font face=tahoma size=1>";
    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 "<br>";
        $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 "<br>";
        $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 "<br>";
        $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 "<br>";
        $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 "<br>";
        $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 "<br>";
        $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 "<br>";
        $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 "</font></td>";
    echo "<td bgcolor=#FFFFCC><font face=tahoma size=1>";
    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 "<br>";
        $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 "<br>";
        $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 "<br>";
        $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 "<br>";
        $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 "<br>";
        $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 "<br>";
        $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 "<br>";
        $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 "</font></td>";
    echo "<td bgcolor=#FFFFCC><font face=tahoma size=1>";
    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 "<br>";
        $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 "<br>";
        $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 "<br>";
        $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 "<br>";
        $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 "<br>";
        $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 "<br>";
        $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 "<br>";
        $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 "</font></td>";
    echo "<td><font face=tahoma size=1>";
    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...
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

[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() ?
Link to comment
Share on other sites

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...
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.