Filtering records based on string array PHP and MySQL

Question

I have a string array in PHP called groups. The array looks something like this based on user input: groups[All, Sales].

Example: (UPDATED TABLE SCHEMA)

groups[] = ['Sales', 'All']

            ANNOUNCEMENT
 |description|masterID|  groupName   |
=======================================
 | hello     | 1      | All, Final,  |
 | greetings | 2      | Sales, All,  |
 | demo      | 3      | Final,       |

So from above table it should only return "hello" and "greetings" as output because groups[] has Sales and All as row 1 has All and row 2 has both. Please help. I am an amateur in SQL and PHP both.

My current try:

SELECT * FROM announcement WHERE groupName REGEXP '(Sales | All)'

Output:

No rows affected

NEVER store comma separated values in database. To retrieve them you'll need a full text search through LIKE "%Final%". — sashkello
– sashkello, Commented May 4, 2013 at 12:47
I would suggest to normalize your database. Create a new table with group names and have another table with masterID and groupId. That would make your life a lot easier going forward as well. — Sabeen Malik
– Sabeen Malik, Commented May 4, 2013 at 12:48
Thanks for all your suggestions, but this is what I have to work with. Can't change it otherwise I would have easily solved this issue. Can you show me a way to work with this? — Rishi Jasapara
– Rishi Jasapara, Commented May 4, 2013 at 12:51

sashkello · Accepted Answer · 2013-05-05 22:56:21Z

3

I guess, your groups should really be in string format: groups[] = ['Final', 'All', 'Test'].
This $search = implode('|', $groups) will produce a string Final|All|Test.
Now you need to put this string into your query to search by either or the words. So, this is WHERE groupName REGEXP '({$search})'. Don't forget about quotes inside the query and so that they don't clash with quotes you put your whole query in.

edited May 5, 2013 at 22:56

answered May 5, 2013 at 3:00

sashkello

18k25 gold badges84 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rishi Jasapara Over a year ago

I changed my table schema to make it: |description|masterID|groupName|. Please check my updated question above.

Rishi Jasapara Over a year ago

The only error was REGEXP '($search)'. It should have been a rounded bracket. It works now :)

LHMathies · Accepted Answer · 2013-05-04 13:00:46Z

1

MySQL does Perl-like regular expressions. Construct a query similar to this:

SELECT description
  FROM announcement NATURAL JOIN master
 WHERE groupName REGEXP '\\b(Final|All|Test)\\b';

answered May 4, 2013 at 13:00

LHMathies

2,46418 silver badges21 bronze badges

3 Comments

Rishi Jasapara Over a year ago

But how do I get each group array element in between REGEXP?

LHMathies Over a year ago

I left that as an exercise -- but something like $query = 'SELECT ... REGEXP \'\\\\b(' . implode('|', $groups) . ')\\\\b\'' would work.

Rishi Jasapara Over a year ago

I am pretty amateur. Although I am searching it on google on how to do it, if you could just get me through, I would be grateful to you.

Collectives™ on Stack Overflow

Filtering records based on string array PHP and MySQL

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related