How to Remove a Database Entry when a Link is Clicked

Question

I wanted to expand my PHP skills so I read through a tutorial on tutorialzine. I understand the instructions presented in the tutorial. But when it comes to expanding on it I seem to be lacking a connection. My main goal was to simply delete a selected note when an a tag is clicked. However I don't know how to select the id assigned to the note to be able to pass it to my delete function.

Source: http://tutorialzine.com/2010/01/sticky-notes-ajax-php-jquery/

Thanks for the help.

<?php
error_reporting(E_ALL^E_NOTICE);
require 'connect.php';
mysql_query("DELETE FROM notes WHERE id>3 AND dt<SUBTIME(NOW(),'0 1:0:0')");
$query = mysql_query("SELECT * FROM notes ORDER BY id DESC");

$notes = '';
$left='';
$top='';
$zindex='';

while($row=mysql_fetch_assoc($query)){
    list($left,$top,$zindex) = explode('x',$row['xyz']);
    $notes.= '
        <div class="note '.$row['color'].'" style="left:'.$left.'px;top:'.$top.'px;z-index:'.$zindex.'">
            '.htmlspecialchars($row['text']).'
            <div class="author">'.htmlspecialchars($row['name']).'</div>
            <span class="data">'.$row['id'].'</span>
            <a id="remove_note" href="javascript:;" onclick="deleteNote('<? echo $row['id']; ?>');">&nbsp;</a>
        </div>';
}
function deleteNote(id){
    $sql="DELETE FROM notes WHERE id='$rows['id']'";
    $result=mysql_query($sql) or die("Error when tryin to delete note.");
}
?>

Update:

I've been playing around with this and the answers that both Andrew and sachleen have provided. And ill plan to work on an AJAX alternative since you've mentioned the whole SQL Injection issue. But I am still having issues with passing the id to the remove.php file. I believe is has to do with how $notes is creating the information from the DB.

I say this because I get: Parse error: syntax error, unexpected T_STRING in /home/avonamyd/public_html/projects_php/sticky_notes/demo.php on line 24

And that is only when I include the code as is from sachleen. But when I update it to account for the single quotes I have the following code. The id is present and is passed to the remove.php file but I am still getting an error. This is when I use my code or what you've provided.

        $notes.= '
        <div class="note '.$row['color'].'" style="left:'.$left.'px;top:'.$top.'px;z-index:'.$zindex.'">
            '.htmlspecialchars($row['text']).'
            <div class="author">'.htmlspecialchars($row['name']).'</div>
            <span class="data">'.$row['id'].'</span>
            <a id="remove_note" target="_blank" href="remove.php?id='.$row['id'].'">&nbsp;</a>
        </div>';

Below is what I currently have in my remove.php file:

<?php
include 'connect.php';
$_GET['id'];
function deleteNote($id){
    $sql="DELETE FROM notes WHERE id='$id'";
}
    $result=mysql_query($sql) or die("Error when tryin to delete note.");

?>

Update

I've added in additional echo lines throughout the remove.php and this is what I am coming up with.

<?php
include 'connect.php';
$_GET['id'];
echo  $id; --doesnt show
function deleteNote($id){
    echo "hello"; --doesnt show
    $sql="SELECT FROM notes WHERE id='$id'";
}
echo  'hello2'; --shows
$result=mysql_query($sql) or die("Error when tryin to delete note.");

?>

Update: Thank you for everyone's help with this project I've finally gotten the concepts to click in my head after some tinkering around. I will post the functional code below for anyone else that stumbles upon this code. =D Thx Everyone!

demo.php

    error_reporting(E_ALL^E_NOTICE);
require 'connect.php';
mysql_query("DELETE FROM notes WHERE id>3 AND dt<SUBTIME(NOW(),'0 1:0:0')");
$query = mysql_query("SELECT * FROM notes ORDER BY id DESC");

$notes = '';
$left='';
$top='';
$zindex='';

while($row=mysql_fetch_assoc($query)){
    list($left,$top,$zindex) = explode('x',$row['xyz']);
    $id = $row['id'];
    $notes.= '
        <div class="note '.$row['color'].'" style="left:'.$left.'px;top:'.$top.'px;z-index:'.$zindex.'">
            '.htmlspecialchars($row['text']).'
            <div class="author">'.htmlspecialchars($row['name']).'</div>
            <span class="data">'.$row['id'].'</span>
            <a id="remove_note" target="_blank" href="remove.php?id='.$row['id'].'">&nbsp;</a>
        </div>';
}

remove.php

<?php
include 'connect.php';
$id = intval($_GET['id']);
$sql="DELETE FROM notes WHERE id=$id";
$result = mysql_query($sql) or die("Unable to delete database entry.");

?>

Answer 1

you have 2 options.

1) make an <a href="another_php_script.php?delete=true"> (or similar), then run the delete script. (then header back to the same page you were on).

This is because you cannot run an onClick php function, you have to redirect to the other page.

2) You can use the onclick function to call an AJAX script, and execute the deleting PHP script from the page you're on - without redirecting.

Option 1 is the easy option, Option 2 is the better option to learn from.

Answer 2

It looks like you are trying to mix JavaScript and PHP. You cannot call the deleteNote() function when your link is clicked because it is a PHP function. There are a couple of ways to go about calling the PHP script to delete the note:

Use something like the following:

<?php
// ...
$id_to_delete = $_GET['id'];
if( isset($id_to_delete) ) {
    $sql="DELETE FROM notes WHERE id='$id_to_delete'";
    $result=mysql_query($sql) or die("Error when tryin to delete note.");
}
$query = mysql_query("SELECT * FROM notes ORDER BY id DESC");

//...

while($row=mysql_fetch_assoc($query)){
   //...
   echo '<a id="remove_note" href="CURRENT_SCRIPT_URL?id=' . $id_to_delete . '">X</a>';
   //...
}
?>

Or you could create a second script that deletes a row from the database based on the data that you pass to it and use ajax (I would recommend using jQuery for ajax functionality) to call that script with the id of the item to delete.

Remember that anyone could call your script with a GET parameter and delete a record from the database (or worse, perform an SQL injection attack), so make sure that you have some sort of safeguard in place unless you want all of your records wiped out!

Answer 3

You can't onclick a PHP function. You're mixing JavaScript with PHP. I would do this:

<a id="remove_note" href="remove.php?id=<?php echo $row['id']; ?>">Remove</a>

And then on remove.php get the ID using $_GET['id'] and pass that into the DELETE query.