Find exact match in imploded array in VARCHAR field

[radiator251]

I have an array of numbers, which correspond to the ID# for my users table, that is stored in another table to determine which users can edit information for that row. The array is imploded with ",", so if users 13 and 25 are admins for a row, the row's `admins` field will read "13,25".

I'm new to MySQL - how would I make a query that, given a certain ID#, will find if that ID has an exact match within the imploded array in the `admins` field?

From what I've read, I think I can do something like "...WHERE (`admins` = '$id') OR (`admins` LIKE ',$id') OR (`admins` LIKE ',$id,'))". Is this correct?

Thanks.

[rocknbil]

Well, storing commonly accessed data in a delimited list is bereft with problems for a few reasons, the first of which you've discovered. You should really have an admin field marked with an access level, or have these id's in a separate table or something, then look them up by exact match on the integer field. But something like this might work.

$id = 13; // This user, correct?

$query = "select * from users where admin regexp '^,*$id,*$'";

This means:
^ = string absolutely starts with

,* = Zero or more commas. This covers the possibility it's the first number in the list.

$id = the input

,* = zero or more commas, see above, it may be the second or third or last number

$ = absolutely ends with. This should work as is, but if PHP interpolates this as the variable $' somehow, try one of these two:

$query = "select * from users where admin regexp '^,*" . $id . ',*$\'';
// single quoted last part, $ won't interpolate

$query = "select * from users where admin regexp '^,*$id,*\$'";
// $ Escaped for PHP, should be literal for mySQL

Regexp is a better choice because you can't indicate "zero or more" for specific characters in a like. You could probably use your Or . . . but what happens if you add a third admin?

mySQL regexp [dev.mysql.com]

Should you take my advice and abandon this now, restructure your database, it gets a lot easier:

select * from users where admin=1; // default 0 for reg. users

select * from users where access_level > 0; // ditto, more control

select * from users,admins where admins.user_id=users.user_id;
// Two tables, which makes the larger users table narrower,
// the admins table only as deep as it needs to be
// As database grows, why carry half a million records
// with only two records > 0?