Jump to content

Archived

This topic is now archived and is closed to further replies.

jcbarr

Why does this only show one record?

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...

Share this post


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

Share this post


Link to post
Share on other sites
Yeah....I figured that was going to be a problem.

Thanks for at least attempting to read through the mess.

Share this post


Link to post
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.

Share this post


Link to post
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() ?

Share this post


Link to post
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...

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites
Okay I used a program to clean it up and hopefully make it easier to read.

Share this post


Link to post
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?

Share this post


Link to post
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.

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites
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!!

Share this post


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

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

×

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.