MySql & PHP: How to find which row has an item in the leading spot in a CSV list?

Question

This is kind of a weird question so my title is just as weird.

This is a voting app so I have a table called ballots that has a two important fields: username and ballot. The field ballot is a VARCHAR but it basically stores a list of 25 ids (numbers from 1-200) as CSVs. For example it might be:

22,12,1,3,4,5,6,7,...

And another one might have

3,4,12,1,4,5,...

And so on.

So given an id (let's say 12) I want to find which row (or username) has that id in the leading spot. So in our example above it would be the first user because he has 12 in the second position whereas the second user has it in the third position. It's possible that multiple people may have 12 in the leading position (say if user #3 and #4 have it in spot #1) and it's possible that no one may have ranked 12.

I also need to do the reverse (who has it in the worst spot) but I figure if I can figure out one problem the rest is easy.

I would like to do this using a minimal number of queries and statements but for the life of me I cannot see how.

The simplest solution I thought of is to traverse all of the users in the table and keep track of who has an id in the leading spot. This will work fine for me now but the number of users can potentially increase exponentially.

The other idea I had was to do a query like this:

select `username` from `ballots` where `ballot` like '12,%'

and if that returns results I'm done because position 1 is the leading spot. But if that returned 0 results I'd do:

select `username` from `ballots` where `ballot` like '*,12,%'

where * is a wildcard character that will match one number and one number only (unlike the %). But I don't know if this can actually be done.

Anyway does anyone have any suggestions on how best to do this?

Thanks

Answer 1

I'm not sure I understood correctly what you want to do - to get a list of users who have a given number in the 'ballot' field ordered by its position in that field?

If so, you should be able to use MySQL FIND_IN_SET() function:

SELECT username, FIND_IN_SET(12, ballot) as position 
  FROM ballots
 WHERE FIND_IN_SET(12, ballot) > 0
 ORDER BY position

This will return all rows that have your number (e.g. 12) somewhere in ballot sorted by position you can apply LIMIT to reduce the number of rows returned.