0

This should be an easy question, I googled it and was noytable to get it working 100%.

I have a sql DB which records people signing up via a form for a newsletter/group. I have the form working fine where it inserts the data into the Db correctly. I now want to create another form that removes people form the database, aka a remove me type thing.

I have a simple form set up that asks for their email and I want the php to look at the DB, find a record that has the same email address and then removes that record from the DB.

I've been working with this sample

$dbuser="username";
$dbpass="password";
$dbname="mydata";  //the name of the database
$chandle = mysql_connect("localhost", $dbuser, $dbpass) 
    or die("Connection Failure to Database");
mysql_select_db($dbname, $chandle) or die ($dbname . " Database not found. " . $dbuser);

$mainsection="links"; //The name of the table where web links are stored
$idno=10;
$query1="delete from " . $mainsection . " where id = " . $idno;
mysql_db_query($dbname, $query1) or die("Failed Query of " . $query1);
echo "Link with ID " . $idno . " has been deleted as requested.<br>";
}

and I have changed it a bit so it looks like this

<?php

$email = $_POST['email2'];

if (!preg_match("/^([a-z0-9._-](\+[a-z0-9])*)+@[a-z0-9.-]+\.[a-z]{2,6}$/i", $email)) {  
  die ('The email you provided appears to have an error, please check it and submit the registration again.');
}

$email = $_POST['email2'];

$dbname="sampledb";

mysql_connect("localhost", "username", "password") or die(mysql_error());
mysql_select_db("sampledb") or die(mysql_error());

$mainsection="group"; //The name of the table where web links are stored
$idno= $email;
$query1="delete from " . $mainsection . " where email = " . $idno;
mysql_db_query($dbname, $query1) or die("Failed Query of " . $query1);
echo "Link with ID " . $idno . " has been deleted as requested.<br>";

?>

Obviously, I am sure to you guys, it does not work. I get "Failed Query of delete from group where email = email@email.com"

The "group" table in the specified Db does have a record with that email listed in the "email" column. Each record is 9 entries and "email" is the first entry and is also the primary/index entry for that record.

Edited by macross: n/a

3
Contributors
9
Replies
10
Views
8 Years
Discussion Span
Last Post by macross
-1

Change your mysql_db_query to a mysql_query with the query string as the only parm. It will use the DB that you selected.

-1

Change your mysql_db_query to a mysql_query with the query string as the only parm. It will use the DB that you selected.

If I understood you, like this? If so I am getting a different error now: "Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /xxx/xxx/xxxx/group_unreg.php on line 28
Failed Query of delete from group where email = name@domain.com"

<?php

$email = $_POST['email2'];

if (!preg_match("/^([a-z0-9._-](\+[a-z0-9])*)+@[a-z0-9.-]+\.[a-z]{2,6}$/i", $email)) {
	die ('The email you provided appears to have an error, please check it and submit the registration again.');
}

$email = $_POST['email2'];

$dbname="sampledb";

mysql_connect("localhost", "username", "password") or die(mysql_error());
mysql_select_db("sampledb") or die(mysql_error());

$mainsection="group"; //The name of the table where web links are stored
$idno= $email;
$query1="delete from " . $mainsection . " where email = " . $idno;
mysql_query($dbname, $query1) or die("Failed Query of " . $query1);
echo "Link with ID " . $idno . " has been deleted as requested.<br>";

?>
0

Sorry it might help if I pasted all the page so the line error matches properly

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>

<?php

$email = $_POST['email2'];

if (!preg_match("/^([a-z0-9._-](\+[a-z0-9])*)+@[a-z0-9.-]+\.[a-z]{2,6}$/i", $email)) {
	die ('The email you provided appears to have an error, please check it and submit the registration again.');
}

$email = $_POST['email2'];

$dbname="sampledb";

mysql_connect("localhost", "username", "password") or die(mysql_error());
mysql_select_db("sampledb") or die(mysql_error());

$mainsection="group"; //The name of the table where web links are stored
$idno= $email;
$query1="delete from " . $mainsection . " where email = " . $idno;
mysql_query($dbname, $query1) or die("Failed Query of " . $query1);
echo "Link with ID " . $idno . " has been deleted as requested.<br>";

?>


</body>
</html>
1

You have a choice:
1. Your query can use just the query name as in:

mysql_query ($query1) ...

2. In your Connect use a variable to keep the resource handle:

$handle = mysql_connect("localhost", "username", "password") or die(mysql_error());

then in the query use that handle:

mysql_query($query1,$handle) or die("Failed Query of " . $query1);

The first one is simpler. It's all in the PHP manual with an example.

0

Sorry, you said that originally and I missed it, thank you for your help so far.

I changed line 28 to

mysql_query($query1) or die("Failed Query of " . $query1);

it got me back to "Failed Query of delete from group where email = name@domain.com"

To make sure I am not confusing the point, the DB table is "group", it contains 9 columns, the first of which is "email" and is the primary/index entry for that table. The table currently has two rows of data, 1 of which has the email i am entering in the form as the email entry for that row.

0

I simplified the php as much as I think I can and it still refuses to execute. I have checked multiple times to make sure that "group" is the name of the table, that "email" is the name of the catagory that contains the email value, and that the email I have entered is indeed the value in that record.

Do I need to run some other query first or include some other variable.... this is making no sense to me.

<?php

mysql_connect("localhost", "username", "password") or die(mysql_error());
mysql_select_db("domain_sampledb") or die(mysql_error());
mysql_query("DELETE from group where email = 'email@domian.com'") or die("Failed Query");


?>

Here is a screen shot of the phpmyadmin showing the DB i am trying to work with.

Edited by macross: added image

1

Sorry to butt in. Your table seems to have a strange primary key - 'lastname'. A primary key should be unique. What happens if two users have the same lastname? Consider having an 'id' field, set as integer and primary key, not null, autoincrement.

mysql_query("DELETE from group where email = 'email@domian.com'")

Should this be [email]email@domain.com, or is it only as an example?

Try running the query in the phpmyadmin SQL box to see what happens.

You may want to rename the 'group' field as this is a keyword in mysql syntax, either that or enclose the 'group' with `` (weird quotes - the key before '1' on my keyboard).

In addition, you should sanitize/clean your $_POST variable before using in an SQL statement to avoid injection attacks.

It could be useful to redo your statement like this:

$email = addslashes(htmlentities($_POST['email']));
//or use mysql_real_escape_string()
$rs = mysql_query("DELETE FROM `group` WHERE email = '$email'");
if(mysql_affected_rows() > 0){
   echo "Record deleted successfully";
}else{
   echo "Record could not be deleted, see admin.";
}

Edited by diafol: n/a

0

Not certian which change made the difference, but it worked!

I redid the database and changed group to jgroupco, and I added a 10th field as you suggested.

I recoded the very simple version of the script to be

<?php

mysql_connect("localhost", "username", "password") or die(mysql_error());
mysql_select_db("domain_sampledb") or die(mysql_error());
mysql_query("DELETE from `jgroupco` where email = 'name@domain.com'") or die("Failed Query");

?>

Now I need to rebuild it and make some changes as you suggested. I really appreciate the help from both of you, +rep and I will switch this to solved once I am sure I don't have any last questions related to it.

0

In addition, you should sanitize/clean your $_POST variable before using in an SQL statement to avoid injection attacks.

It could be useful to redo your statement like this:

$email = addslashes(htmlentities($_POST['email']));
//or use mysql_real_escape_string()
$rs = mysql_query("DELETE FROM `group` WHERE email = '$email'");
if(mysql_affected_rows() > 0){
   echo "Record deleted successfully";
}else{
   echo "Record could not be deleted, see admin.";
}

Ok I took what you suggested and I came up with the following, which still works when i run it. Does this look right or am I using it incorrectly?

<?php

$email = addslashes(htmlentities($_POST['email2']));
$dbname="[I]domain[/I]_sampledb";
$username="[I]username[/I]";
$password="[I]password[/I]";

mysql_connect("localhost", $username, $password) or die(mysql_error());

mysql_select_db($dbname) or die(mysql_error());

mysql_query("DELETE from `jgroupco` where email = '$email'") or die("Failed Query");

	if(mysql_affected_rows() > 0)
	
	{   echo "You have succesfully removed yourself from the group";}
	
	else
	{   echo "We are unable to find a match for that address, please check it and try again. <br />
	If you are still having trouble please contact an administrator.";}	
	
?>

Edited by macross: n/a

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.