Solved

mysql query

Posted on 2013-06-01
12
252 Views
Last Modified: 2013-06-14
I have a whole heap of spam in a table that I'm trying to get rid of.

What makes the posts the same is the following text:

[url=%url%][/url]

Open in new window


However when I search for it with the below query I get 0 results back which I assume is because of those % wildcard characters. How can I get around this to show all comments with the above text within them? Please ignore the /url tag because EE is asking me to put that in.

SELECT *
FROM FeaturesComments
WHERE COMMENT LIKE  ''
and ApprovalStatus = 0
0
Comment
Question by:PeterErhard
12 Comments
 
LVL 25

Expert Comment

by:clockwatcher
ID: 39213640
\% should escape the % wildcard.  But if you're not getting any results with the wildcard in there, I can't see how escaping it would make something show up.  Escaping the wildcard is going to make it even more restrictive, so if nothing showed up before, I can't imagine you'd get anything with it escaped.  Ignore the spaces before/after the brackets-- needed them to get around the stupid EE parser.


SELECT *
FROM FeaturesComments
WHERE COMMENT LIKE   '[ url=\%url\% ][ /url]'
and ApprovalStatus = 0


But really think there is probably something else going on.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39213786
I'm confused.  Why don't you show us an actual entry?  Put it in the code section.
0
 
LVL 5

Expert Comment

by:DOSLover
ID: 39213845
To escape the % sign we need \%. In addition, we need a wild card % to use the like statement.
For example, in your search string, we need to escape the embedded % sign and then use actual % for wildcard, please try this

SELECT * 
FROM FeaturesComments
WHERE COMMENT LIKE  '%[url=\%url\%][ /url]%'
and ApprovalStatus = 0

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39215086
Please show us the actual data.  You can find this with phpMyAdmin.  You can copy it and paste it into the code snippet here at EE.  This will help us get you a better answer.
0
 

Author Comment

by:PeterErhard
ID: 39215490
Apologies for the late response.

An attached sample comment is attached.

The below query doesn't find it:

SELECT * 
FROM FeaturesComments
WHERE COMMENT LIKE  '%[url=\%url\%][ /url]%'
and ApprovalStatus = 0

Open in new window

0
 

Author Comment

by:PeterErhard
ID: 39215491
Forgot to attach.
spam.txt
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 25

Expert Comment

by:clockwatcher
ID: 39215503
Try
like '[url=\%url\%]%[/url]'

Open in new window

0
 

Author Comment

by:PeterErhard
ID: 39215504
Thanks clockwatcher, but your query doesn't find it.
0
 
LVL 25

Expert Comment

by:clockwatcher
ID: 39215519
Duh... sorry... forgot it had stuff before it... too early in the morning.]

like '%[url=\%url\%]%[/url]'

Open in new window

0
 

Author Comment

by:PeterErhard
ID: 39215523
That did the trick, thank you very much :)
0
 
LVL 25

Accepted Solution

by:
clockwatcher earned 500 total points
ID: 39215524
And that assumes that it really ends with  
[/url]

Open in new window


Can't really tell from your file.  If not then...

like '%[url=\%url\%]%[/url]%'

Open in new window

0
 

Author Closing Comment

by:PeterErhard
ID: 39249501
Apologies for the late accept.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

929 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

13 Experts available now in Live!

Get 1:1 Help Now