Welcome to WebmasterWorld Guest from 126.96.36.199
Forum Moderators: open
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?
$id = 13; // This user, correct?
$query = "select * from users where admin regexp '^,*$id,*$'";
^ = 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?