CODEWITHSUNDEEP
phpmysqlsorting
brandon-estrella-dev
brandon-estrella-dev

Reputation: 397

select Mysql data from multiple rows, arithmetic, sort

I am pulling data from a mysql DB. I would like to add the length of multiple runs and sort them by the rank of the person that has run the furthest.

    function determineLength($db, $name){
      $getLength = "select length from $db where name = $name and sex = 'male'";
      $gettingLength = mysql_query($getLength);
      while($gotLenth = mysql_fetch_array($gettingLength)){

      more code that calculates total length and returns it as $lengthAccumulator 
      }
      return array('totalLength'=>$lengthAccumulator);
    }

Now, I have 30 different males whose names will never change, that I need to pull and sort. How should I go about running the mysql code for each person without redundancy? I can only figure out this way - 

    $name = john;
    $a = determineLength($db, $name);
    $aLength = $a['totalLength'];

    $name = sam;
    $b = determineLength($db, $name);
    $bLength = $b['totalLength'];

    $name = henry;
    $c = determineLength($db, $name);
    $cLength = $c['totalLength'];

    $name = ted;
    $d = determineLength($db, $name);
    $dLength = $d['totalLength'];

then store the $aLength, $bLength, $cLength...into an array and sort them out that way. it just seems like the WRONG way to do it, not to mention redundant and slow. there are over 40k rows of data in the DB, so attempting to do it this way would run through the DB over 1.2 Mil times?! Can I pass an array of the names into the function, then utilize the ORDER BY length DESC in the mysql code?

I'm in need of help. Thank You!

    ****the answer below by zane seemed to work to order the runners, but

how would I echo the actual rank using this method? I have substituted his select statement into the one I had above, but how would I go about echoing a persons rank? Could I save the results into an array then echo the key?

Upvotes: 0

Views: 443

Answers (1)

Zane Bien
Zane Bien

Reputation: 23125

If I understand your situation correctly, you can simply do this in one single SQL statement:

SELECT   name
FROM     $db
GROUP BY name
ORDER BY SUM(length) DESC

And everything is already sorted straight from the result-set. No procedural code.

The above gets all runners, but if you wanted to get a sorted set of specific male runners, you can add a WHERE clause like so:

SELECT   name
FROM     $db
WHERE    name IN ('name1', 'name2', 'name3', 'etc...') AND 
         sex = 'male'
GROUP BY name
ORDER BY SUM(length) DESC

To rank in the actual SQL, you can do:

SELECT     name, @rank:=@rank+1 AS rank
FROM       $db
CROSS JOIN (SELECT @rank:=0) val_init
WHERE      name IN ('name1', 'name2', 'name3', 'etc...') AND 
           sex = 'male'
GROUP BY   name
ORDER BY   SUM(length) DESC

Then the reference the rank column in php for the person's rank.

Upvotes: 6

Related Questions