PHP MySQL delete where values are equal to

Question

I have a PHP chat script that calls a MySQL database when a user signs out to delete them from the database.

My script is:

  if(isset($_GET['logout'])){
    mysql_query("DELETE FROM users WHERE username='" .$user['username']. "' AND rank='0'");
    header("Location: login_mini.php?logout=1");
  }

What I want to do is delete the user if they have a rank of 0 when they leave. Why isn't this script working?

Echo your query, then try directly inputting the query into MySQL. — user1032531, Sep 30 '13 at 00:58
If you're trying to reference a value from an array it should be `username='{$user['username']}'`. Note the curly brackets and the quotes around the key. — ChicagoRedSox, Sep 30 '13 at 01:00
You said `What I want to do is delete anyone with the rank of 0 when they leave` which means you don't need to add `username` in WHERE statement , `rank=0` is enough.. your statement is very conflicting with your SQL statement that you want. Just my opinion — Drixson Oseña, Sep 30 '13 at 01:03
@ChicagoRedSox I just tried that and any entry is still deleted when the script is executed even if rank=4. Any ideas? — Callum Whyte, Sep 30 '13 at 01:03
Actually...wait a minute. How is the query even successfully running? `DELETE * FROM` is not valid as far as I know; it should just be `DELETE FROM`. — ChicagoRedSox, Sep 30 '13 at 01:07
@ChicagoRedSox I think it is valid, just tried both and still not working though. — Callum Whyte, Sep 30 '13 at 01:10
@DrixsonOseña won't it delete all users with `rank=0`? I think the OP wants to delete only a specific user with `rank=0` — Niket Malik, Sep 30 '13 at 01:12
@user2036031, really? have you tried running the query in phpmyadmin? Just run `DELETE * FROM users WHERE username='myusername' AND rank='0'` — Drixson Oseña, Sep 30 '13 at 01:13
It sounds like either there's something you're leaving out here (or just missing), or there's something very strange going on with your MySQL installation. This is a very basic, straightforward query and it doesn't make sense that it would be deleting rows that don't match. — ChicagoRedSox, Sep 30 '13 at 01:19
**By building SQL statements with outside variables, you are leaving yourself wide open to SQL injection attacks.** Also, any input data with single quotes in it, like a name of "O'Malley", will blow up your SQL query. Please learn about using parametrized queries, preferably with the PDO module, to protect your web app. http://bobby-tables.com/php has examples to get you started, and [this question](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has many examples in detail. — Andy Lester, Sep 30 '13 at 01:24
If someone puts in a username of `' OR 1=1; --` then you will delete every user. — Andy Lester, Sep 30 '13 at 01:25

score 2 · Answer 1 · answered Sep 30 '13 at 01:18

2

DELETE doesn't take column arguments

Remove the *

answered Sep 30 '13 at 01:18

Jason Fingar

3,358
1
21
27

Drixson Oseña · Answer 2 · 2013-09-30T01:18:32.507

1

The syntax for MYSQL Delete example:

DELETE FROM somelog WHERE user = 'jcole'
ORDER BY timestamp_column LIMIT 1;

So you're query is wrong that's the reason why it is not running:

It should be

//without * and add quotes in your $user['username']
    mysql_query("DELETE FROM users WHERE username=" .$user['username']. " AND rank='0'");

edited Sep 30 '13 at 01:18

answered Sep 30 '13 at 01:11

Drixson Oseña

3,631
3
23
36

+1 This is mostly a consolidation of all the comments but it's also the only complete and correct answer. – ChicagoRedSox Sep 30 '13 at 01:24
@user2036031 , have you tried running the query in mysql? if it's not running then my suggestion is to put the rest of your code. – Drixson Oseña Sep 30 '13 at 01:27
Have it `echo` the query: `"DELETE FROM users WHERE username='" .$user['username']. "' AND rank='0'"`. Connect to the database directly and paste that exact string. What happens? – ChicagoRedSox Sep 30 '13 at 01:28
1

Also, @DrixsonOseña, it looks like you removed the single quotes from the query on your edit - those should be in there since the column is presumably a varchar or other string type. – ChicagoRedSox Sep 30 '13 at 01:29
@user2036031 - `echo "DELETE FROM users WHERE username='" .$user['username']. "' AND rank='0'"` prints `1`? – ChicagoRedSox Sep 30 '13 at 01:33
Then I'm kind of at a loss. It sounds like there's a problem with your PHP installation if `echo` isn't even behaving the way it's supposed to. Even if `$user` was null, it should just output `username=''` along with the rest of the string. – ChicagoRedSox Sep 30 '13 at 01:40

score 1 · Answer 3 · answered Sep 30 '13 at 01:22

1

mysql_query("DELETE FROM users WHERE username='$user[username]' AND rank=0");

There is no * or any columns in DELETE operation because you are deleting the whole row(s).

answered Sep 30 '13 at 01:22

Paul Denisevich

2,329
14
19

score 1 · Answer 4 · answered Sep 30 '13 at 02:53

Are you sure that users table have a record that have rank == 0 ? Check it by

SELECT COUNT(*)
FROM users
WHERE rank='0'

then if there is check your variable $user['username'] if it has value.

var_dump($user);

then if both has value then try to execute this manually on your mysql

SELECT *
FROM users
WHERE username = #the value of the username
    AND rank = '0'

If there is a result then maybe your PHP is throwing an error while executing the mysql_query. try to insert this code after the mysql_query

if (mysql_error()) {
    die(mysql_error());
}

by the way for security reason enclosed your $user['username'] with mysql_real_escape(). this to protect your query from SQL Injection. — notmii, Sep 30 '13 at 02:55

PHP MySQL delete where values are equal to

4 Answers4