Solved

PHP/MySQL query checking for more precise LIKE searches

Posted on 2014-10-27
4
195 Views
Last Modified: 2014-11-11
I have a database I'm trying to cross reference for duplicates.

The members table has a list of entries where it's stored with a Members row with each ID comma separated.

So it could be Members: 271,612,614,6600,1102

If I do a duplicates report it is set to take each record and break it up searching on each entry, however since I have the query doing a search such as
SELECT * FROM `Group` WHERE `Members` LIKE '%$memberquery%'

Open in new window

of course if Member ID 660 is looked up it'll also see Member 6600.
Could there be an easier way to Query it or should I do something like a strlen($meberquery) to then discard if it's irrelevant?
0
Comment
Question by:kiwistag
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 58

Assisted Solution

by:Gary
Gary earned 425 total points
ID: 40406770
What does $memberquery consist of?

You would better using IN rather than LIKE

... IN (271,612,614,6600,1102)
0
 
LVL 9

Assisted Solution

by:Brian Tao
Brian Tao earned 75 total points
ID: 40406786
Well, the table structure will eventually cause you performance issue.
if you can redesign your tables to a group table with a group ID and then another table, e.g. group_member (a 1-to-n relation), then you'd be able to run a query against the group_member table using something like
SELECT * FROM group_member WHERE member='$memberquery'

Open in new window

If you cannot redesign/rebuild your table and can live with the performance, you can reformulate the query like the following:
SELECT * FROM Group WHERE CONCAT(',', Members, ',') LIKE '%,$memberquery,%'

Open in new window

0
 
LVL 58

Accepted Solution

by:
Gary earned 425 total points
ID: 40406859
SELECT *
FROM `Group` 
WHERE FIND_IN_SET ($memberquery,Members)

Open in new window

0
 
LVL 6

Author Comment

by:kiwistag
ID: 40436547
taoyipai:
The group has it's own GroupID Column (which is a Primary Key).
The Table consists of: GroupID, GroupLeader, Members, and some other info.

Gary: Thanks for that - I'll give it a shot (the IN and the Find_In_Set).

I'll close and accept the answers as the project has wound down for the year now and will be approached later next year.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

These days socially coordinated efforts have turned into a critical requirement for enterprises.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question