Solved

PHP/MySQL query checking for more precise LIKE searches

Posted on 2014-10-27
4
162 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
Comment Utility
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
Comment Utility
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
Comment Utility
SELECT *
FROM `Group` 
WHERE FIND_IN_SET ($memberquery,Members)

Open in new window

0
 
LVL 6

Author Comment

by:kiwistag
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This article discusses four methods for overlaying images in a container on a web page
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now