Multi Delete from MySQL using PHP help please.

[brailleschool]

Hi:

I have one database with four tables in it. I want to delete from all four tables at the same time. I have tried many things and nothing is working right now. Please advise.

This is what I am trying to do (in one query)

$sql = "DELETE FROM logintest WHERE username = '$username";
$sql = "DELETE * FROM contactbooktest WHERE username = '$username'";
$sql = "DELETE * FROM contactbooktest WHERE contactuser = '$username'";
$sql = "DELETE * FROM sentusermessagestest WHERE frommbr = '$username'";
$sql = "DELETE * FROM sentusermessagestest WHERE tombr = '$username'";
$sql = "DELETE * FROM usermessagestest WHERE frommbr = '$username'";
$sql = "DELETE * FROM usermessagestest WHERE tombr = '$username'";

with this:

$connection = mysql_connect("$server", "$db_user", "$db_pass") or die(mysql_error());
$db = mysql_select_db("$database", $connection) or die(mysql_error());
$sql = "delete * from logintest, contactbooktest, sentusermessagestest, usermessagestest where logintest.username = '$username' and contactbooktest.username = '$username' and contactbooktest.contactuser = '$username' and sentusermessagestest.frommbr = '$username' and sentusermessagestest.tombr = '$username' and usermessagestest.frommbr = '$username' and usermessagestest.tombr = '$username'";
$result = mysql_query($sql, $connection);
echo "$username and its associated account information and messages have been permanently removed from the server.";

I get an error message as follows:
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 'where logintest.username = 'admin' and contactbooktest.username

When I echo $sql, I get the following:
delete from logintest, contactbooktest, sentusermessagestest, usermessagestest where logintest.username = 'admin' and contactbooktest.username = 'admin' and contactbooktest.contactuser = 'admin' and sentusermessagestest.frommbr = 'admin' and sentusermessagestest.tombr = 'admin' and usermessagestest.frommbr = 'admin' and usermessagestest.tombr = 'admin'

This is the complete code:

<?
$server = "localhost";
$db_user = "username";
$db_pass = "password";
$database = "database";

$connection = mysql_connect("$server", "$db_user", "$db_pass") or die(mysql_error());
$db = mysql_select_db("$database", $connection) or die(mysql_error());

$sql = "SELECT * FROM login WHERE username='$username' AND password='$password' AND password2='$password' AND emaila='$emaila'";
$result = mysql_query($sql, $connection);

if($username=='' OR $password=='' OR $password2=='' OR $emaila=='')
{
echo "All fields are required for security reasons.";
}
elseif($password != $password2)
{
echo "Passwords do not match.";
}
elseif (!eregi("^([a-z0-9]+)([._-]([a-z0-9]+))*[@]([a-z0-9]+)([._-]([a-z0-9]+))*[.]([a-z0-9]){2}([a-z0-9])?$", $emaila)) 
{
echo "Email address does not seem to be formatted correctly.  Please rectify.";
}
elseif (!eregi("^[a-z0-9_]+$", $username)) 
{
echo "There are illegal characters in the username field.  I cannot proceed.";
}
elseif (strlen($password) < 4) 
{
echo "Braille School passwords are only a minimum of 4 characters.";
}
elseif (strlen($password) > 20) 
{
echo "Braille School passwords are only a maximum of 20 characters long.";
}
elseif (strlen($username) > 15) 
{
echo "Braille School usernames are only a maximum of 15 characters long.";
}
elseif (strlen($username) < 5) 
{
echo "Braille School usernames are only a minimum of 6 characters long.";
}

//start deleting from tables
$connection = mysql_connect("$server", "$db_user", "$db_pass") or die(mysql_error());
$db = mysql_select_db("$database", $connection) or die(mysql_error());

$sql = "delete * from logintest, contactbooktest, sentusermessagestest, usermessagestest where logintest.username = '$username' and contactbooktest.username = '$username' and contactbooktest.contactuser = '$username' and sentusermessagestest.frommbr = '$username' and sentusermessagestest.tombr = '$username' and usermessagestest.frommbr = '$username' and usermessagestest.tombr = '$username'";

$result = mysql_query($sql, $connection) or die(mysql_error());

echo "$username and its associated account information and messages have been permanently removed from the server.";

mail("[e-mail]", "$username Terminated Account","Dear Paddy,\n$username used the Braille School form to terminate their account today.  All settings were removed (login information, profile information, address book, as well as, inbox and sent messages contents.)\nCordially,
Blindness Related Learning.", "From: Braille School Accounts <[e-mail]>");
?>

[dewen]

delete * from

or

delete from ?

[brailleschool]

Originally posted by dewen
delete * from

or

delete from ?

delete all records that have the value of the username in the specified table columns. I have a form that uses the above code with four input fields.

username, password, password2, emaila

[kburger]

change 'delete * from' to just 'delete from'

[brailleschool]

Originally posted by kburger
change 'delete * from' to just 'delete from'

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 'where logintest.username = 'admin' and contactbooktest.username

[kburger]

try this:

$sql = "delete 
logintest.*, 
contactbooktest.*, 
sentusermessagestest.*, 
usermessagestest.* 
from logintest, contactbooktest, sentusermessagestest, usermessagestest
where 
logintest.username = '$username' 
and contactbooktest.username = '$username' 
and contactbooktest.contactuser = '$username' 
and sentusermessagestest.frommbr = '$username' 
and sentusermessagestest.tombr = '$username' 
and usermessagestest.frommbr = '$username' 
and usermessagestest.tombr = '$username'";

[brailleschool]

Originally posted by kburger
try this:

$sql = "delete 
logintest.*, 
contactbooktest.*, 
sentusermessagestest.*, 
usermessagestest.* 
from logintest, contactbooktest, sentusermessagestest, usermessagestest
where 
logintest.username = '$username' 
and contactbooktest.username = '$username' 
and contactbooktest.contactuser = '$username' 
and sentusermessagestest.frommbr = '$username' 
and sentusermessagestest.tombr = '$username' 
and usermessagestest.frommbr = '$username' 
and usermessagestest.tombr = '$username'";

[/B]

Gives no errors but does not perform any deletion in any table.

[kburger]

Then it sounds like the syntax is now correct, but your logic is flawed.

[brailleschool]

Originally posted by kburger
Then it sounds like the syntax is now correct, but your logic is flawed.

all the table columns have one thing in common. that being the username. the script of course deletes by the username so it should work. all tables are in the same database. have no idea why it isnt working. i am sure that the joining is correct but not sure why it is not doing what it is supposed to do.

[brailleschool]

I have gone back to single queries because it is a lot easier and it works. However, I have a problem.

I want to match the inputted information with the records in the database to make sure they are all correct before you are allowed to go ahead with a delete.

Doesn't seem to be working and cannot find any reference on how to do this.

Please advise.

The part I am having problems with I believe is:

elseif($username != $ROWusername)
{
echo "Username does not match any records in the member database.";
}
elseif($password != $ROWpassword)
{
echo "Password does not match any records in the member database.";
}
elseif($password2 != $ROWpassword2)
{
echo "Confirmed password does not match any records in the member database.";
}
elseif($emaila != $ROWemailaddress)
{
echo "Email address does not match any records in the database.";
}

The variables are given values like so:

//// column names to check inputted information against to make sure the information actually exists
$ROWusername = $rows[username];
$ROWpassword = $rows[password];
$ROWpassword2 = $rows[password];
$ROWemailaddress = $rows[emaila];

The complete code is below:

<?
//// login information
$server = "localhost";
$db_user = "--------------------";
$db_pass = "----------------";
$database = "---------------------";

//// table names
$login = "login";
$contactbook = "contactbook";
$sentusermessages = "sentusermessages";
$usermessages = "usermessages";

//// column names to check inputted information against to make sure the information actually exists
$ROWusername = $rows[username];
$ROWpassword = $rows[password];
$ROWpassword2 = $rows[password];
$ROWemailaddress = $rows[emaila];

if($username=='' OR $password=='' OR $password2=='' OR $emaila=='') 
{ 
echo "All fields are required for security reasons."; 
} 
elseif($password != $password2) 
{ 
echo "Passwords do not match."; 
} 
elseif (!eregi("^([a-z0-9]+)([._-]([a-z0-9]+))*[@]([a-z0-9]+)([._-]([a-z0-9]+))*[.]([a-z0-9]){2}([a-z0-9])?$", $emaila))  

{ 
echo "Email address does not seem to be formatted correctly.  Please rectify."; 
} 
elseif (!eregi("^[a-z0-9_]+$", $username))  

{ 
echo "There are illegal characters in the username field."; 
} 
elseif (strlen($password) < 4)  

{ 
echo "Braille School passwords are only a minimum of 4 characters long."; 
} 
elseif (strlen($password) > 20)  

{ 
echo "Braille School passwords are only a maximum of 20 characters long."; 
} 
elseif (strlen($username) > 15)  

{ 
echo "Braille School usernames are only a maximum of 15 characters long."; 
} 
elseif (strlen($username) < 6)  

{ 
echo "Braille School usernames are only a minimum of 6 characters long."; 
} 
elseif($username != $ROWusername)
{
echo "Username does not match any records in the member database.";
}
elseif($password != $ROWpassword)
{
echo "Password does not match any records in the member database.";
}
elseif($password2 != $ROWpassword2)
{
echo "Confirmed password does not match any records in the member database.";
}
elseif($emaila != $ROWemailaddress)
{
echo "Email address does not match any records in the database.";
}
else
{
$connection = mysql_connect("$server", "$db_user", "$db_pass") or die(mysql_error()); 
$db = mysql_select_db("$database", $connection) or die(mysql_error());

$sql = "SELECT * FROM $login WHERE username='$username' AND password='$password' AND password2='$password' AND emaila='$emaila'"; 
$result = mysql_query($sql, $connection);

$sql = "delete from $login where username = '$username'";
mysql_query($sql, $connection) or die("Could not delete your login information");

$sql2 = "delete from $contactbook where username = '$username'";
mysql_query($sql2, $connection) or die("Could not delete your contact book information");

$sql3 = "delete from $contactbook where contactuser = '$username'";
mysql_query($sql3, $connection) or die("Could not delete your username from other members contact books");

$sql4 = "delete from $sentusermessages where frommbr = '$username'";
mysql_query($sql4, $connection) or die("Could not delete sent folder contents from you to other members");

$sql5 = "delete from $sentusermessages where tombr = '$username'";
mysql_query($sql5, $connection) or die("Could not delete sent folder contents from other members to you");

$sql6 = "delete from $usermessages where frommbr = '$username'";
mysql_query($sql6, $connection) or die("Could not delete inbox contents from you to other members");

$sql7 = "delete from $usermessages where tombr = '$username'";
mysql_query($sql7, $connection) or die("Could not delete inbox contents from other members to you"); 

echo "$username and its associated account information and messages have been permanently removed from the server."; 

//// send an email message to the administration to advise them of the account termination.
mail("[e-mail]", "$username Terminated Account","Dear Paddy,\n$username used the Braille School form to terminate their account today.  All settings were removed (login information, profile information, address book, as well as, inbox and sent messages contents.)\nCordially, 
Blindness Related Learning.", "From: Braille School Accounts <[e-mail]>");

//// send an email message to the person who terminated the account to show receipt of the successful termination.
mail("$emaila", "Braille School Account Termination Confirmation","Dear $username:\nWe would like to advise you that this is a receipt to inform you that your account has been terminated successfully via the form you used.  The username is now a part of the public domain which means it can be registered by someone else in the future.\nIf you feel the need to come back, you are more than welcome.\nIt would be very much appreciated if you can advise us why you terminated your account with us.\nCordially,\nBraille School Accounts Manager,\nhttp://www.brailleschool.com", "From: Braille School Accounts <[e-mail]>");
}
?>