Forum Moderators: coopster
After doing a select query to get all of the data from the table, I create a variable called $ppg for points per game [((field goals made * 2) + three-pointers made + free throws made) / games]. I use "( $row = mysql_fetch_array($result))" and "extract($row);" to put the data into variables. The problem is I can't figure out how to sort by a variable. In order to make my top 10 scorers list, I have to "cheat" by ordering by "((field goals made * 2) + three-pointers made + free throws made) / games" in my sql query. I want to be able to order the data by the variable $ppg. I have pasted my code below. I would greatly appreciate any help; keep in mind that I am a true beginner with experience only in MS Excel!
$getdata = "select * from ballers order by (((fgm * 2) + tpm + ftm) / g) desc";
$result = mysql_query($getdata)
or die ("COuldn't execute query");
$j = 0;
echo "<b><u>Top 10 Points Per Game</b></u><br>";
while ( $row = mysql_fetch_array($result))
{
extract($row);
$j++;
$ppg=(("$fgm" * 2) + "$ftm" + "$tpm" ) / "$g";
$f_ppg = sprintf("%01.1f",$ppg);
if ($j <= 10)
{
echo "$j. $firstname $lastname";
if ($team == "")
{
echo " *";
}
elseif ($team!= "")
{
echo " ($team)";
}
echo " $f_ppg<br>";
}
elseif ($j >= 11)
{
echo "";
}
}
It's generally best to let the database do as many calculations as possible, as it's usually faster than PHP. So letting the database order your results wasn't such a bad idea. I'd propose changing your query:
$getdata = "SELECT *, (((fgm * 2) + tpm + ftm) / g) AS ppg FROM ballers ORDER BY ppg desc";
Now you don't need to calculate the ppg again with PHP, as it's already in the query results.
In case you really need to do the sorting with PHP, you'll have to put the data into an array, and sort it with one of PHP's sort() functions.
JUSTAGEEK: I will try your suggestion (if I can) and report back. Thanks.
RONPK: The problem is that I don't know how to do what you are suggesting. The data is in variables created by extract($row) (I think... Isn't it?) with the column names as variable (key?) names. How do I sort by $ppg for example and keep other columns such as $lastname and $firstname lined up?
For further clarification, here is what I'm trying to get-- and I am getting it... I just think I should be getting it another way.
Top 10 Points Per Game
1. Tracy McGrady (Orl) 32.1
2. Kobe Bryant (LAL) 30.0
3. Allen Iverson (Phi) 27.6
4. Shaquille O'Neal (LAL) 27.5
5. Paul Pierce (Bos) 25.9
6. Dirk Nowitzki (Dal) 25.1
7. Tim Duncan (SA) 23.3
8. Chris Webber (Sac) 23.0
9. Kevin Garnett (Min) 23.0
10. Ray Allen * 22.5
RONPK: Thank you for the tip on PHP vs. MYSQL speed. However, I'm still creating the $ppg variable anyway for display purposes. Furthermore, it just seems more concise to sort by the variable since if I wanted to change the formula for points scored, I would only have to change it in one place.
I know asort, arsort, etc., but I can't seem to get them to work.
If I use the AS statement in the SQL query, does that create a temporary column named ppg, which then creates a variable too?
Also, I've seen a heck of a lot of cases where PHP if faster for processing than MySQL.
The data is in variables created by extract($row) (I think... Isn't it?) with the column names as variable (key?) names. How do I sort by $ppg for example and keep other columns such as $lastname and $firstname lined up?
RonPK is abbreviating for brevity's sake. So you still need to SELECT for firstname, lastname but then you also SELECT for the formula he gives you which effectively creates a temporary column (ppg) to sort against, you sort DESC so that highest comes first, and $extract['ppg'] now gives you points per game with highest scorer first and you don't need to calculate it.
$i is your counter, from 1 to 10
echo $i . ". ". $extract['firstname'] . " " . $extract['lastname'] . " (" . $extract['team'] . " " . $extract['ppg'] . "\n";
should give you the desired output. If you have a small number of visitors, let it lie. If you are worried about server overhead, look at Ian's comment, do both and test.
Tom
