Deleting rows from mysql db

[nadz]

Basically ive got a list of 18000 username and email addresses in a txt file in the following form:

 

[username] :

[username] :

[username] :

[username] :

etc etc etc

 

The same email addresses and usernames are stored in the mysql db on my server. what im trying to do is delete all the rows in my database that match any email addresses in the txt file.

 

for example if there is a line in the text file that says

 

username : [email protected]

 

i want it to delete the mysql row that has "[email protected]" in the email field.

 

any help would be appreciated.

 

[chigley]

<?php

$lines = file("yourtextfile.txt");

foreach($lines as $content) {
list($username, $email) = explode(":", $content);
$query = mysql_query("DELETE FROM table WHERE email = '{$email}'");
if($query) {
  echo "Deleted email address: {$email}";
} else {
  $error = mysql_error();
  echo "Error deleting email address: {$email} - {$error}";
}

?>

[nadz]

thanks ill test that now

[nadz]

My first post was a lil dodgy, this is what it should look like.

 

Basically ive got a list of 18000 username and email addresses in a txt file in the following form:

 

[username] : [email]
[username] : [email]
[username] : [email]
[username] : [email]

 

etc etc etc

The same email addresses and usernames are stored in the mysql db on my server. what im trying to do is delete all the rows in my database that match any email addresses in the txt file.for example if there is a line in the text file that says

 

myusername : [email protected]

 

i want it to delete the mysql row that has "[email protected]" in the email field.

 

any help would be appreciated.

[chocopi]

Thats what Chigley has posted

 

The only thing you might have to change is

 

list($username, $email) = explode(":", $content)

 

to

 

list($username, $email) = explode(" : ", $content)

 

~ Chocopi

[nadz]

hi, thanks for the help chocopi ill just test that now. i was a bit confused  - it was telling me it deleted all the addresses but they were still in the database. thanks again

[nadz]

ok, im having some problems. when i use the script it echos "Deleted Email address: [email protected]" 18000 times but it doesnt actually delete the rows in the database. heres the code im using:

 

<?php

include("config.php");

mysql_connect($db_host, $db_user, $db_pwd);
mysql_select_db($db_name);

$lines = file("sent.txt");

foreach($lines as $content) {
list($username, $email) = explode(" : ", $content);
$query = mysql_query("DELETE FROM $db_table WHERE email = '{$email}'");
if($query) {
  echo "Deleted email address: {$email}<br>";
} else {
  $error = mysql_error();
  echo "Error deleting email address: {$email} - {$error}";
}
}
?>

[chocopi]

replace $db_table with the actual tablename, get rid of the curly braces and add an astrieks, so this line:

 

 $query = mysql_query("DELETE FROM $db_table WHERE email = '{$email}'");

 

looks like

 

 $query = mysql_query("DELETE * FROM tablename WHERE email='$email'");

 

---

 

if not replace

 

 $query = mysql_query("DELETE * FROM tablename WHERE email='$email'");

 

with

 

 $query = "DELETE * FROM tablename WHERE email='$email'";
mysql_query($query) or die(mysql_error());

 

Hope one of those works

 

~ Chocopi

[nadz]

i get this error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM vb1user WHERE email='n*****@googlemail.com '' at line 1

 

and im using this code:

 

<?php

$db_host = "mysql.t***.co.uk";
$db_user = "nextman";
$db_pwd = "*****";
$db_name = "***";

mysql_connect($db_host, $db_user, $db_pwd);
mysql_select_db($db_name);

$lines = file("sent.txt");

foreach($lines as $content) {
list($username, $email) = explode(" : ", $content);

$query = "DELETE * FROM vb1user WHERE email='$email'";
mysql_query($query) or die(mysql_error());

if($query) {
  echo "Deleted email address: {$email}<br>";
}
}
?>