Ad:

PHP Discussion Thread
Marked Solved
Views: 572

Oct 5th, 2009

Remove Db record via form question

Expand Post »

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

PHP Syntax (Toggle Plain Text)
$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>";
}
$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 Syntax (Toggle Plain Text)
<?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>";
 
?>
<?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.

Last edited by macross; Oct 5th, 2009 at 11:37 am.

Similar Threads

Form Question in ASP.NET
Remove space after form in IE8 in HTML and CSS
Open form with new record in VB.NET
MS Access - load record to form from button click on another? in Visual Basic 4 / 5 / 6

macross

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

14 posts
since Sep 2009

Permalink

Oct 5th, 2009

-1

Re: Remove Db record via form question

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.

chrishea

Reputation Points: 210

Solved Threads: 228

Nearly a Posting Virtuoso

Offline

1,389 posts
since Sep 2008

Permalink

Oct 5th, 2009

-1

Re: Remove Db record via form question

Click to Expand / Collapse Quote originally posted by chrishea ...

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 Syntax (Toggle Plain Text)
<?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>";
 
?>
<?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

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

14 posts
since Sep 2009

Permalink

Oct 5th, 2009

Re: Remove Db record via form question

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

PHP Syntax (Toggle Plain Text)
<!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>
<!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

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

14 posts
since Sep 2009

Permalink

Oct 5th, 2009

Re: Remove Db record via form question

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

PHP Syntax (Toggle Plain Text)
mysql_query ($query1) ...
mysql_query ($query1) ...

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

PHP Syntax (Toggle Plain Text)
$handle = mysql_connect("localhost", "username", "password") or die(mysql_error());
$handle = mysql_connect("localhost", "username", "password") or die(mysql_error());

then in the query use that handle:

PHP Syntax (Toggle Plain Text)
mysql_query($query1,$handle) or die("Failed Query of " . $query1);
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.

chrishea

Reputation Points: 210

Solved Threads: 228

Nearly a Posting Virtuoso

Offline

1,389 posts
since Sep 2008

Permalink

Oct 5th, 2009

Re: Remove Db record via form question

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

I changed line 28 to

PHP Syntax (Toggle Plain Text)
mysql_query($query1) or die("Failed Query of " . $query1);
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

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

14 posts
since Sep 2009

Permalink

Oct 5th, 2009

Re: Remove Db record via form question

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 Syntax (Toggle Plain Text)
<?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");
 
 
?>
<?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.

Attached Thumbnails

Click image for larger version

Name: db.jpg
Views: 9
Size: 49.9 KB
ID: 11973

Last edited by macross; Oct 5th, 2009 at 4:10 pm. Reason: added image

macross

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

14 posts
since Sep 2009

Permalink

Oct 5th, 2009

Re: Remove Db record via form question

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.

PHP Syntax (Toggle Plain Text)
mysql_query("DELETE from group where email = 'email@domian.com'")
mysql_query("DELETE from group where email = 'email@domian.com'")

Should this be 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:

PHP Syntax (Toggle Plain Text)
$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.";
}
$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.";
}

Last edited by ardav; Oct 5th, 2009 at 6:05 pm.

ardav

Sponsor

Featured Poster

Reputation Points: 1067

Solved Threads: 955

Disgraced Poster

Offline

6,728 posts
since Oct 2006

Permalink

Oct 5th, 2009

Re: Remove Db record via form question

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 Syntax (Toggle Plain Text)
<?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");
 
?>
<?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

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

14 posts
since Sep 2009

Permalink

Oct 5th, 2009

Re: Remove Db record via form question

Click to Expand / Collapse Quote originally posted by ardav ...

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:
PHP Syntax (Toggle Plain Text)
$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.";
}
$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?

(Toggle Plain Text)

<?php

$email = addslashes(htmlentities($_POST['email2']));
$dbname="domain_sampledb";
$username="username";
$password="password";

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

Last edited by macross; Oct 5th, 2009 at 7:58 pm.

macross

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

14 posts
since Sep 2009

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in PHP Forum Timeline: Controlling variables

Next Thread in PHP Forum Timeline: Distinguishing Summer and non-summer Weeks

DaniWeb Message

Cancel Changes

User Name
Password