Use a simple form and PHP to delete a mySQL record

Question

I am trying to build a page that will allow the user to enter an employee number via a form and when they hit the "delete" button it will remove the corresponding record. The database is named "Crosshill", the Table is called "Employees" and the field I want to use is "employeeid".

It seems to connect fine to the DB, but the code below doesn't work. When you hit the "Delete" button it returns an error of:

Could not delete data: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE employeeid =' at line 1 Blockquote

<html>
<head>
<title>Delete an Employee</title>
</head>
<body>

<h3>Enter the Employee Number below to delete a record</h3>

<?php
if(isset($_POST['delete']))
{
$dbhost = '####';
$dbuser = '####';
$dbpass = '####';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}

$employeeid = $_POST['employeeid'];

$sql = "DELETE Employees ".
       "WHERE employeeid = $employeeid" ;

mysql_select_db('Crosshill');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not delete data: ' . mysql_error());
}
echo "Deleted data successfully\n";
mysql_close($conn);
}
else
{
?>
<form method="post" action="<?php $_PHP_SELF ?>">
<table width="400" border="0" cellspacing="1" cellpadding="2">
<tr>
<td width="100">Employee ID</td>
<td><input name="employeeid" type="number" id="employeeid"></td>
</tr>
<tr>
<td width="100"> </td>
<td> </td>
</tr>
<tr>
<td width="100"> </td>
<td>
<input name="delete" type="submit" id="delete" value="Delete">
</td>
</tr>
</table>
</form>
<?php
}
?>
</html>

Missed FROM word. Writing `$employeeid = (int)$_POST['employeeid'];` will fix this security hole) But I hope this is test purpose only code... — vp_arth, Dec 15 '13 at 19:27
Could you elaborate? This is just for a test project, but I'm still learning and would like to know how to prevent that — WJB, Dec 15 '13 at 19:29
@WJB: [This question](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) explains this in detail. — Amal Murali, Dec 15 '13 at 19:33
you always should sanitize your input, for example if I provide 'employeeid' value instead a numeric id SQL will `DELETE FROM Employees WHERE employeeid = employeeid` So we lost all employees... — vp_arth, Dec 15 '13 at 19:33
Thanks very much to all of you! It is working now. I'll add points as soon as my rep allows. Appreciate the help and input! — WJB, Dec 15 '13 at 19:40
This form can delete your entire **database**. Please, be very careful to **always** [properly escape your data](http://bobby-tables.com/php) to avoid [SQL injection bugs](http://bobby-tables.com/). This sort of mistake is the reason `mysql_query` is being removed from PHP in future versions so it's best to avoid using it. A more modern replacement like [PDO is not hard to learn](http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/) and makes doing this correctly a lot easier. — tadman, Dec 15 '13 at 19:43
If you're having trouble with these fundamentals, what you really need is a [development framework](http://codegeekz.com/best-php-frameworks-for-developers/) that will do a lot of this for you. The super low-level approach you're taking here fell out of fashion ten years ago. — tadman, Dec 15 '13 at 19:44

score 6 · Answer 1 · answered Dec 15 '13 at 19:22

6

It's DELETE FROM <table> WHERE <condition>, the FROM is missing in your query.

answered Dec 15 '13 at 19:22

BluePsyduck

1,111
9
9

Thanks very much! It is working now. I'll add points as soon as my rep allows. Appreciate the help! – WJB Dec 15 '13 at 19:38

score 0 · Answer 2 · answered Dec 15 '13 at 19:28

0

You are missing "from" after delete.. It should be as DELETE from Employees WHERE condition.

To avoid such situations always do one thing, just echo the sql query and using "exit" after the same to terminate the further execution of the program.

Copy the query from browser and run the same in phpmyadmin or whatever other tool you use..

That practice will help you to find out the root cause of the problem..

answered Dec 15 '13 at 19:28

sandeep Kumar

121
1
8

Thanks very much! It is working now. I'll add points as soon as my rep allows. Appreciate the help! – WJB Dec 15 '13 at 19:38

Use a simple form and PHP to delete a mySQL record

2 Answers2