Can't DELETE a record in a database.

Hopestation · Tue Oct 02, 2018 1:46 am

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

hyper · Tue Oct 02, 2018 11:45 am

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.

Do you have a foreign key constraint?

Is this happening when I run the PHP, but I don't see the message?

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.

Hopestation · Thu Oct 04, 2018 1:45 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.

hyper · Thu Oct 04, 2018 9:55 am

I don't know how to set up error reporting.

Look at the link I provided.

Show more of your code to include the prepare and execute statements.

Hopestation · Fri Oct 05, 2018 1:55 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.

hyper · Fri Oct 05, 2018 7:38 am

I've reformatted your code to show why the delete SQL is "not working"

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

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

Hopestation · Sun Oct 07, 2018 1:52 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.

hyper · Sun Oct 07, 2018 12:51 pm

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:

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>

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:

Code: Select all

<?php
...
$Delete = $_REQUEST["delval"];
...
if($Delete == 1){
  $sql = "DELETE FROM members_2018 WHERE Rcd=$Recrd";
  $result = mysqli_query($db, $sql);
}

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:

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

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.

hyper · Sun Oct 07, 2018 12:59 pm

I've been using HTMLTidy to check my code, but it can't check for missing code.

HTMLTidy, only checks html, not PHP; and as you say it can't check code that isn't there

Hopestation · Tue Oct 09, 2018 10:18 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,

PHP forum, your PHP coding community

Can't DELETE a record in a database.

Login • Register

ADVERTISEMENT

ADVERTISEMENT

ADVERTISEMENT