asked on

mysql query

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

clockwatcher

\% 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.

Dave Baldwin

I'm confused. Why don't you show us an actual entry? Put it in the code section.

DOSLover

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

Ray Paseur

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.

PeterErhard

ASKER

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

PeterErhard

ASKER

Forgot to attach.
spam.txt

clockwatcher

Try

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

Open in new window

PeterErhard

ASKER

Thanks clockwatcher, but your query doesn't find it.

clockwatcher

Duh... sorry... forgot it had stuff before it... too early in the morning.]

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

Open in new window

PeterErhard

ASKER

That did the trick, thank you very much :)

ASKER CERTIFIED SOLUTION

clockwatcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PeterErhard

ASKER

Apologies for the late accept.