HTML 1 click delete from MySQL database

Question

Hey Guys,

how would I create a link that would when clicked erase a record from a MySQL database based on the id of that item? using php if possible.

edit// The link will require authentication in order to view

Thanks

Answer 1

1. Put the id in the query string
2. Read the value from $_GET
3. Construct the SQL query
4. Send it

… or don't. Having a bot or a pre-fetching cache delete your database is a really bad idea. Use forms and $_POST. Get requests are supposed to be safe.

Answer 2

EDIT: I somehow got it in my head that you were looking for an ajax solution, but it seems that I was wrong. Still, I'll leave this here in case it's useful.. @David's solution is the way to go based on what you asked.

This should get you started. The client script uses jQuery:

<a id="item_45" href="#" class=".btnDelete">Delete this</a>
<a id="item_100" href="#" class=".btnDelete">Delete this</a>

<script>
$(document).ready(function() {
    $("a.btnDelete").click(function() {
        // get the number from the ID after the '_'. Remember, IDs cannot start with numbers
        var itemId = this.id.split("_")[1];

        // ask the server for some json
        // if the 'status' offset is set to '1', the delete was successful
        // otherwise, display what's in the 'error' offset
        $.post('deleteStuff.php', {id: itemId}, function(json) {
            if(json.status == "1") {
                alert("delete was successful");
            } else {
                alert(json.error);
            }
        }, "json"); 
        return false;
    });
});
</script>

<?php

$id = $_POST['itemId'];

// delete corresponding record from database

if($delete_successful) {
    $data = array('status' => '1');
} else {
    $data = array('error' => 'Could not delete item. Please contact support';
}

echo json_encode($data);

?>

Answer 3

<?php
if (isset($_GET['delete']) && preg_match('/[0-9]+/', $_GET['delete'])) {
    $id  = (int) $_GET['delete'];
    $sql = "DELETE FROM $table WHERE id = '$id' LIMIT 1";
    $res = mysql_query($sql);
    $del = mysql_affected_rows();
}
if ($del) {
    echo '<p>Rows deleted: <strong>'.$del.'</strong></p>';
}

// loop over your records here, outputting an <a> tag for each record
// this could be an interactive data-grid table or whatever
echo '<p><a href="?delete=1">Delete record #1</a></p>';
echo '<p><a href="?delete=2">Delete record #2</a></p>';