Solved

PHP/MySQL query checking for more precise LIKE searches

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
count download link and run update query 9 55
Problem with Simple PHP/mySQL Query 3 51
Download a website to hdd 2 49
Amazon Redshift 2 26
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
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…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

910 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

20 Experts available now in Live!

Get 1:1 Help Now