How to delete a row from mysql via php

Question

Brushing up on php and working on a simple program where I've run into an issue. I can't seem to figure out how to delete a mysql row. I will link my script in a pastie document so you can see how I have it set up.

I'm not familiar with AJAX or Javascript.. so I just made the delete button a form. I'd like to keep it like this for now if I can make it work.

PASTIE HERE

The script is only 45 lines of code. You want me to paste the whole thing here instead of my pastie link? — Brandon, Aug 14 '12 at 04:22
You asked how to delete a row from mysql. You could include just the one or two relevant lines, then the question because useful for everyone, even if pastie isn't available. — Hamish, Aug 14 '12 at 04:23
Make sure you are cleaning your post data for SQL injection! Or use binding with PDO. — Revent, Aug 14 '12 at 04:24
Thank you, I sure will. Trying to see if I can get it funcional first then will add security. — Brandon, Aug 14 '12 at 04:28

Nir Alfasi · Answer 1 · 2012-08-14T04:36:08.697

2

change:

mysql_query("DELETE FROM name WHERE name=.'$del'.");

to:

mysql_query("DELETE FROM name WHERE name='".$_POST['$del']."'");

becuase:
1. you should get rid of the . inside the query, dot is used for string concatenation.
2. you want to use the value of $_POST['$del'] - the parameter $del is not set

Updates:

change <input type="hidden" name="del" /> to: <input type="hidden" name="del" value="theNameYouWantToDelete"/>
you give the same name to all the form elements (name="del") - this is not recommended! better set a different name to each object.
please do not use mysql_* - it's deprecated and vulnerable to sql-injection, use PDO or MySQLi instead.

edited Aug 14 '12 at 04:36

answered Aug 14 '12 at 04:22

Nir Alfasi

53,191
11
86
129

He would also need to pass relevant data with `$_POST['del']`. Which he isn't. – Blake Aug 14 '12 at 04:23
@Brandon - please try again :) – Nir Alfasi Aug 14 '12 at 04:25
No parse error this time. But the row still exists in the table unfortunately. – Brandon Aug 14 '12 at 04:27
That's because `$_POST['del']` has no data... like I said. – Blake Aug 14 '12 at 04:28
@Brandon - please echo the value of `$_POST['del']` before the query runs – Nir Alfasi Aug 14 '12 at 04:30
@brandon On line number 41 in given link you are creating the hidden field with no value. – Rupesh Pawar Aug 14 '12 at 04:33
@Brandon I just updated my answer with Rupesh's comment, he's right - you're submitting the page with null value in `$_POST['del']` – Nir Alfasi Aug 14 '12 at 04:37

Blake · Answer 2 · 2012-08-14T04:25:59.480

0

You're not properly concatenating the $del variable. You could simply use:

mysql_query("DELETE FROM name WHERE name='$del'");

Also, you need to set $del before the DELETE query. That variable hasn't been declared before you tried to use it, so it will be null.

edited Aug 14 '12 at 04:25

answered Aug 14 '12 at 04:20

Blake

1,691
2
15
23

Blake, could you instruct me how to get this working then? I declared $del in the while loop but I guess I can't access it outside of that loop. – Brandon Aug 14 '12 at 04:29

score 0 · Accepted Answer · answered Aug 14 '12 at 04:34

0

On line 30 you have two inputs named del, and the second one does not contain the name to delete

echo '<form id="del" method="post"><input type="submit" name="del" value="X" /><input type="hidden" name="del" /></form>';

Need to change to something like -

echo '<form id="del" method="post"><input type="submit" name="del_submit" value="X" /><input type="hidden" name="del" value="'.$del.'" /></form>';

Then change lines 12-13 to

if (isset($_POST['del_submit'])) {
mysql_query("DELETE FROM name WHERE name='".$_POST['del']."'");

Please note that mysql_ functions are depreciated, and you are subject to sql injection.

answered Aug 14 '12 at 04:34

Sean

12,443
3
29
47

Sean, this worked simply and perfectly. Thank you for your input. – Brandon Aug 14 '12 at 04:38
Sean, could you provide me with a link on switching my mysql functions to PDO or something more secure? I wasn't aware they were depreciated. – Brandon Aug 14 '12 at 04:48
You can look at [http://www.php.net/manual/en/mysqlinfo.api.choosing.php](http://www.php.net/manual/en/mysqlinfo.api.choosing.php) – Sean Aug 14 '12 at 05:00
Sean, here is my revised code. I've converted it to mysqli. Could you tell me if this is more secure, and other ways to improve it? I'd really appreciate it! http://pastie.org/4471333 – Brandon Aug 14 '12 at 05:15
Yes, it is more secure. Noticed that when you changed to mysqli you removed `$_POST['del']` from line 13, so now `$del` in `$con->query("DELETE FROM name WHERE name='$del'");` is not set. Make sure to add `$del = $_POST['del']` before line 13. Also make sure this page is only accessible to those who you want to be able to delete names. – Sean Aug 14 '12 at 05:36
Well I should of been clearer. I guess other ways you could advise to improve security.. would it be necessary to use "mysql_real_escape_string" since I'm using mysqli now? Those types of things. And the script works perfectly as it is now.. should I add `$del = $_POST['del']` back in even though it's working perfectly? – Brandon Aug 14 '12 at 05:41
If it is working, no need to add it back in. I am also learning `mysql_*` to `mysqli_*`, so I cannot provide the best help on security/sanitizing. Here are 2 links to look at - [http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) and [http://stackoverflow.com/questions/3327974/when-to-sanitize-php-mysql-code-before-being-stored-in-the-database-or-when-it](http://stackoverflow.com/questions/3327974/when-to-sanitize-php-mysql-code-before-being-stored-in-the-database-or-when-it) – Sean Aug 14 '12 at 05:50

How to delete a row from mysql via php

3 Answers3