mass delete entries from mysql database

[deezin]

Hello. I hope someone can help. I have a list of urls separated one on a line in a txt file. I want to delete entries from my mysql database for each entry that url = one of the urls in the txt file. tutorials is the name of the table. So something like

delete from tutorials where url LIKE 'http://fakeurl.com'

 

Any ideas on how to do this one?

 

Thank you for your help.

Rachel

[Guest]

This should be straight forward... I'll take your word for it when you say it's one url a line.

 

Well... the most intuitive idea that comes to mind is simply reading the entire file into an array, use a foreach to construct a large SQL statement, and then commit the statement.

 

I will also assume that this script is to serve an administrative function, NOT a user-end purpose (or to say simply: meaning it won't be run by the website--instead, you will be running it yourself, to accomplish one task seperate from the web app). This way, no need for error handling, graceful recoveries or optimizing.

 

So here's my two cents.

 

<?php

$urlsToDelete = file_get_contents('urls.txt');
$urlArray = explode("\n", $urlsToDelete);
foreach( $urlArray as $line => $url )
{
	$urls .= "'$url'";
	if( $line != count($urlArray) )
	{
		$urls .= ",";
	}
}

$query = "DELETE FROM tblurls WHERE url IN ($urls)";
mysql_query($query, $database);

?>

 

Did it pretty quick, so I'll verify if it works in a bit. Just be sure to replace the table name and column name.

[Guest]

However, that script is very optimistic. It assumes the entries in the txt file with equal EXACTLY the url in the database.

 

If you want it to just look for similarities you can do:

 

<?php

$urlsToDelete = file_get_contents('urls.txt');
$urlArray = explode("\n", $urlsToDelete);
foreach( $urlArray as $line => $url )
{
	$urls .= "'%$url%'";
	if( $line != count($urlArray) )
	{
		$urls .= " OR url=";
	}
}

$query = "DELETE FROM tblurls WHERE url LIKE $urls";
mysql_query($query, $database);

?>

[deezin]

Thank you so much...that worked!!