*SOLVED* Can I do this with an array?

[jcbarr]

Okay so here is what is going on. I have a table that looks like this;

PID YEAR NAME SP AC AG ST HA EN IN DI

Each PID will have multiple years associated with it. So for each PID there will be several rows of data in the table.

So I pull the data with a query like this;

[code]$sql="
SELECT *
FROM archive
WHERE PID='$pid'
ORDER BY YEAR DESC";[/code]

Okay so then I want to display the data in a table with a while loop that creates a new table row for every year listed and then displays each column in a table cell within that row.

I know how to do all that and it isn't a problem. I want to be able to color code specific changes in the data. For all the columns other than PID, NAME and YEAR I want it to show the change from one year to the next. So when I display the data year 2000 will be on top, 1999 under that and so on.

How would I go about telling it to look at the data and find the difference between the SP value for 2000 and the SP value for 1999? Then if the 2000 value is higher than the 1999 value change the font color to green, if it is lower change the font color to red.

Can this be done with an array of some sort or is there an easier way to accomplish this?

If you need to see the actual code for displaying the data I can whip it up real quick and post it if that will help. Basically thought I just wanted to make sure this sort of thing could be done before I went and coded the page that is going to call the data.

Thanks for the help!!

[printf]

so you only want the change to happen wheen the row year changes, not for every row, what mean

row 1 = 2000 change to green
row 2 = 2000 no change (no color)
row 3 = 2000 no change (no color)
row 4 = 1999 change to red
row 5 = 1999 no change (no color)
row 6 = 2000 change to green

OR

row 1 = 2000 make it green
row 2 = 2000 make it green
row 3 = 2000 make it green
row 4 = 1999 make it red
row 5 = 1999 make it red
row 6 = 2000 make it green


me!

[jcbarr]

No not really. I am selecting rows based on the PID field. Each YEAR associated with the PID will be unique. So the display will look like this;

2006 86 88 81 75 64 99 52 64
2005 85 87 81 77 64 98 52 64

If this were the only two years displayed then I would want the 86,88 and 99 to be green while the 75 would be red.

The last row displayed (the earliest year in the table) would display with no color changes at all because that would be the original ratings for the player.

Hope that helps.

[jcbarr]

Okay, here is an example of what I actually want the page to look like.

http://www.usafl.net/manage/player_profile.php?PLAYER=10087

That is from another site, but I am trying to emulate that format.

[Barand]

try
[code]
<STYLE content='text/css'>
th {
    font-size: 0.8em;
    font-weight: 300;
    text-align: center;
    color: #000000;
    background-color: #EEEEEE;
}
td {
    font-size: 0.8em;
    font-weight: 300;
    text-align: center;
    color: #000000;
    background-color: #FFFFFF;
}
td.less {
    background-color: #FFC0C0;
}
td.more {
    background-color: #C0FFC0;
}
</STYLE>
<?php
$data = array (
    array (2006, 86, 88, 81, 75, 64, 99, 52, 64 ),
    array (2005, 85, 87, 81, 77, 64, 98, 52, 64 )
);
$heads = array ('YEAR', 'SP', 'AC', 'AG', 'ST', 'HA', 'EN', 'IN', 'DI' );

echo "<table border='1' cellpadding='2' cellspacing='0'>\n";
//headings
echo '<tr>';
foreach ($heads as $h) echo "<th>$h</th>";
echo '<tr>';

$k = count($data);
for ($y=0; $y < $k-1; $y++) {
    echo "<tr><td>{$data[$y][0]}</td>";
    for ($i=1; $i<=8; $i++) {
       
        if ($data[$y][$i] < $data[$y+1][$i])
            $class = 'class="less"';
        elseif ($data[$y][$i] > $data[$y+1][$i])
            $class = 'class="more"';
        else $class = '';

        echo "<td $class>{$data[$y][$i]}</td>";
    }
    echo '</tr>';
}
// first year
echo "<tr><td>{$data[$k-1][0]}</td>";
for ($i=1; $i<=8; $i++) {
    echo "<td>{$data[$k-1][$i]}</td>";
}
echo '</tr>';
echo '<table>';
?>[/code]

You can create the array initially with
[code]<?php
$sql = "SELECT year,sp,ac,ag,st,ha,en,in,di
        FROM archive
        WHERE pid = '$pid'
        ORDER BY year DESC";
$res = mysql_query($sql) or die (mysql_error());
while ($row = mysql_fetch_row($res)) {
    $data[] = $row;                            // edited following Sasa's reply below
}
?>[/code]

[sasa]

Barand mean $data[] = $row; in line 8 in 2nd part of code

[Barand]

Ooops! Your right.

[jcbarr]

Works beautifully. Except for the fact that I had to change the column name from IN to INTEL...darn sql functions don't play nice when they have the same name as a column in your table..

Thanks for all the help guys.