Delete rows from a MySQL database with a confirmation

This tutorial shows you how to delete rows from your database and uses a JavaScript powered confirmation basically when you press delete a confirmation message will appear asking if you want to delete the item making sure you meant to delete it.

For this example lets say I have a news table with lots of records in it, in the structure of newsID, newsTitle and newsContent.

What were going to do is build a page that lists all the news items with their newsID and newsTitle and in that list will be a link to delete it once pressed the item will be deleted and the page will be reloaded.

At the very top of the page connect to your database here's a simple way of doing it just change the db properties with your own, in a live site the db details would normally be in a standalone file and then included into all pages then need it.

// db properties
$dbhost = 'localhost';
$dbuser = 'database username';
$dbpass = 'database password';
$dbname = 'database name';

// make a connection to mysql here
$conn = mysql_connect ($dbhost, $dbuser, $dbpass) or die ("I cannot connect to the database because: " . mysql_error());
mysql_select_db ($dbname) or die ("I cannot select the database '$dbname' because: " . mysql_error());

Right under the connection to the database we have an if statement that checks if isset delnews (the name of the function to delete the news items).

It goes like this if isset then run the query. It will only be set if a delete link has been pressed and then they confirmed they want to delete it, that is done by javascript then when it's finished it passed the newsID to the if statement to delete it.

The query is very simple using the command DELETE to delete the item then tell it where to delete from in this case news then where meaning which column since were sending the newsID it will know which row to delete. WHERE newsID $_GET['delnews'] this is the id of the news item since a link is clicked on and it send variables it sent via get so using get you can get the data contained within it.

Once the query has finished the row has been deleted then reload the page using a header() with $_SERVER['HTTP_REFERER'] which will store the page your on so it will reload the same page. Remember to use exit; as this will stop the script from carrying on.

if(isset($_GET['delnews']))
{
$query = mysql_query("DELETE FROM news WHERE newsID = '{$_GET['delnews']}'")or die('Error : ' . mysql_error());

header('Location: ' . $_SERVER['HTTP_REFERER']);
exit;
}

Then in the head section of your page put the JavaScript function.

First open JavaScript by using <script language=”JavaScript” type=”text/javascript”>

Then define the function first give it a name then pass two parameters to it delnews(newsID, newsTitle) when you call the function you will pass the two variables contained within the function so it knows what the data is.

Then an if statement is done to confirm you want to delete the item if you press no then the script stops there and nothing is done, if you press yes the script carries on.

The function passes the newsID to the php by using window.location.href then contains the link to pass it to, in this case I've used admin.php.

Then close the JavaScript with </script>

<head>
<script language="JavaScript" type="text/javascript">
function delnews(newsID, newsTitle)
{
if (confirm("Are you sure you want to delete '" + newsTitle + "'"))
{
window.location.href = 'admin.php?delnews=' + newsID;
}
}
</script>
</head>

Now in the body of the page were going to print out all the news itesm using a simple query.

The query calls all everything from the news table the die(mysql_error()); will only be called if something goes wrong. Then a while loop is used to loop through all the results, add all the results to an object called $row to make it an object use mysql_fetch_object.

Then print out a list (ul) I use the n to make line breaks in the source code it is not really needed but its easier to read your source code with it.

Then print out the news items inside the list in <li> tags first print the newsTitle using $row->newsTitle then create a link all double quotes need to be escaped to use a backslash on all double quotes inside the echo string.

The first part of the link is starting a JavaScript function its printing out the function name followed by the two parameters it needs newsID and newsTitle then print what's going to be used as the link i.e. DELETE then close the link and the list then close the ul too, remember to close the loop after that or you will get an error.

<?php
$result = mysql_query("SELECT * FROM news")or die(mysql_error());
while($row = mysql_fetch_object($result))
{
echo "<ul>n";
echo "<li>$row->newsTitle <a href="javascript:delnews('$row->newsID','$row->newsTitle')">Delete</a></li>n";
echo "</ul>n";
}
?>
</body>

That's it. Now every time you press Delete you will get a message asking if your want to delete the item and when yes is pressed the row will be deleted and the page will be reloaded.

Note if JavaScript is turned off then the link won't do anything as it needs JavaScript for the confirmation message.

Here's the full script:

<?php
// db properties
$dbhost = 'localhost';
$dbuser = 'database username';
$dbpass = 'database password';
$dbname = 'database name';

// make a connection to mysql here
$conn = mysql_connect ($dbhost, $dbuser, $dbpass) or die ("I cannot connect to the database because: " . mysql_error());
mysql_select_db ($dbname) or die ("I cannot select the database '$dbname' because: " . mysql_error());

if(isset($_GET['delnews']))
{
$query = mysql_query("DELETE FROM news WHERE newsID = '{$_GET['delnews']}'")or die('Error : ' . mysql_error());
header('Location: ' . $_SERVER['HTTP_REFERER']);
exit;
}
?>

<html>
<head>
<script language="JavaScript" type="text/javascript">
function delnews(newsID, newsTitle)
{
if (confirm("Are you sure you want to delete '" + newsTitle + "'"))
{
window.location.href = 'admin.php?delnews=' + newsID;
}
}
</script>
</head>

<body>
<?php
$result = mysql_query("SELECT * FROM news")or die(mysql_error());
while($row = mysql_fetch_object($result))
{
echo "<ul>n";
echo "<li>$row->newsTitle <a href="javascript:delnews('$row->newsID','$row->newsTitle')">Delete</a></li>n";
echo "</ul>n";
}
?>
</body>