MySQL sorting with PHP

Question

I'm trying to accomplish the following situation:

$mysql_query = "
  SELECT * 
  FROM st_users 
  WHERE 
      `user_comp_supervisor_id` = '$team_supervisor' AND 
      `user_exempt_from_goals` = '0' 
  ORDER BY 'calculate_progress_percent()' ASC
";

I know that I can't accomplish ordering by a function in a MySQL statement, but I'm trying to figure out how to take all the returned records, and then order them in order of highest to lowest from a php function result. Any ideas would be greatly appreciated; I've been trying to wrap my head around this for a few hours now... :-(

        function diy_calc_progress_percent($user_id,$period_id,$period_week_number)
    {
        $this->user_id = $user_id;
        $this->period_id = $period_id;
        $this->period_week_number = $period_week_number;

        if ($this->period_week_number == 1)
        {
            $this->week_id = mysql_result( mysql_query(" SELECT `period_week_one` FROM `st_comp_periods` WHERE `period_id` = '$this->period_id' "),0 );
        }
        else if ($this->period_week_number == 2)
        {
            $this->week_id = mysql_result( mysql_query(" SELECT `period_week_two` FROM `st_comp_periods` WHERE `period_id` = '$this->period_id' "),0 );
        } 
        else
        {
            echo "Week number not valid.";
            exit();
        }

        $this->week_start_date = mysql_result( mysql_query(" SELECT `week_start_date` FROM `st_comp_weeks` WHERE `week_id` = '$this->week_id' "),0 );

        $this->week_end_date = mysql_result( mysql_query(" SELECT `week_end_date` FROM `st_comp_weeks` WHERE `week_id` = '$this->week_id' "),0 );

        $this->user_department = $this->user_info($this->user_id,"user_comp_department_id");

        $this->user_week_diy_goal = mysql_result( mysql_query(" SELECT `goal_diy_department` FROM `st_comp_department_goals` WHERE `goal_department_id` = '$this->user_department' AND `goal_week_id` = '$this->week_id' "),0 );

        $this->calc_totals_result = mysql_query("SELECT SUM(record_total_diy_revenue) AS user_week_total FROM `st_entered_records` WHERE `record_user_id` = '$this->user_id' AND `record_date` BETWEEN '$this->week_start_date' AND '$this->week_end_date'");
        $this->calc_totals_row = mysql_fetch_assoc($this->calc_totals_result);
        $this->user_week_total = $this->calc_totals_row['user_week_total']; 
        $this->user_week_one_percent = ($this->user_week_total / $this->user_week_diy_goal) * 100;
        $this->user_week_one_percent = number_format( (float)$this->user_week_one_percent, 2, '.', '' );

        return $this->user_week_one_percent;
    }

So what does your PHP 'calculate_progress_percent() actually do? Show that part of your PHP code, and it might be fairly easy to accomplish the same in MySQL — Mark Baker, Jan 17 '13 at 23:52
Is the php functions job simply to take the result set and order it? — Craig Taub, Jan 17 '13 at 23:54
Shame as the above function could be written quite easily using mongo, I am aware this comment is pretty useless (just a fan of mongo). — Craig Taub, Jan 17 '13 at 23:57
@Craig How do you know if it could be written easily if we don't know what "calculate_progress_percent()" does? (re: Mark Baker) — showdev, Jan 17 '13 at 23:58
@MarkBaker That's a good point, but never the less can execute dynamic javascript functions (includng mapreduce) inside a mongo query. Just love any opportunity to point out mongos muscle power. :) — Craig Taub, Jan 17 '13 at 23:59
You **can** order by expressions that contain functions in SQL. Just not PHP functions of course but I'm not aware of any restrictions for SQL functions. — dualed, Jan 18 '13 at 00:02
just a quick guess: you probably will have to do some array juggling -> first get all your entries FROM st_users into a first array (mysql_query) –– then you could run through that array, and for each entry you do the calculate_progress_percent() and build up a second array in which you could add the additional info ("user_progress_percent"). After this you can sort the new array ba your new info ("user_progress_percent"). — tillinberlin, Jan 18 '13 at 00:04
Basically it's taking a specific user, two-week period, and the specific week in that period and calculating how close their sales were to their goals (returning their % to goal, from 0 to 100+). I'm creating a leader board of sales for a specific week, and need to order it by their percentages from highest to lowest. — David W., Jan 18 '13 at 00:05
Please, don't use mysql_* functions for new code. They are no longer maintained and are [deprecated as of PHP 5.5.0](http://php.net/manual/en/intro.mysql.php). See the [red box](http://goo.gl/GPmFd)? Instead learn about [prepared statements](http://goo.gl/vn8zQ) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). If you can't decide, [this article](http://goo.gl/3gqF9) will help you to choose. If you care to learn, here is good [PDO tutorial](http://goo.gl/vFWnC). — peterm, Jan 18 '13 at 00:10

score 0 · Accepted Answer · edited May 23 '17 at 12:27

You probably will have to do some array juggling. First get all your entries FROM st_users into a first array (mysql_query) Then you could run through that array, and for each entry you do the calculate_progress_percent() and build up a second array in which you could add the additional info ("user_progress_percent"). After this you can sort the new array ba your new info ("user_progress_percent").

And here is some quick and dirty code-suggestions – code is however not tested… of course…:)

First:

$mysql_query = "SELECT * FROM st_users 
WHERE `user_comp_supervisor_id`='$team_supervisor' AND 
      `user_exempt_from_goals` = '0'";

Then something like this:

$i = 0;
while($tmp = mysql_fetch_array($mysql_query)) {
    $my_second_array[$i]['user_id'] = $tmp['user_id'];
    $user_id = $my_second_array[$i]['user_id'];
    diy_calc_progress_percent($user_id,$period_id,$period_week_number);
    $my_second_array[$i]['user_result'] = $diy_calc_progress_percent_result;
    $i++;
}

And then sorting that second array should be possible as described here:

Sort Multi-dimensional Array by Value

…hope this helps at some point…

MySQL sorting with PHP

1 Answers1