Sorting information retrived and modified from two tables...

[geekygumshoe]

I'm working on a Fantasy Football site for a client.  The database has projections he's uploaded, I've created a script that pulls the information from the database and then does the math based on the visitors league settings to give projected fantasy points.

 

However, I have it pulling the info from the database with mysqli_fetch_array - and then it does the math in PHP (in a while loop).  I then have it echo'ing out a nice table with all the stats and the fantasy points. Obviously, I need to be able to order this information by the Fantasy Points the math figures out (highest points on top).

 

My first thought was to put all of this information in an array, then order the array by fantasy points.  However, the array information I find online doesn't really help me figure out how to add array information in a while loop.

 

My second thought was to just create another table in the mysql database that held the information and the fantasy points - but it just seems like an unnecessary step (and unnecessary use of disk space).

 

Anyone happen to know a simple solution to my problem? 

 

Much thanks.

[JAY6390]

Why not do the math in the query itself. Then assign it to a value and order by that? It would be much easier

[geekygumshoe]

I had absolutely no idea you could do that!  Well... rock on.  Thanks!

[JAY6390]

No problem

[geekygumshoe]

Alright, I've spent the last couple of hours trying to do math in a query, and I'm getting errors like crazy.  Here's my code, any help?

 

$year = $_POST['year'];
$week = $_POST['week'];
$pos = $_POST['pos'];
$league_name = $_POST['league_name'];

$get_projections_sql = "SELECT * FROM projections WHERE year = '$year' AND week = '$week' AND pos = '$pos'";
$get_projections_query = mysqli_query($dbc, $get_projections_sql);
echo '<h1>Rankings/Projections</h1><form method=post action=rankings.php>Year: <select name="year">';
$get_years_sql = "SELECT year FROM years_months";
$get_years_query = mysqli_query($dbc, $get_years_sql);
while($get_years = mysqli_fetch_array($get_years_query)) {
echo '<option value="'.$get_years['year'].'">'.$get_years['year'].'</option>';
}
echo '</select>Week:<select name="week">';
$get_weeks_sql = "SELECT week FROM years_months";
$get_weeks_query = mysqli_query($dbc, $get_weeks_sql);
while($get_weeks = mysqli_fetch_array($get_weeks_query)) {
echo '<option value="'.$get_weeks['week'].'">'.$get_weeks['week'].'</option>';
}
echo '</select>POS:<select name="pos"><option value="QB">QB</option><option value="WR">WR</option><option value="RB">RB</option><option value="TE">TE</option><option value="K">K</option></select> League:<select name="league_name">';
$get_users_leagues = "SELECT league_name FROM leagues WHERE user_id = '$user_id'";
$get_users_leagues_query = mysqli_query($dbc, $get_users_leagues);
while($get_league = mysqli_fetch_array($get_users_leagues_query)) {
echo '<option value="'.$get_league['league_name'].'">'.$get_league['league_name'].'</option>';
}
echo '</select><input type="submit" name="submit" value="Submit"></form>';
if(empty($league_name)) {
echo '<br/><br/>Please select your league name from the drop-down menu above.<br/>';
}
else {
echo '<br/><br/><table align="center">
<tr>
<td><b>#</b></td>
<td><b>POS</b></td>
<td><b>NAME</b></td>
<td><b>PROJECTED POINTS</b></td>
</tr>';
$i = 1;
while($get = mysqli_fetch_array($get_projections_query)) {
$get_league_sql = "SELECT * FROM leagues WHERE user_id = '$user_id' AND league_name = '$league_name'";
$get_league_query = mysqli_query($dbc, $get_league_sql);
while($league = mysqli_fetch_array($get_league_query)) {
$incompletes = $get['pass_attempts'] - $get['pass_completion'];
$pts_for_completion = $league['pt_per_comp'];
$pen_pt_per_inc = $league['pen_pt_per_inc'];
$pts_per_pass_td = $league['pt_per_pass_td'];
$pen_pt_per_int = $league['pen_pt_per_int'];
$rush_att_per_pt = $league['rush_att_per_pt'];
$rush_yds_per_pt = $league['rush_yds_per_pt'];
$pts_per_rush_td = $league['pts_per_rush_td'];
$rec_per_pt = $league['rec_per_pt'];
$rec_yds_pt = $league['rec_yds_pt'];
$pts_per_rec_td = $league['pts_per_rec_td'];
$pts_per_fg = $league['pts_per_fg'];
$pen_pts_missed_fg = $league['pen_pts_missed_fg'];
$pts_per_extra_point = $league['pts_per_extra_point'];

$total_pts_for_completion = $pts_for_completion * $get['pass_completion'];
$total_pts_for_inc = $pen_pt_per_inc * $incompletes;
$total_pts_per_pass_td = $pts_per_pass_td * $get['pass_td'];
$total_pen_pt_per_int = $pen_pt_per_int * $get['pass_int'];
$total_rush_att_per_pt = $rush_att_per_pt * $get['rush_atts'];
$total_rush_yds_per_pt = $get['rush_yds'] / $rush_yds_per_pt;
$total_pts_per_rush_td = $rec_per_pt * $get['rush_td'];
$total_rec_per_pt = $rec_per_pt * $get['rec_rec'];
$total_rec_yds_pt = $get['rec_yds'] / $rec_yds_pt;
$total_pts_per_rec_td = $pts_per_rec_td * $get['rec_td'];

$total_points = $total_pts_for_completion + $total_pts_for_inc + $total_pts_per_pass_td + $total_pen_pt_per_int + $total_rush_att_per_pt + $total_rush_yds_per_pt + $total_pts_per_rush_td + $total_rec_per_pt + $total_rec_yds_pt + $total_pts_per_rec_td;

echo '<tr>
<td>'.$i.'</td>
<td>'.$get['pos'].'</td>
<td>'.$get['player'].'</td>
<td>'.$total_points.'</td></tr>';
$i++;
}
}
}