Solved

PHP/MySQL query checking for more precise LIKE searches

Posted on 2014-10-27
4
174 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
  • 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

770 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