Cannot delete MySQL record using PHP

Question

Sorry for the badly phrased title, I don't know what else to call this situation.

Here's my dilemma, for fun I'm making a little doodad so people can submit the name, platform, and location of Battlefield 3 servers so they no longer need to rely on word of mouth.

This older code works fine, it deletes records and does it's job.

<?php

mysql_connect('localhost', 'root', 'pass');
mysql_select_db('database');

$server_secret = mysql_real_escape_string($_POST['secret_initial']);
$server_confirm = mysql_real_escape_string($_POST['secret_verify']);

if ($server_secret == $server_confirm) {
    echo "Both matched.";
    if ((strlen($server_secret) < 6) and (strlen($server_verify) < 6)) {
        echo "Password too short.";
    } else {
        echo "Password is of proper length.";
        $sql = "DELETE FROM `servers` WHERE secret='" . sha1($server_secret) . "'";
        mysql_query($sql) or die("Couldn't delete record. " . mysql_error());
    }
} else {
    echo "Mismatch.";
}

?>

However, if I use this:

<?php

mysql_connect('localhost', 'root', 'pass');
mysql_select_db('database');

// Create variables from form
$server_secret = mysql_real_escape_string($_POST['secret_initial']);
$server_confirm = mysql_real_escape_string($_POST['secret_verify']);

// Check for verification
if ($server_secret == $server_confirm) {
    // Delete from database
    $sql = "DELETE FROM `servers` WHERE secret='" . sha1($server_secret) . "'";
    if (mysql_query($sql) or die("Couldn't delete record. " . mysql_error()) {
        $success = true;
    } else {
        $success = false;
    }
}

?>

It always reports $success as true, even when it is false, and what also confuses me is even when both the secret and verification are correct the entry isn't removed from the MySQL database.

The older version works fine though. That's what baffles me.

The same method I used for checking success (if (mysql_query($sql)) worked fine for insertion and actually gives me feedback if it fails as it should, but for some reason it always reports success when I attempt to delete a record.

If anyone can help me out here I'd really appreciate it.

Please note: I'm very much a novice with PHP/MySQL, so please forgive me.

Please don't use mysql_* functions in new code. They were removed from PHP 7.0.0 in 2015. Instead, use prepared statements via PDO or MySQLi. See Why shouldn't I use mysql_* functions in PHP? for more information. — Madara's Ghost
– Madara's Ghost, Commented Sep 1, 2012 at 17:08
Exactly. You should start using MySQLi or PDO, both ensure that your code is from SQL injection and other such headaches. — NedStarkOfWinterfell
– NedStarkOfWinterfell, Commented Sep 1, 2012 at 17:09
Side note : In your code you first escape string, and then do sha1 on escaped value. I think order should be opposite : first sha1, and then escape result. — a1ex07
– a1ex07, Commented Sep 1, 2012 at 17:11
@Truth Once I finish and make sure it all works I'll go about changing it. Thanks for the heads up. — Battleroid
– Battleroid, Commented Sep 1, 2012 at 17:14
Also, if you check the length of the passwords, you only have to check one of them, since you've already checked if they are equal (like in your first code) — Dirk McQuickly
– Dirk McQuickly, Commented Sep 1, 2012 at 17:16

Community · Accepted Answer · 2017-05-23 12:12:20Z

2

From This post

mysql_query will return TRUE even if the query did not actually remove anything.

So you will always get true from Delete query..

Credit goes to Paolo

edited May 23, 2017 at 12:12

CommunityBot

11 silver badge

answered Sep 1, 2012 at 17:16

Moe Tsao

1,0546 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Christopher Armstrong · Accepted Answer · 2012-09-01 17:07:47Z

1

This is because the query is syntactically correct, so there'll never be an error. Instead, you want to find the number of rows that were actually affected:

<?php

mysql_connect('localhost', 'root', 'pass');
mysql_select_db('database');

// Create variables from form
$server_secret = mysql_real_escape_string($_POST['secret_initial']);
$server_confirm = mysql_real_escape_string($_POST['secret_verify']);

// Check for verification
if ($server_secret == $server_confirm) {
    // Delete from database
    $sql = "DELETE FROM `servers` WHERE secret='" . sha1($server_secret) . "'";
    $result = mysql_query($sql);
    if (mysql_affected_rows($result)) {
        $success = true;
    } else {
        $success = false;
    }
}

?>

answered Sep 1, 2012 at 17:07

Christopher Armstrong

7,9812 gold badges28 silver badges28 bronze badges

4 Comments

Battleroid Over a year ago

Ah! Let me try this really quick.

Christopher Armstrong Over a year ago

Also, please read the comment from Truth - he is absolutely correct. I was showing you how to correct this case to learn from, but you should move away from the mysql_* functions.

Battleroid Over a year ago

Thanks, that was the issue, I'm going to double check the insertion deal as well. Once everything is working I'll go about changing it.

Battleroid Over a year ago

Well, it no longer reports success, however it still is not removing records. Odd.

Collectives™ on Stack Overflow

Cannot delete MySQL record using PHP

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related