Solved

mysql query

Posted on 2013-06-01
12
250 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 82

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
This video discusses moving either the default database or any database to a new volume.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

746 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

15 Experts available now in Live!

Get 1:1 Help Now