Hi.
I have a MYSQL database on both an XAMPP server and on line server.
I can insert records and change records in both but deleting is ignored.
My code is:
$sql = "DELETE FROM members_2018 WHERE Rcd=$Recrd";
Rcd is a unique index field.
I have tried this on both servers and it doesn't work on either.
If I try running the query in phpMysql it asks if I really want to delete the record before it does it.
Is this happening when I run the PHP, but I don't see the message?
Thanks
Can't DELETE a record in a database.
Moderators: egami, macek, gesf
Do you have a foreign key constraint?If I try running the query in phpMysql it asks if I really want to delete the record before it does it.
I can insert records and change records in both but deleting is ignored.
No, because PHP doesn't (cannot) wait for you to press y or n in the way that other programming languages can. Do you have display errors set? as you should be getting an error.Is this happening when I run the PHP, but I don't see the message?
-
- New php-forum User
- Posts: 8
- Joined: Fri Aug 03, 2018 6:59 am
Hi Hyper.
Thanks for your reply.
I'm new to using MYSQL so I followed the example at:
https://www.w3schools.com/php/php_mysql_delete.asp
// sql to delete a record
$sql = "DELETE FROM MyGuests WHERE id=3";
The only difference is that I've used a variable instead of an integer. Is this a mistake?
I tried putting ' ' round $recd but this didn't work.
Sorry, I don't know what a foreign constraint is and I don't know how to set up error reporting.
Thanks for your reply.
I'm new to using MYSQL so I followed the example at:
https://www.w3schools.com/php/php_mysql_delete.asp
// sql to delete a record
$sql = "DELETE FROM MyGuests WHERE id=3";
The only difference is that I've used a variable instead of an integer. Is this a mistake?
I tried putting ' ' round $recd but this didn't work.
Sorry, I don't know what a foreign constraint is and I don't know how to set up error reporting.
-
- New php-forum User
- Posts: 8
- Joined: Fri Aug 03, 2018 6:59 am
This is the code:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN">
<html lang="EN" dir="ltr" xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="content-type" content="text/xml; charset=utf-8" />
<title>Update/Delete Record</title>
</head>
<body>
<?php
$Delete = $_REQUEST["delval"];
$Recrd = $_REQUEST["record"];
$Forename = $_REQUEST["forename"];
$Surname = $_REQUEST["surname"];
$Status = $_REQUEST["status"];
$Position = $_REQUEST["position"];
$House = $_REQUEST["house"];
$Number = $_REQUEST["number"];
$Road = $_REQUEST["road"];
$Area = $_REQUEST["area"];
$Town = $_REQUEST["town"];
$County = $_REQUEST["county"];
$Postcode = $_REQUEST["postcode"];
$Phone = $_REQUEST["phone"];
$Mobile = $_REQUEST["mobile"];
$EMail = $_REQUEST["email"];
$Badge = $_REQUEST["badge"];
include('dbconnect-01.php');
$result =mysqli_query($db, "SELECT * FROM members_2018");
if (!$result) {die("Database query failed: " . mysqli_error());}
if($Delete == 1){$sql = "DELETE FROM members_2018 WHERE Rcd=$Recrd"; print "Deleting record $Recrd";}
else
{$sql = "UPDATE members_2018
SET Forename='$Forename', Surname='$Surname', Status='$Status', Position='$Position', House='$House', Number='$Number', Road='$Road',
Area='$Area', Town='$Town', County='$County', Postcode='$Postcode', Phone='$Phone', Mobile='$Mobile', EMail='$EMail', Tag='$Badge'
WHERE Rcd=$Recrd";
if (!mysqli_query($db, $sql)) {echo "Error: " . $sql . "<br />"; sleep(10); print"Updating reocord";}}
echo("<script>location.replace(\"members-06.php\");</script>");
?>
</body>
</html>
As I said before, this uodates the record if $Delete is not 1, but doesn't delete when it is =1.
I also get the message "Deleting record 1", etc..
I've looked at your link and also followed it up on the W3schools web site, which gives examples that I can understand.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN">
<html lang="EN" dir="ltr" xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="content-type" content="text/xml; charset=utf-8" />
<title>Update/Delete Record</title>
</head>
<body>
<?php
$Delete = $_REQUEST["delval"];
$Recrd = $_REQUEST["record"];
$Forename = $_REQUEST["forename"];
$Surname = $_REQUEST["surname"];
$Status = $_REQUEST["status"];
$Position = $_REQUEST["position"];
$House = $_REQUEST["house"];
$Number = $_REQUEST["number"];
$Road = $_REQUEST["road"];
$Area = $_REQUEST["area"];
$Town = $_REQUEST["town"];
$County = $_REQUEST["county"];
$Postcode = $_REQUEST["postcode"];
$Phone = $_REQUEST["phone"];
$Mobile = $_REQUEST["mobile"];
$EMail = $_REQUEST["email"];
$Badge = $_REQUEST["badge"];
include('dbconnect-01.php');
$result =mysqli_query($db, "SELECT * FROM members_2018");
if (!$result) {die("Database query failed: " . mysqli_error());}
if($Delete == 1){$sql = "DELETE FROM members_2018 WHERE Rcd=$Recrd"; print "Deleting record $Recrd";}
else
{$sql = "UPDATE members_2018
SET Forename='$Forename', Surname='$Surname', Status='$Status', Position='$Position', House='$House', Number='$Number', Road='$Road',
Area='$Area', Town='$Town', County='$County', Postcode='$Postcode', Phone='$Phone', Mobile='$Mobile', EMail='$EMail', Tag='$Badge'
WHERE Rcd=$Recrd";
if (!mysqli_query($db, $sql)) {echo "Error: " . $sql . "<br />"; sleep(10); print"Updating reocord";}}
echo("<script>location.replace(\"members-06.php\");</script>");
?>
</body>
</html>
As I said before, this uodates the record if $Delete is not 1, but doesn't delete when it is =1.
I also get the message "Deleting record 1", etc..
I've looked at your link and also followed it up on the W3schools web site, which gives examples that I can understand.
I've reformatted your code to show why the delete SQL is "not working"
If you follow the code you will see that it's not there to execute.
It's not the only problem that you have in your code, but there's your answer. The code you have is out of date, the link you gave also has what's called PDO instructions, look to use that instead
Code: Select all
if($Delete == 1) {
$sql = "DELETE FROM members_2018 WHERE Rcd=$Recrd"; print "Deleting record $Recrd";
# Where's the database query?
} else {
$sql = "UPDATE members_2018
SET Forename='$Forename', Surname='$Surname', Status='$Status', Position='$Position', House='$House', Number='$Number', Road='$Road',
Area='$Area', Town='$Town', County='$County', Postcode='$Postcode', Phone='$Phone', Mobile='$Mobile', EMail='$EMail', Tag='$Badge'
WHERE Rcd=$Recrd";
if (!mysqli_query($db, $sql)) { # this will execute
echo "Error: " . $sql . "<br />"; sleep(10); print"Updating reocord";
}
}
It's not the only problem that you have in your code, but there's your answer. The code you have is out of date, the link you gave also has what's called PDO instructions, look to use that instead

-
- New php-forum User
- Posts: 8
- Joined: Fri Aug 03, 2018 6:59 am
Thanks again Hyper.
I can see what the problem is now.
I've been using HTMLTidy to check my code, but it can't check for missing code.
Regarding my code, I have been using HTML, XHTML &CSS for Dummies, 2nd Edition published in 2011 for guidance, since 2014 and W3Schools for working examples. I have seen their use of PDO in some examples but didn't know what the difference was.
Since starting on ALGOL in the 1960s I've done odd projects in various languages and this is the latest one for for a Club I go to, so I'm not that good at it.
I can see what the problem is now.
I've been using HTMLTidy to check my code, but it can't check for missing code.
Regarding my code, I have been using HTML, XHTML &CSS for Dummies, 2nd Edition published in 2011 for guidance, since 2014 and W3Schools for working examples. I have seen their use of PDO in some examples but didn't know what the difference was.
Since starting on ALGOL in the 1960s I've done odd projects in various languages and this is the latest one for for a Club I go to, so I'm not that good at it.
Most of what you have learned from your book will not be a complete waste of time, to update a bit use this as a html template:
I've just tried to write a few simple explanations on security, but it's near impossible in just a simple, single post, so see examples below.
If we take your code:
and we enter http://yoursite.com/yourpage.php?delval=1&record=42 - we could probably delete record 42 from your database.
How would a nasty person know this? well, they wouldn't know for sure that they could do that, but looking at you form names, they could work out if it was worth a try. And in a similar way, they could also use your UPDATE SQL to place some JavaScript in your database.
Your code should look more like this:
htmlentities is used to make safe characters like < which can be used for things like <script>. It is only part of your security.
I have barely scratched the surface here, but I hope that I have nudged you to do further research.
You should know from your ALGOL days that you must never trust user input, it is more relevant on the web especially as it can be intercepted. Add to this that you should never completely trust tutorials on the web, no matter how well meaning; take what you learn and verify it with other information and make your own judgement.
Code: Select all
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Your page title here</title>
<link href='style.css' rel='stylesheet'>
</head>
<body>
<!--
All of your html here
-->
</body>
</html>
If we take your code:
Code: Select all
<?php
...
$Delete = $_REQUEST["delval"];
...
if($Delete == 1){
$sql = "DELETE FROM members_2018 WHERE Rcd=$Recrd";
$result = mysqli_query($db, $sql);
}
How would a nasty person know this? well, they wouldn't know for sure that they could do that, but looking at you form names, they could work out if it was worth a try. And in a similar way, they could also use your UPDATE SQL to place some JavaScript in your database.
Your code should look more like this:
Code: Select all
$Delete = htmlentities($_POST["delval"]);# be specific, POST or GET
$db = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
$sql = $db->prepare('SELECT * FROM members_2018 WHERE id = :id');// be specific
$sql->execute([$id]);
$result = $sql->fetch(PDO::FETCH_ASSOC);
I have barely scratched the surface here, but I hope that I have nudged you to do further research.
You should know from your ALGOL days that you must never trust user input, it is more relevant on the web especially as it can be intercepted. Add to this that you should never completely trust tutorials on the web, no matter how well meaning; take what you learn and verify it with other information and make your own judgement.
-
- New php-forum User
- Posts: 8
- Joined: Fri Aug 03, 2018 6:59 am
Thanks again, Hyper.
Your reply was very comprehensive.
I'm going to have to spend some time following up your comments.
I have now got deleting working, but I may have to start again,
Your reply was very comprehensive.
I'm going to have to spend some time following up your comments.
I have now got deleting working, but I may have to start again,