Solved

mysql query

Posted on 2013-06-01
12
258 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 110

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
 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

636 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