Remove Db record via form question

Question

macross 0 Newbie Poster

15 Years Ago

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.

php

Edited 15 Years Ago by macross because: n/a

3 Contributors
9 Replies
182 Views
14 Hours Discussion Span
Latest Post 15 Years Ago Latest Post by macross

chrishea 182 Nearly a Posting Virtuoso

15 Years Ago

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.

diafol

15 Years Ago

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 15 Years Ago by diafol because: n/a

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

chrishea 182 Nearly a Posting Virtuoso · Answer 1 · 2009-10-05T22:10:02+00:00

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.

macross 0 Newbie Poster · Answer 2 · 2009-10-05T22:22:41+00:00

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>";

?>

macross 0 Newbie Poster · Answer 3 · 2009-10-05T23:18:01+00:00

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>

macross 0 Newbie Poster · Answer 4 · 2009-10-05T23:30:51+00:00

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.

macross 0 Newbie Poster · Answer 5 · 2009-10-06T01:03:00+00:00

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.

macross 0 Newbie Poster · Answer 6 · 2009-10-06T04:23:18+00:00

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.

macross 0 Newbie Poster · Answer 7 · 2009-10-06T04:56:55+00:00

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.";}	
	
?>