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 "
playerid
year
pct
{$playerid}
{$yr}
{$percent}
";

I can't get it to display playerid

Two issues.

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

Here are the results of the code.

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

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]

Originally Posted by 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

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

[fantom1979]

Originally Posted by lordscarlet

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.