Deleting a MySQL row using php is not working and does not post the error

PHP
1

I am trying to delete a row based on the unique user_id. I have done a var_dump($_POST); to verify that I have the proper information. I convert the $_POST variables to $uid, $fn and $ln.


 if (isset($_POST['submitted'])) { // Handle the form.
	$uid = $_POST['user_id'];
	$fn = $_POST['first_name'];
	$ln = $_POST['last_name'];
echo $uid; // I see the correct $uid here
    } else {
	echo 'There was an error. <br /> Please return and select a  record for <a href="delete_list.html">deletion</a><br />';
}
	$query = "DELETE FROM users_tbl WHERE user_id= '$uid' LIMIT 1";
	echo $query; // I see the query with my test $uid
			$result = mysql_query($query) or trigger_error("Sorry an error occurred and the account could not be deleted");

I do not get either of the errors I have put in the code. All I get on my page is the dump and the echos I have marked as receiving.

The user_id is the primary key. If I change the WHERE to be primarykey= ‘$uid’ then I the trigger_error, so suspected that using primarykey was not the correct thing in this query.

2

Hi CSU-Bill,
Could you send the html code for the form?

3

Does the user which you are trying to delete exist with the specified $uid? Posting users_tbl SQL dump and the HTML of the form would help.

4

If I change the WHERE to be primarykey= ‘$uid’ then I the trigger_error, so suspected that using primarykey was not the correct thing in this query.

Yes, it was not the right thing to do.

I like the way you asked your question, shows you really thought about it.

The next test (to divide “is it my PHP?” or “is it my SQL?”) is to copy the echoed query and paste it straight into your database.

Did it work?

Obviously with a primary key removal, you will always have to pick another number …

If the query was good, then you should look at the last line of your mysql log file, and see if the query (which you established was good) and see if PHP pushed it to mysql. If it did not, then there must be something wrong with your database connection, you forgot to invoke it or whatever.

Couple of other things:


if (isset($_POST['submitted'])) { // Handle the form.

I prefer not to rely on that, because on some browsers if the user submitted the form with a Keyboard “Enter” the form is submitted but the submit button value is not. Unlike if the user presses the designated submit button.

You do not show where you are escaping the values before putting them into your database, which is a security risk.

If $uid is a unique numerical key then you can easily typecast it to an integer, and you do not need to surround it with quotes either.


    $uid = (int)$_POST['user_id']; 

...

    $query = "DELETE FROM users_tbl WHERE user_id= $uid LIMIT 1";

All the other values need escaping – this is one method:


    $uid = (int)$_POST['user_id']; 
    $fn = mysql_real_escape_string($_POST['first_name']);
5

Here are the forms I am using. First, I collect the information and pass it to a verify page.

<form action="delete_users_verify.html" method="post">
	
	<p><input type="hidden" name="user_id" value="<?php if (isset($_POST['user_id'])) echo $_POST['user_id']; ?>" /></p>
	
	<p><input type="hidden" name="first_name" value="<?php if (isset($_POST['first_name'])) echo $_POST['first_name']; ?>" /></p>
	
	<p><input type="hidden" name="last_name" value="<?php if (isset($_POST['last_name'])) echo $_POST['last_name']; ?>" /></p>
	
	<div style="text-align:left;"><p><input style="background-color:#FF0000;" type="submit" name="submit" value="DELETE" /></p><br /></div>

	<div><input type="hidden" name="submitted" value="TRUE" /></div>

</form>

The verify form is:

<form action="delete_user.html" method="post">

	<p>Are you sure you want to delete ' . $fn . ' ' . $ln .'? </p>
	<div><input type="hidden" name="user_id" value=" ' . $uid . '"/></div>
	<div><input type="hidden" name="first_name" value=" ' . $fn . '"/></div>
	<div><input type="hidden" name="last_name" value=" ' . $ln . '"/></div>
	<div><input type="submit" name="submit" value="YES" /></div>
	<div><input type="hidden" name="submitted" value="TRUE" /></div>
	<p><br /></p>

</form>

Once through the verify that a delete is really wanted, I pass to the code in the original post.

@cups

I will make the change to primary key and run the tests you suggest when I get back to my development computer later today. I know I am not using good security at this time. I am reading the information from the database, filling the forms and trying to delete my test user. Once I get the delete to work, I will insert the security.

The user table does not have a numeric ID. I am using a alpha-numeric ID as the login name. Should I change the database and have the numeric ID as well as the login name?

@jakub_polak

yes, the user exists, and I have verified that the information is in the post. I will provide that information later today.
I actually start with a display page that reads the database and fills in the data to hidden fields below the full display. Then have a RED delete button below a “delete $fn $ln” so that it is obvious that the intent is to delete firstname lastname. None of the data is user input.

6

Fair enough, its just force of habit to mention it esp for anyone else looking at your post.

No, I would not change it for that reason alone. As long as you are enforcing uniqueness on your ID you should be ok. I had imagined wrongly that the id was a number. You don’t need to quote numbers is all I was trying to stress.

I also see your “submitted” var is a hidden field not the name of a submit button, so that advice does not stand either.

OT

Tsk, I wish I had more coffee this morning before posting on here :expressionless:

7

I understand. Coffee is one of the required food groups. :slight_smile:

8

Here is the dump from the post:

array(5) { [“user_id”]=> string(6) “Test01” [“first_name”]=> string(6) “Tester” [“last_name”]=> string(6) “Tested” [“submit”]=> string(6) “DELETE” [“submitted”]=> string(4) “TRUE” }

The above is a dump of all variables in POST.

Are you sure you want to delete Tester Tested?

When I click on the button, the next script post looks like:

array(5) { [“user_id”]=> string(7) " Test01" [“first_name”]=> string(7) " Tester" [“last_name”]=> string(7) " Tested" [“submit”]=> string(3) “YES” [“submitted”]=> string(4) “TRUE” }

And I get an error from the server:

Notice: Sorry an error occurred and the account could not be deleted in /delete_user.html on line 55

This error only shows up when I am using primarykey= in the query.

Off to do some more testing.

9

@cups

Thank you for suggesting I enter the code directly in SQL. As part of my testing, i have been echoing my query. Being lazy, I copied and pasted directly from the echo on my screen. It did not delete the record.

I did notice that it did not look right.

DELETE FROM users_tbl WHERE user_id=’ Test01’ LIMIT 1

Once I removed the SPACE at the front of Test01, the delete did work. Now I need to figure out how the space was entered into the user_id of ’ Tester01’ and then eliminate the error. I notice that on my first data dump, I had:

[“user_id”]=> string(6) “Test01” [“first_name”]=> string(6) “Tester”

while on the next data dump, I had:

[“user_id”]=> string(7) " Test01" [“first_name”]=> string(7) " Tester"

I have done something to add a space at the beginning of my variables.

10

I found the problem.

<form action="delete_user.html" method="post">

    <p>Are you sure you want to delete ' . $fn . ' ' . $ln .'? </p>
    <div><input type="hidden" name="user_id" value=" ' . $uid . '"/></div>
    <div><input type="hidden" name="first_name" value=" ' . $fn . '"/></div>
    <div><input type="hidden" name="last_name" value=" ' . $ln . '"/></div>
    <div><input type="submit" name="submit" value="YES" /></div>
    <div><input type="hidden" name="submitted" value="TRUE" /></div>
    <p><br /></p>

</form>

Each of the value entries has value="_’ (I added the line to show the space. Removing that space solved my problem.

Now I can add some security to my data and move on to the next time on my project.

11

why u discuss this much for this small problem check the code what he posted properly first its like this right
$query = “DELETE FROM users_tbl WHERE user_id= ‘$uid’ LIMIT 1”;

but it should be like this
$query = “DELETE FROM users_tbl WHERE user_id= ‘.$uid.’ LIMIT 1”; thts it
delete will work

thank you have a nice day…:slight_smile:

12 
$query = "DELETE FROM users_tbl WHERE user_id= '.$uid.' LIMIT 1";

If $uid is for example 1, then this query will result in:

DELETE FROM users_tbl WHERE user_id= '.1.' LIMIT 1

If you try to run this against database then it will end in SQL error, that means, that your answer is wrong.

13

Maybe your user doesn’t have permission to delete rows from the MySQL database?