PHP will not delete from MySQL

Question

For some reason, JavaScript/PHP wont delete my data from MySQL! Here is the rundown of the problem.

---

I have an array that displays all my MySQL entries in a nice format, with a button to delete the entry for each one individually. It looks like this:

<?php

            include("login.php");

        //connection to the database
        $dbhandle = mysql_connect($hostname, $username, $password)
         or die("<br/><h1>Unable to connect to MySQL, please contact support at [email protected]</h1>");

        //select a database to work with
        $selected = mysql_select_db($dbname, $dbhandle)
          or die("Could not select database.");

        //execute the SQL query and return records
        if (!$result = mysql_query("SELECT `id`, `url` FROM `videos`"))
        echo 'mysql error: '.mysql_error();

        //fetch tha data from the database
        while ($row = mysql_fetch_array($result)) {
           ?>

       <div class="video"><a class="<?php echo $row{'id'}; ?>" href="http://www.youtube.com/watch?v=<?php echo $row{'url'}; ?>">http://www.youtube.com/watch?v=<?php echo $row{'url'}; ?></a><a class="del" href="javascript:confirmation(<? echo $row['id']; ?>)">delete</a></div>

<?php }

//close the connection
mysql_close($dbhandle);
?>

The delete button has an href of javascript:confirmation(<? echo $row['id']; ?>) , so once you click on delete, it runs this:

<script type="text/javascript">
<!--
function confirmation(ID) {
    var answer = confirm("Are you sure you want to delete this video?")
    if (answer){
        alert("Entry Deleted")
        window.location = "delete.php?id="+ID;
    }
    else{
        alert("No action taken")
    }
}
//-->
</script>

The JavaScript should theoretically pass the 'ID' onto the page delete.php. That page looks like this (and I think this is where the problem is):

<?php

include ("login.php");

mysql_connect($hostname, $username, $password)
 or die("Unable to connect to MySQL");

mysql_select_db ($dbname)
or die("Unable to connect to database");

mysql_query("DELETE FROM `videos` WHERE `videos`.`id` ='.$id.'");
echo ("Video has been deleted.");
?>

If there's anyone out there that may know the answer to this, I would greatly appreciate it. I am also opened to suggestions (for those who aren't sure).

Thanks!

Answer 1

In your delete.php script, you are using this line :

mysql_query("DELETE FROM `videos` WHERE `videos`.`id` ='.$id.'");

The $id variable doesn't exists : you must initialize it from the $_GET variable, like this :

$id = $_GET['id'];

(This is because your page is called using an HTTP GET request -- ie, parameters are passed in the URL)

Also, your query feels quite strange : what about this instead :

mysql_query("DELETE FROM `videos` WHERE `videos`.`id` = '$id' ");

ie, removing the '.' : you are inside a string already, so there is nothing to concatenate (the dot operator in PHP is for concatenation of strings)

Note :

• if this works on some server, it is probably because of register_globals
  • For more informations, see Using Register Globals
  • But note that this "feature" has been deprecated, and should definitely not be used !
    • It causes security risks
    • And should disappear in PHP 6 -- that'll be a nice change, even if it breaks a couple of old applications
• your code has a big SQL injection hole : you should sanitize/filter/escape the $id before using it in a query !
  • If you video.id is a string, this means using mysql_real_escape_string
    • If you where using the mysqli or PDO extensions, you could also take a look at prepared statements
  • with an integer, you might call intval to make sure you actually get an integer.

So, in the end, I would say you should use something that looks like this :

$id = $_GET['id'];
$escaped_id = mysql_real_escape_string($id);
$query = "DELETE FROM `videos` WHERE `videos`.`id` = '$escaped_id'";
// Here, if needed, you can output the $query, for debugging purposes
mysql_query($query);