PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Tue Jul 23, 2019 8:16 pm

All times are UTC - 5 hours




Post new topic Reply to topic  [ 7 posts ] 
Author Message
PostPosted: Mon Jan 09, 2012 7:31 am 
Offline
Forum Newbie

Joined: Mon Jan 09, 2012 7:16 am
Posts: 1
Im hoping im asking this question in the right place.

I have a mysql table with a column admin.

An example of contents in Row1 could be "jbloggs, fbloggs"
An example of contents in Row 2 could be "jbloggs1"

What i want to do is display Rows based on a username.

The issue is if i do a search LIKE "%jbloggs%" its going to display both Row1 and Row2

Is there a way using Regex that i can match the exact username?

I was thinking i could get the length of the string then match the username and length but not sure how id go about doing that.

Apologies if the question is a bit unclear!


Top
 Profile  
 
PostPosted: Mon Jan 09, 2012 2:31 pm 
Offline
Forum Commoner
User avatar

Joined: Thu Dec 15, 2011 2:40 pm
Posts: 85
Location: Nelson, NZ
Hi jhall,

Not sure I fully understand your question, and I'm not a mySQL regex expert.
MySQL regex syntax is quite poor compared to PHP's PCRE. Nevertheless, it works.
To give you an example, this works for me in sqlYog:
Syntax: [ Download ] [ Hide ]
SELECT * FROM movies WHERE url REGEXP "[0-9]"

This selects movies where the url field contains a digit.
(Note that in mySQL regex, \d doesn't work, therefore using a [0-9] class. Could have used [[:digit:]] instead.)

So for you, something like:
Syntax: [ Download ] [ Hide ]
SELECT * FROM YourTable WHERE Row2 REGEXP "jbloggs[0-9]"

should get you on the right track (selects records where row2 is jbloggs1, jbloggs2 etc).
If you give me more details of what you are trying to accomplish, I am happy to have a look for you. :)


Top
 Profile  
 
PostPosted: Mon Jan 09, 2012 7:53 pm 
Offline
Forum Newbie

Joined: Fri Jan 06, 2012 2:43 am
Posts: 9
If I understand correctly, the names are comma-separated in the "admin" column. Try this:

Syntax: [ Download ] [ Hide ]
SELECT * FROM `users` WHERE `admin` REGEXP '[[:<:]]jbloggs[[:>:]]'

OR

SELECT * FROM `users` WHERE `admin` REGEXP '(^|, )jbloggs($|, )'
 


The first regex uses word boundaries, the second one uses "beginning of the line or comma", "end of the line or comma". You can read about the syntax in


Top
 Profile  
 
PostPosted: Tue Jan 10, 2012 1:54 am 
Offline
Forum Regular
User avatar

Joined: Tue Sep 28, 2010 11:41 am
Posts: 984
Location: Columbus, Ohio
Hey, I like the second one, never thought of using ^ and $ in an OR situation!

Can you explain the first expression? I'm not understanding it, even after throwing it into RegexBuddy and trying both of the POSSIX settings. (Love that program!)

One thing I would suggest for the second one, not being able to see the actual data, in case there are not spaces, do (^|, ?) and ($|, ?) to say the space is optional.

-Greg


Top
 Profile  
 
PostPosted: Tue Jan 10, 2012 3:14 am 
Offline
Forum Commoner
User avatar

Joined: Thu Dec 15, 2011 2:40 pm
Posts: 85
Location: Nelson, NZ


Top
 Profile  
 
PostPosted: Tue Jan 10, 2012 7:34 am 
Offline
Forum Newbie

Joined: Fri Jan 06, 2012 2:43 am
Posts: 9
Hi, Twinedev,

as Playful already said, [[:<:]] and [[:>:]] are beginning of a word and end of a word (\< \> or \b in other flavors).

Optional spaces are great idea, thank you!


Top
 Profile  
 
PostPosted: Tue Jan 10, 2012 10:06 am 
Offline
Forum Regular
User avatar

Joined: Tue Sep 28, 2010 11:41 am
Posts: 984
Location: Columbus, Ohio
Yep, after I saw Playful's post, I went back to the link again, just didn't get it read all the way to the bottom I guess (or got interrupted in the middle when I was checking it)

-Greg


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 7 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 3 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group