Adding an array to a MySQL column in SELECT

Question

I have an array with as set of users and their respective karma:

$some_array = Array
(
    [user_id] => Array
        (
            [0] => 4
            [1] => 3
            [2] => 5
            [3] => 1
        )

    [karma] => Array
        (
            [0] => 129
            [1] => 87
            [2] => 13
            [3] => 20
        )

)

I was wondering if, when you are retrieving the user data from a MySQL table, you can add an extra column with the karma array and order by that extra_column:

 $query = 'SELECT user.*, {{$some_array['karma'] as extra_column}} WHERE
 user.id IN '.(implode(',',$some_array['user_id'])).' ORDER BY extra_column';

Any ideas? Is it possible?

Answer 1

Not really (at least not without going through some really nasty dynamic sql generation).

You would have to have the karma in a column on the user table (or some other table that you could join to the user table). It seems like that should be stored in the database anyways though right?

Answer 2

SELECT  id, ELT(FIELD(id, 4, 3, 5, 1), 129, 87, 13, 20) AS karma
FROM    (
        SELECT  4 AS id
        UNION ALL
        SELECT  3 AS id
        UNION ALL
        SELECT  5 AS id
        UNION ALL
        SELECT  1 AS id
        ) q
WHERE   id IN (4, 3, 5, 1)
ORDER BY
        karma

Answer 3

If the values in the array have to be provided by the script (i.e., don't exist in the database in any way you could join them, which is the ideal case), then I see two possibilities:

1. Create a temporary table for your karma, insert all karma values, and then join your query with your karma array
2. Use a case statement in your query. This is an untenable solution if the number of users is large.

The code for generating the case statement could look something like this:

$query = 'SELECT *, ' . GenerateCaseStatement($some_array) . ' AS extra_column FROM user WHERE user.id in (' . implode(',', $some_array['user_id']) . ' ORDER BY extra_column';

function GenerateCaseStatement($some_array)
{
    $str = 'CASE id ';
    for (i=0; i<array_len($some_array['user_id']); ++i)
    {
        $str .= ' WHEN ' . (int)($some_array['user_id'][i]) . ' THEN ' . (int)($some_array['karma'][i]);
    }
    $str .= ' ELSE NULL END';
    return $str;
}