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?

Do you want that extra column to be only in your result of the SELECT? The values in the array are not in the database? — True Soft
– True Soft, Commented Dec 1, 2009 at 17:50
I would have to agree with Eric. Maybe there is another solution where you can use a table to store some data or do some sort of calculation after the sql results are returned. Are you trying to associate the array with the result, or individual array items to row numbers? — Jonathan Reyes
– Jonathan Reyes, Commented Dec 1, 2009 at 17:57
actually, just the individual array to the row numbers... and @True Soft, these karma calculations change according to the user who is viewing them and when he is viewing them, thats why I don't see why I should store it. — Samin
– Samin, Commented Dec 3, 2009 at 11:13

Quassnoi · Accepted Answer · 2009-12-01 17:59:20Z

3

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

answered Dec 1, 2009 at 17:59

Quassnoi

428k94 gold badges628 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dathan Over a year ago

I didn't know these functions existed. That's a much cleaner solution than my case statement.

Samin Over a year ago

it worked! but somehow after I removed the (SELECT... UNION ALL) stuff... before it was showing all the users in the user_table.

Samin Over a year ago

oh, and I wanted to thank everyone for the interest... this website really rocks!

Eric Petroelje · Accepted Answer · 2009-12-01 17:52:29Z

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?

answered Dec 1, 2009 at 17:52

Eric Petroelje

60.7k9 gold badges133 silver badges178 bronze badges

Comments

Dathan · Accepted Answer · 2009-12-01 18:00:17Z

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:

Create a temporary table for your karma, insert all karma values, and then join your query with your karma array
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;
}

Collectives™ on Stack Overflow

Adding an array to a MySQL column in SELECT

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related