homepage Welcome to WebmasterWorld Guest from 54.204.59.230
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Find exact match in imploded array in VARCHAR field
radiator251




msg:4052240
 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?

Thanks.

 

rocknbil




msg:4052324
 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