Delete record in MySQL with php targeting auto_incremented int?

Question

Why doesnt this delete work to delete the whole record:

$query = 'DELETE FROM tblEvents WHERE index = $_GET["id"]';
$result = mysql_query($query, $db) or die(mysql_error($db));

Where index is variable of type int, auto_incremented in MySQL?

Answer 1

Your question php is related, not mysql.
print $query; and see.
then refer to php strings syntax, http://php.net/types.string for the proper syntax.

Also, a variable that goes to the query, must be properly prepared, escaped, or, in case of integer value, manually cast to this type,

$id=intval($_GET["id"]);

or, to make it single line,

$query = 'DELETE FROM tblEvents WHERE `index` = '.intval($_GET["id"]);

also, index is reserved word that can cause problems too, you can escape it with backticks,

`index`

but it will be much better if you rename it to just id

Answer 2

Col. Shrapnel is right, you can't use variables directly in a string in single quotes. If you use double quotes around your query, it will work.

EDIT: As Col. Shrapnel said in his comment, in this case you'll also have to change the double quotes in the array offset to single quotes.

Answer 3

You should test for delete success with a separate query

$query = 'DELETE FROM tblEvents WHERE index = $_GET["id"]';
mysql_query($query, $db);
if( mysql_affected_rows < 1 ) die();

Answer 4

Hopefully you already know this, but you need to secure that $_GET['id'] so people can't do SQL Injection. Try using the following instead:

$query = sprintf('DELETE FROM tblEvents WHERE index = %d',mysql_real_escape_string($_GET['id']));

This also solves your problem of using a variable in single quotes instead of double quotes.

if you wanted you could also do:

$id = mysql_real_escape_string($_GET['id']);
$query = "DELETE FROM tblEvents WHERE index = {$id}";

This works too.