Mysql delete an entry a table when an element is clicked

Question

I am trying to create a table to manage uploaded music, I would like it so when a user presses 'delete' it deletes the entry. Here is the code I am working with.

<?php
$sql="SELECT * FROM content WHERE `uploader` = '" . $user_data['username'] . "' ORDER BY id DESC";
$records=mysql_query($sql);
while($sound=mysql_fetch_assoc($records)){
    echo "<tr class='adder'>";
    echo "<td width='40' class='player'>&nbsp;&nbsp;<a href='".$sound['link']."' class='sm2_button'>Play/</a></td>";
    echo '<td width="75" class="name">'.$sound['date'].'</td>';
    echo '<td width="150" class="name">'.$sound['name'].'</td>';
    echo "<td width='58' class='bpm'>".$sound['uploader']."</td>";
    echo "<td width='220' class='keywords'>".$sound['keywords']."</td>";
    echo "<td width='50' class='keywords'>Edit</td>";
    echo "<td width='50' class='keywords'><span onclick='mysql_query('DELETE FROM content WHERE id = ".$sound['id']." ')>Delete</span></td>";
    echo "</tr>";
    }
?>

You have not close the single quotes for onclick function at the end and before > — Sunil Pachlangia, Sep 07 '15 at 05:12
possible duplicate of [What is the difference between client-side and server-side programming?](http://stackoverflow.com/questions/13840429/what-is-the-difference-between-client-side-and-server-side-programming) — Mike, Sep 07 '15 at 05:15
mysql module is deprecated in php. Best practice is to use [PDO](http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers). — ygesher, Sep 07 '15 at 08:38

score 1 · Answer 1 · answered Sep 07 '15 at 05:55

I'm not a php developer but i believe you should submit the data to the server. It seems that your code is merely printing the 'mysql_query('DELETE FROM content WHERE id = ".$sound['id']." ')

to the html page.

what you should do is

submit the data to the server either via POST or GET Example (deleteSound.php?soundId=1)
deleteSound.php will then delete the sound with id=1 using php code(server code)
after deleting the sound redirect it back to the original page (soundlist.php)

score 1 · Accepted Answer · edited Oct 29 '21 at 15:55

1

It's really quite simple

Create a page for example called process.php

<?php
// include your connection file

$id = "0";
if (isset($_GET['id'])) {
  $id = $_GET['id'];
}

try {
    $sql = "DELETE FROM table-name WHERE id=:id";
    $stmt = $conn->prepare($sql);
    $stmt->bindValue(':id', $id, PDO::PARAM_INT);
    $stmt->execute();
} catch (PDOException $e) {
    die("Could not delete from the table: " . $e->getMessage());
}
  $GoTo = "/the/path/where/you/want/to/send/the/visitor.php";
  header(sprintf("Location: %s", $GoTo));
?>

Then change this line...

echo "<td width='50' class='keywords'><span onclick='mysql_query('DELETE FROM content WHERE id = ".$sound['id']." ')>Delete</span></td>";

to...

echo "<td width='50' class='keywords'><a href=\"process.php?id=".$sound['id']."\">Delete</a></td>";

edited Oct 29 '21 at 15:55

Nimantha

6,405
6
28
69

answered Sep 07 '15 at 07:39

Kuya

7,280
4
19
31

thanks this sent in the right direction! the process page came out a little simpler – Jeff Sep 07 '15 at 08:15
@Jeff If this answer was helpful to you and answered your question, please don't forget to [accept that answer](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work). Also see [What does it mean when an answer is "accepted"?](http://meta.stackexchange.com/help/accepted-answer) and [Why is voting important?](http://meta.stackexchange.com/help/why-vote). – Kuya Sep 07 '15 at 08:20

score 0 · Answer 3 · answered Sep 08 '15 at 01:05

I just relized A more secure method would be to use $_POST... by using $_GET any user could delete another users entry by changing 'process.php?id=x'

I had to use a form button to do this....

table line:

echo "<td width='50' class='keywords'><form name='deletesound' class='deletesound' method='post' action='process.php'>

";

process.php:

<?php
ob_start();
include 'core/init.php'; 
include 'includes/header.php';
protect_page();

$id = $_POST['getid'];
if (isset($_POST['getid'])) {
  $id = $_POST['getid'];
  mysql_query("DELETE FROM content WHERE id = $id");
  header('Location: manage.php');
  exit();
}
?>

Vipin CP · Answer 4 · 2015-09-07T06:08:50.377

Try below code with ajax.

viewContent.php

<?php
$sql="SELECT * FROM content WHERE `uploader` = '" . $user_data['username'] . "' ORDER BY id DESC";

$records=mysql_query($sql);
while($sound=mysql_fetch_assoc($records)){
?>
  <tr class='adder'>
    <td width='40' class='player'>&nbsp;&nbsp;<a href='<?php echo $sound['link']; ?>' class='sm2_button'>Play/</a></td>
    <td width="75" class="name"><?php echo $sound['date']; ?></td>
    <td width="150" class="name"><?php echo $sound['name']; ?></td>
    <td width='58' class='bpm'><?php echo $sound['uploader']; ?></td>
    <td width='220' class='keywords'><?php echo $sound['keywords']; ?></td>
    <td width='50' class='keywords'>Edit</td>
    <td width='50' class='keywords'><span onclick='deleteContent(<?php echo $sound['id']; ?>)'>Delete</span></td>
    </tr>
    }
?>

<script>
function deleteContent(id)
{
var xmlhttp;
if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    window.location="/viewContent.php";
    }
  }
xmlhttp.open("GET","deleteContent.php?q="+id,true);
xmlhttp.send();
}
</script>

deleteContent.php

<?php
$id=$_GET['id'];
mysql_query('DELETE FROM content WHERE id = ".$id." ')
echo "done";

?>

Above code send request from view page to delete page through ajax , and when gets repsonse reload the page .

You should not be using mysql_* functions as they are deprecated. Also, you haven't defined `$sound` in your PHP. And assuming you actually meant that to be `$id` instead of `$sound['id']`, your code is open to SQL injection. — Mike, Sep 07 '15 at 06:02
just a sample code done for demonstrarting ajax functionality. Cant explain code optimizaion using PDO etc, as its out of the scope of this question. defenitely i mean $id and sorry for that mistake. — Vipin CP, Sep 07 '15 at 06:08
I disagree. You're providing an answer, so in a sense it is *your* code. This is what you are recommending the OP executes on their server and therefore you should take precautions to make it secure from common attacks and not use deprecated functions. — Mike, Sep 07 '15 at 06:10

Mysql delete an entry a table when an element is clicked

4 Answers4