Sorting MYSQL results using values from an array?

[galvin]

Say I have a table called artists with these fields (artistid, rank).

 

Then say I have an array called $rankadjustments with a value for each artist (like 1, 7, 3,-3, 5, 9, etc).

I am doing a MYSQL query that gets the info from artists table and sorts according to the rank field like this...

$sql = "SELECT * FROM artists ORDER BY rank";

Easy enough. But what if I would like to bring that data back sorted by (rank + rankadjustment). For example, if the artist ranked 1st had a rank of "1" from mysql, but had a rankadjustment of "5" from the rankadjustment array, his "true" rank would be "6".

 

Again, rankadjustment is NOT a field in my table, otherwise it would obviously be simple. It's calculated on the fly and stored in an array.  FYI, the array has an index with their "artistid". For example, $rankadjustment[341][8] would be the artist with an artistid of "341" has a rank adjustment of 8. 

 

Is there a way to do this IN the query itself? It doesn't seem possible but wanted to find out for sure. If not, what is best way to do? I assume... Get the data sorted JUST by rank and put it all into an array, then create a new array that adds rank to rankadjustment and reorder by the "new" rank amount?

 

I guess that wouldn't be too bad but again, wanted to make sure I'm not missing something that would allow me to do it all in the query (or just more efficiently).

 

Thanks!

Edited December 22, 2014 by galvin

[mac_gyver]

a) how big is this array of adjustments?

 

b) is there a value in the array for every artist in the table or will there be some artists that don't have values in the adjustment array?

 

c) i'm guessing the purpose of this is to produce a new rank that you will then want to replace the existing rank in the table? if so, telling us the final goal would let someone suggest a direct solution, rather than to sneak up on the problem one step at a time.

 

just based on your description, create a temporary database table, insert the data id/adjustment, then join the temporary table with the existing database table using the id's, sum the rank and adjustment values in the now joined rows and use that in the order by term.

[galvin]

Thanks for reply....

 

a.  There are around 900 artists

b.  Every artist will have a value, which can be 0 (i.e. zero would not adjust the original rank at all)

c.  Not exactly. I don't put the "adjusted" ranks into a table or replace anything.  Essentially, I set the original rank each week which does get stored in a database. Each week, adjusted ranks are calculated on the fly and sometimes used, sometimes not.  There's no real reason to keep the adjusted ranks in a table since they change quite a bit, and from week to week.

 

It's hard to explain so let me use an easier to digest similar example since I'm also working on fantasy sports site.  A fantasy football site might rank players each week.  But sometimes scoring is based on PPR (points per reception) so rankings for PPR might be a little different than standard scoring.  And most commonly, PPR adjustments would work off of year-to-date statistics, specifically "number of receptions".   So each week, the rankings might load based on standard scoring and then if the user clicks "PPR Scoring", the page would look at all reception statistics and create an adjustment to the rankings based off of those stats. So each player would have adjustment number.  Then when displaying the rankings again using PPR scoring, the code would re-rank according to the "ppr adjustment".

 

That sounded good in my head, but I may have just confused things ever more. If I have, I apologize.  Hopefully it kind of makes sense.

 

If anything, I'd love to hear more about creating a TEMPORARY table.  That thought never crossed my mind.  Is this common?  If I ever ending up getting a lot of traffic accessing the artists ranking page, could it be bad to have a table created and then subsequently (i assume) deleted every single time the page is accessed?

[kicken]

Unless the ranking will be changing per-request it'd probably be best just to store them into a table and join with it during the query. You could have a background task update the adjustments periodically and store them rather than calculate them on the fly on each request.

 

If they do need to be calculated per-request then creating a temporary table and joining to it would probably be fine. Creating a temporary table is pretty much the same as creating a normal table, except you stick the word TEMPORARY in the query. See the CREATE TABLE manual page for details about temporary tables. Specify ENGINE=MEMORY when creating the table for the best performance.

 

As far as performance goes, I'd look at how your adjustment calculations affect things before worrying about the temporary table and joining to it.

[galvin]

Thank you both for the suggestions. The adjustments change weekly, but not every request or anything, so I will definitely look into storing them in a table since that seems to be the best route. I will also read up on TEMPORARY tables whether I use them or not.  Thanks again!