mysql DELETE not working

[nathanpacker]

Hey everyone, I'm using php to delete a row from a table in a database in mysql, but it's not working. I'm not even trying to do anything fancy, just delete one row. Here's the code:

<?
include 'config.php';

$recordid = $_POST['recordid'];

echo $recordid;

mysql_query("DELETE FROM employees WHERE recordid='$recordid'")
or die('sorry, no query');


echo "You have successfully deleted the user."


?>

The database is connecting properly, I double checked that. The variable $recordid is echoing out properly, and I even tried not using the variable, and just placing the actual number of the record id in the code instead, but still didn't work. And yes, I did try copying and pasting that code directly in to the mysql command line. Works perfectly no errors. So how come the query never works? The query dies every time, no fail, but works every time when I use the exact same command in the mysql command line, straight copy and paste, no kidding.

Any ideas? I know it's going to be something stupid, and I'm going to kick myself when I find out, but it's killing me!

[sn4rf3r]

change your query to this:

mysql_query("DELETE FROM employees WHERE recordid='".$recordid."'")

you cant wrap a variable in single quotes as it will be trated as literal.

[Puckdropper]

Check for errors with mysql_errorno or a similar function. That might help you out some.

[nathanpacker]

change your query to this:

mysql_query("DELETE FROM employees WHERE recordid='".$recordid."'")

you cant wrap a variable in single quotes as it will be trated as literal.

Thanks, but that doesn't seem to work either. Plus, I'm pretty sure I've done mysql_query's before with variables in single quotes and it works just fine. And besides, I'v even tried just putting the actual record id in that space, and it doesn't work. For instance, I put:

mysql_query("DELETE FROM employees WHERE recordid='8'")

And it still doesn't work. I don't understand!

[nathanpacker]

Check for errors with mysql_errorno or a similar function. That might help you out some.

How exactly do you use mysql_errorno? I can't find any documentation on it. I even looked on http://dev.mysql.com and couldn't find it. I did try mysql_error, and it spit this error out:

Access denied for user: 'user@10.0.0.1' to database 'mydatabase'

I changed the username, IP, and database name, just in case you're wondering. The problem is, I'm not having problems connecting to the database. the config.php file contains a mysql_error that should tell me if there were any connection problems. And the user that this error is telling me can't get access is the same user getting access in all my other scripts. And they're not having any problems. This is really buggin.

so be carefull while using keywords. it's dangerous.

[nathanpacker]

so be carefull while using keywords. it's dangerous.

:?: What?

[DGStudios]

One thing you might try is to store you're query in a variable before putting it into the mysql_query() function.

$query = "DELETE FROM employees WHERE recordid='$recordid'";
$results = @mysql_query($query);

Mind You I used the storing of results. This is good practice as it allows for good error checking. I use it all the time, as queries ALWAYS return a value. while Selects and whatnot actually return values the others return a true/false value set.... or at least false when they fail.

Try it that way and see if that fixes it. If not.. take the query into a database interface and run the query. You might have to echo the query out to get the exact query, and just run it. That will give you a better understanding of exactly WHERE the issue is in the query.

[sn4rf3r]

just looks like he cant connect to the database from where he is.

mysql> select * from mysql.user where user = 'username'
\G;
mysql> GRANT ALL PERMISSIONS ON *.* TO 'joe'@'10.0.1.1' IDENTIFIED BY 'password'

[nathanpacker]

One thing you might try is to store you're query in a variable before putting it into the mysql_query() function.

$query = "DELETE FROM employees WHERE recordid='$recordid'";
$results = @mysql_query($query);

Mind You I used the storing of results. This is good practice as it allows for good error checking. I use it all the time, as queries ALWAYS return a value. while Selects and whatnot actually return values the others return a true/false value set.... or at least false when they fail.

Try it that way and see if that fixes it. If not.. take the query into a database interface and run the query. You might have to echo the query out to get the exact query, and just run it. That will give you a better understanding of exactly WHERE the issue is in the query.

Thanks, I'll give that a try.

[nathanpacker]

Well, that didn't work either. I don't understand, the syntax is correct, I've searched far and wide on the DELETE mysql command, and they all say this is the way to do it, and for some reason it's just not working. Grrr.:mad: :mad: :mad:

[sn4rf3r]

what is the error you are getting? also the example delete from DGStudios will not work since you are wrapping the variable in single quotes and will be treated as literal. you need to do something like this:

$query = "DELETE FROM employees WHERE recordid='".$recordid."'";
$results = @mysql_query($query);

see how you end the string, concatenate it and resume it around the variable?

[nathanpacker]

what is the error you are getting? also the example delete from DGStudios will not work since you are wrapping the variable in single quotes and will be treated as literal. you need to do something like this:

$query = "DELETE FROM employees WHERE recordid='".$recordid."'";
$results = @mysql_query($query);

see how you end the string, concatenate it and resume it around the variable?

I've already tried that. And besides, if you'll notice in my original post, I even tried putting an actual number for the record id (one that actually exists in the database), rather than using a variable. In fact I am still doing it that way at least until I get the command working, then I will try puting a variable in there.

And I can't figure out what error I'm getting. So far I'm just getting out the message I specified with the "die". And someone mentioned I try using "mysql_errorno ", but I've stated that I don't know how to use that, and can't find any documentation on how to use it. And if you read earlier in the posts, you'll see what error I got when just using mysql_error. It said it wasn't able to connect to the database. But again, that cannot be the problem, because all my other scripts are connecting to the database just fine, and are using the exact same configuration script.

Everyone seems to be trying to help me fix the actual DELETE command. But I am pretty sure it is correct. I have copied and pasted the exact command into the mysql command box, and it works just fine. So for some reason, the script just isn't able to execute that command.

[Puckdropper]

Your script can't access the database, so let's try to fix that. Um, let's go through a checklist:
1. Is your username and password correct?
2. Are the servers correct?
3. Database exists, permissions are correct?

The function I was thinking of was mysql_errno , but a search on php.net for mysql_errorno brought up a list which had the right one.

[nathanpacker]

Your script can't access the database, so let's try to fix that. Um, let's go through a checklist:
1. Is your username and password correct?
2. Are the servers correct?
3. Database exists, permissions are correct?

The function I was thinking of was mysql_errno , but a search on php.net for mysql_errorno brought up a list which had the right one.

Thanks for your help Puckdropper, but I'm pretty sure the database connection isn't the issue. I'm not sure why I'm getting that error. If you look at my script, I'm including a file called config.php at the very beginning of the script. (I'll post the script below again, just so you can see.) I include this exact same config.php file in all my scripts, and none of my other scripts are having problems. I'm running at least 10 to 15 other scripts with that same file, and not having any database connection issues. So how can that be the problem? Here is the script again:

<?
include 'config.php';

$recordid = $_POST['recordid'];
$email = $_POST['email'];
$username = $_POST['username'];
$name=$_POST['name'];

$query = "DELETE FROM employees WHERE RECORDID='1'";
$result = mysql_query($query)
or die("Query Failed");


header("Refresh: 3; URL=deleteuser.php");
echo "<center>You have successfully deleted the user $name.<br>
Click <a href=\"deleteuser.php\">here</a> if you are not redirected in 3 seconds.</center>";

 ?>

Right now I'm going to go try the mysql_errno that you gave me, and see what that puts out.
Thanks again!

[nathanpacker]

Alright, I finally got the mysql_errno to output a code. It returned code 1044, which when I looked it up, it indeed does seem to have something to do with the user not having the proper permissions to do whatever it is I'm doing to the database. Here is the code (I deleted the variables and other stuff, cause I just have them commented out anyway. I've cut the script down to the bare minimum to work):

<?
include 'config.php';

$query = "DELETE FROM employees WHERE RECORDID='1'";
$result = mysql_query($query)
or die("Error number:" . mysql_errno() . "");


 ?>

So that may explain why all my other scripts work with this config.php file, but not this script. Maybe the user I'm using to access the database with has all the permissions I need except to delete an entire row. After all, this is the only script among all my scripts that attempts to delete an entire row. So maybe you all were right about the permissions thing. Since I can't change the permissions on that user, I'll try using a different user. If that doesn't work, I'll have to have my boss change the permissions of that user and then try it out. Thanks so much for all your help, I'll let you know when I make more headway!

Edit: I tried with a different user, but wasn't able to get that to work. I'll get my boss to fix the permission on the user, then I'll try again. It seems that this may be the problem!

[Puckdropper]

Have you tried the delete command in one of the scripts that's working?

The mysql_errno just gives you the error code (but not the text.) PHP.net has excellent documentation on such things, it's worth looking things up with.

[nathanpacker]

Have you tried the delete command in one of the scripts that's working?

The mysql_errno just gives you the error code (but not the text.) PHP.net has excellent documentation on such things, it's worth looking things up with.

That is a good idea, maybe I'll try that. And I was able to find what the 1044 code meant on php.net. It meant that the user didn't have the permissions to execute that command. That's why I'm thinking this script is having a problem, cause it's the only one trying to delete a whole row. So maybe I will try sticking it in another script and try it out.
Thanks.

[hazel_0821]

Thanks for your help Puckdropper, but I'm pretty sure the database connection isn't the issue. I'm not sure why I'm getting that error. If you look at my script, I'm including a file called config.php at the very beginning of the script. (I'll post the script below again, just so you can see.) I include this exact same config.php file in all my scripts, and none of my other scripts are having problems. I'm running at least 10 to 15 other scripts with that same file, and not having any database connection issues. So how can that be the problem? Here is the script again:

<?
include 'config.php';
 
$recordid = $_POST['recordid'];
$email = $_POST['email'];
$username = $_POST['username'];
$name=$_POST['name'];
 
$query = "DELETE FROM employees WHERE RECORDID='1'";
$result = mysql_query($query)
or die("Query Failed");
 
 
header("Refresh: 3; URL=deleteuser.php");
echo "<center>You have successfully deleted the user $name.<br>
Click <a href=\"deleteuser.php\">here</a> if you are not redirected in 3 seconds.</center>";
 
 ?>

Right now I'm going to go try the mysql_errno that you gave me, and see what that puts out.
Thanks again!

this the code that works:

<?php
$con = mysql_connect("localhost","root");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}mysql_select_db("employees", $con);
$qu=$_GET;
mysql_query("DELETE FROM employee_data WHERE f_name like '%$qu%'",$con);
mysql_close($con);
echo "data deleted"
?>

[kegham]

change your query to this:

mysql_query("DELETE FROM employees WHERE recordid='".$recordid."'")

you cant wrap a variable in single quotes as it will be trated as literal.

Thank you my friend. This piece of code with those double quotes '".$recordid."'" helped me so much, since i was using for example: '$recordid' and it was not deleting it. Peace.

:)

[njagi]

Try using this approach
$sql = "DELETE FROM employees WHERE recordid='$recordid'";
$theresult = mysql_query($sql) or die("query could not be completed");

[Abhishek Anand]

Use this query
$query="delete from employees where recordid = '".$recordid."' ";
mysql_query($query):

Thanks,
Abhishek Anand.

[nav33n]

This thread is more than an year old! :)