homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

Find exact match in imploded array in VARCHAR field

5+ Year Member

Msg#: 4052238 posted 6:53 pm on Dec 31, 2009 (gmt 0)

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?




WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 10+ Year Member

Msg#: 4052238 posted 8:51 pm on Dec 31, 2009 (gmt 0)

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?

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved