Deleting a record in a SQL database using PHP

Question

I have coded a few pages to delete a record from a database but it doesnt seem to work.

I have a website where the user logs in using the correct name and password that is stored in a database, this works fine.

This then takes the user to the admin page, the code consists of some of the following:

<?php

$sql = mysqli_query($con, "SELECT * FROM details INNER JOIN genre ON genre.genreID = details.genreID;") or die(mysqli_error($sql));


while($row = mysqli_fetch_array($sql)) {


  echo '<div class="info-box">

  <img class="image" src='.'"'.$row['image'].'"
  '.'>

  <a class="edit" href="delete.php?id=' . $row["ID"] .'" onclick="return confirm(\'Are you sure you want to delete this record?\')">Delete</a>  |



  </div>'; 

} 

mysqli_close($con);
?>

I have created a JS fiddle with the full code on the admin page ... http://jsfiddle.net/n3n4ot4g/

This page goes to delete.php behind the scenes to delete the record. This page consists of the following code:

<?php

$myID = $_GET['ID'];

echo $myID;


include ('includes/dbconx.php');


//check if a value has been sent
if(isset($myID)){

//MySQL statement to delete record using the unique id variable
mysqli_query($con, "DELETE FROM details WHERE ID = '$myID'") or die ('Error: '.mysqli_error());

}

mysqli_close($con);


header("Location: admin.php");


?>

When i click on the delete link the pop up box appears to confirm the deletion, where i click yes, but then it takes me to admin.php with no content in it. All the records disappear. This also happens when i click on "view records" on the left panel navigation. I can go back and log in again and see the records, but the record i tried to delete will not be deleted.

Im not sure if this is a HTML or PHP problem.

I know JS fiddle is not designed for PHP, but i could not find an alternative.

**Danger**: You are **vulnerable to [SQL injection attacks](http://bobby-tables.com/)** that you need to [defend](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) yourself from. — Quentin, Jan 08 '15 at 11:05
try to put else statement in second code and check whether session started on not. — Manoj Sharma, Jan 08 '15 at 11:21
`mysqli_error()` expects the connection object as paramater. Change `mysqli_error($sql)` to `mysqli_error($con)` — Gerald Schneider, Jan 08 '15 at 11:26

score 0 · Answer 1 · answered Jan 08 '15 at 11:14

0

Change: mysqli_query($con, "DELETE FROM details WHERE ID = '$myID'") or die ('Error: '.mysqli_error());

to:

mysqli_query($con, "DELETE FROM details WHERE ID = " . $myID) or die ('Error: '.mysqli_error());

By putting $myID in single quotes, you're saying that it should be taken as is, so your query is searching for an ID of literally "$myID" which of course it won't find.

Alternatively, you could remove the single quotes and not concatenate the $myID variable, making sure that the query itself is still wrapped in double quotes. This means that the variable will be processed properly and the value of it will be used.

mysqli_query($con, "DELETE FROM details WHERE ID = $myID") or die ('Error: '.mysqli_error());

answered Jan 08 '15 at 11:14

danhardman

633
1
6
16

I tried replacing this line with both your examples but my code is still doing the same thing.. – Jordan Jan 08 '15 at 11:18
1

Change `$myID = $_GET['ID'];` to `$myID = $_GET['id'];` – danhardman Jan 08 '15 at 11:22
This is now working to delete the record! Thanks. But only problem now is it is not displaying the records after deletion, it should go back to the admin page with all the records but instead the part of the page is blank where the records should be. Should i create a new question for this problem? – Jordan Jan 08 '15 at 11:28
it's because you're `echo`ing to the page before you run the `header()` method. Header information needs to be set before any content is loaded to the page. Remove the `echo $myID;` and it will work. – danhardman Jan 08 '15 at 11:29
I originally had the echo $myID to check if it was working, but i have removed it and still nothing has changed. – Jordan Jan 08 '15 at 11:34

Deleting a record in a SQL database using PHP

1 Answers1