PING: php people

[Ben E Lou]

So I'm running a couple of queries to determine the percentage of his team's total offense that a player was responsible for. Here's that portion.

$query="SELECT year, abbrev, team, rushingyards, receivingyards, sum(rushingyards)+sum(receivingyards) AS yardsfromscrimmage, lastname, firstname
FROM statsbyseason s
Join fof_playerhistorical h on h.id=s.playerid
join fof_teams t on t.id=s.team
group by playerid, year
order by yardsfromscrimmage desc limit 50";

$result = mysql_query($query);
while($row = mysql_fetch_array($result))

{$query2="SELECT sum(rushingyards)+sum(passyards)-sum(sackedyards) as teamtotalyards
FROM fof_playergamestats s
WHERE year={$row[year]} and team={$row[team]}";


$result2 = mysql_query($query2);
while($row2 = mysql_fetch_array($result2))
{$pct[$row[playerid]][$row[year]]=number_format(100*$row[yardsfromscrimmage]/$row2[teamtotalyards],1);
}
$team[$row[playerid]]=$row[team];
$abbrev[$row[playerid]]=$row[abbrev];
}
So I've got $pct[playerid][year] as an array. The problem is coming in sorting and displaying it. Here's what I have, and it's wrong.

array_multisort($pct,SORT_DESC);
echo "";
echo "";
foreach ($pct as $playerid=>$temp1)

{foreach ($temp1 as $yr=>$percent)

{echo "";
echo "";}}

echo "<table><tbody><tr><th>playerid</th><th>year</th><th>pct</th></tr><tr><td>{$playerid}</td><td>{$yr}</td><td>{$percent}</td></tr></tbody></table>";I can't get it to display playerid

Two issues.

1. I want to sort on percentage. It's not doing it.
2.<s>It's not returning the player id at all.</s> RESOLVED.

Here are the results of the code.

<table> <tbody><tr><th>playerid</th><th>year</th><th>pct</th></tr><tr><td>
</td><td>2011</td><td>40.3</td></tr><tr><td>
</td><td>2020</td><td>29.6</td></tr><tr><td>
</td><td>2018</td><td>30.2</td></tr><tr><td>
</td><td>2012</td><td>49.5</td></tr><tr><td>
</td><td>2014</td><td>28.9</td></tr><tr><td>
</td><td>2013</td><td>27.6</td></tr><tr><td>
</td><td>2026</td><td>33.9</td></tr><tr><td>
</td><td>2010</td><td>42.2</td></tr><tr><td>
</td><td>2017</td><td>38.5</td></tr><tr><td>
</td><td>2009</td><td>42.6</td></tr><tr><td>
</td><td>2024</td><td>39.1</td></tr><tr><td>
</td><td>2004</td><td>52.4</td></tr><tr><td>
</td><td>2005</td><td>55.3</td></tr><tr><td>
</td><td>2007</td><td>46.1</td></tr><tr><td>
</td><td>2021</td><td>35.2</td></tr><tr><td>
</td><td>2022</td><td>31.3</td></tr><tr><td>
</td><td>2023</td><td>42.8</td></tr><tr><td>
</td><td>2015</td><td>31.1</td></tr><tr><td>
</td><td>2025</td><td>43.3</td></tr><tr><td>
</td><td>2006</td><td>41.9</td></tr><tr><td>
</td><td>2019</td><td>32.3</td></tr></tbody> </table>


What am I doing wrong? (And no lectures about doing this in one query. I'm thinking two is going to be just easier, plus I'd really like to improve my multi-dimensional array skills, and this will help.)

[Ben E Lou]

Heh. It's removing some of my table tags from the code, but I'm sure the problem is in the foreach statements and the array sort function.

[cartman]

Don't you have to use 'usort' in there somewhere?

[cartman]

or, try moving the array_multisort after the foreach commands

edit: yeah, I think this is it, because array_multisort needs an array of columns, not rows.

[Ben E Lou]

That's not it. If I move the statement, it only gives me one row. If I completely remove that statement, I get the same output. I've got something wrong in the syntax that's causing it to do...nothing.

[Ben E Lou]

Ha. I figured out why playerid isn't showing up...

$query="SELECT year, abbrev, team, rushingyards, receivingyards, sum(rushingyards)+sum(receivingyards) AS yardsfromscrimmage, lastname, firstname
FROM statsbyseason s
Join fof_playerhistorical h on h.id=s.playerid
join fof_teams t on t.id=s.team
group by playerid, year
order by yardsfromscrimmage desc limit 50";
Hey, dummy! Player ID isn't gonna show up IF YOU DON'T HAVE IT IN THE QUERY!!!

[cartman]

Here's a sample snippet I found that pointed me towards moving the array_multisort:


// Obtain a list of columns
foreach ($data as $key => $row) {
$volume[$key] = $row['volume'];
$edition[$key] = $row['edition'];
}

// Sort the data with volume descending, edition ascending
// Add $data as the last parameter, to sort by the common key
array_multisort($volume, SORT_DESC, $edition, SORT_ASC, $data);

[Ben E Lou]

I don't think that's it. Moving it around isn't helping. I'm not terribly good with arrays yet, so I'm not certain, though.

[lordscarlet]

If I find time today I'll try to play with this. But I don't use PHP enough to know the answer without trying it.

[Coffee Warlord]

Edit: Hang on, trying something.

[fantom1979]

Sorry, I can't be much help. I avoid arrays and will go to great lengths to code without them. If you figure it out, please post it. I run into problems like this all the time. If I see the solution, it might help my own multi-dimensional array problems :)

[lordscarlet]

Sorry, I can't be much help. I avoid arrays and will go to great lengths to code without them. If you figure it out, please post it. I run into problems like this all the time. If I see the solution, it might help my own multi-dimensional array problems :)

I have no idea how you can code while avoiding arrays! :)

[fantom1979]

I have no idea how you can code while avoiding arrays! :)

Since I do not use arrays, I have no idea how you can code while using arrays. :)

Seriously, I have a fantasy football website for my keeper league. I keep track of standings, stats, records, etc. I have spent a considerable amount of time to set up the databases and record input pages to work without arrays. It would have probably taken me a lot less time to just sit down one day and figure it out.

[Coffee Warlord]

I believe this'll do it.


function checkVal( $a, $b )
{
$c1 = $a{percent};
$c2 = $b{percent};

if ( $c1 == $c2 )
return 0;

return ( $c1 > $c2 ) ? -1 : 1;
}

//This is just some test data
$array[5][2010] = 7.5;
$array[5][2011] = 42.5;
$array[8][2010] = 15.5;
$array[8][2011] = 12.5;
$array[2][2010] = 24.5;
$array[2][2011] = 38.5;
//End

$x = 0;
foreach ( $array as $playerid => $temp )
{
foreach ( $temp as $year => $percent )
{
$new_array[$x]{year} = $year;
$new_array[$x]{id} = $playerid;
$new_array[$x]{percent} = $percent;
$x++;
}
}

usort( $new_array, "checkVal" );

foreach ( $new_array as $row => $final )
{
echo "$final[id] - $final[year] ($final[percent])\n";
}

[Ben E Lou]

Thanks for that, CW. I'll give it a try.