homepage Welcome to WebmasterWorld Guest from 54.196.18.51
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
using multiple SELECT * FROM WHERE url LIKE
john1000




msg:3941616
 3:18 pm on Jun 27, 2009 (gmt 0)

hello,

im cleaning up a database with links and because there are so many wrong ones i need to speed up my deleting process.

current im using like:
SELECT * FROM my_links WHERE url LIKE '%bla.com%'

but there are more urls to check....
like whatever.com , bla.com, etc....
all nlinks that contain private pages that i want out..

i tried :

SELECT * FROM my_links WHERE url LIKE '%bla.com%' AND '%bla2.com%' AND '%bla3.com%' AND '%bla4.com%' AND '%bla5.com%'

the list goes on..
i also used OR instead of AND..
a real sample of what i have is this:

SELECT * FROM my_links WHERE url LIKE '%chello%'
SELECT * FROM my_links WHERE url LIKE '%tiscali%'
SELECT * FROM my_links WHERE url LIKE '%lycos%'
SELECT * FROM my_links WHERE url LIKE '%versatel%'
SELECT * FROM my_links WHERE url LIKE '%web-log%'

meaning that i want to delete all urls that contain the above..
but how do i do it all together..?

 

janharders




msg:3941623
 3:56 pm on Jun 27, 2009 (gmt 0)

SELECT * FROM my_links WHERE url LIKE '%bla.com%' OR url LIKE '%bla2.com%' OR url LIKE ...

each expression has to be valid, you cannot run multiple values on "url LIKE " without a function or stored procedure (such as .. WHERE id IN(1, 2, 3), but to my knowledge, there's no such thing for LIKE)

rocknbil




msg:3941632
 4:02 pm on Jun 27, 2009 (gmt 0)

i also used OR instead of AND..

Or should have worked, did it not (with the changes J.H. noted?)

The only way I know to do this is script it, in the language of your choice. PHP example,

var $list = Array ('url1','url2','url3'); // etc
// You could put these in a text file or
// database field and read them in to
// populate the array

var $where='';


foreach ($list as $url) {
if ($where != '') { $where .= ' or'; }
$where .= " like '%$url%'";
}
if ($where != '') {
$select = "delete from table where $where";
// execute
}

echo your output before coding in the execute, just in case.

john1000




msg:3941774
 11:35 pm on Jun 27, 2009 (gmt 0)

thanks guys,...
janharders...the or url like worked...
so that did exactly what i wanted..

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved