Testing a SQL query in PHP

Question

I have the following simple php code snippet, which will, when called, delete a relevant article from a database. The result is passed to a javacsript function, which will update the page via AJAX. I would like to return the string "false" if the query fails, as I am attempt below.

if($cmd=="deleterec"){
$deleteQuery = "DELETE FROM AUCTIONS1 WHERE ARTICLE_NO = ?";
if ($delRecord = $con->prepare($deleteQuery)) {
    $delRecord->bind_param("s", $pk);
    $delRecord->execute();
    $delRecord->close();
    echo "true";
} else {
echo "false";
}
}

I would like to know what I have missed, and the correct way to check if a query was successful or not.

edit:

based on peoples replies, I tried moving the if clause to $delRecord->execute(); instead of prepare, but t his made no difference. I can not get the page to return 'false'.

The below solutions do not work, because the query alwasy executes, even if incorrectly, and hence always returns 'true'. If I try the affected rows method I get the error : Call to undefined method mysqli_stmt::affected_rows()

Answer 1

You need to use mysqli->affected_rows() for checking if the query was successful (or you could use mysqli_stmt->execute()'s result value).

Taking your example, and modifying nothing but for the above:

if($cmd=="deleterec") {
    $deleteQuery = "DELETE FROM AUCTIONS1 WHERE ARTICLE_NO = ?";

    if ($delRecord = $con->prepare($deleteQuery)) {
        $delRecord->bind_param("s", $pk);
        $delRecord->execute();


        if ($delRecord->affected_rows() > 0) {
            echo "true";
        } else {
            echo "false";
        }

        $delRecord->close();
    }
}

Answer 2

You're currently only checking whether the SQL statement is correctly prepared, you're not checking whether it actually deleted the record.

Try:

...
echo ($delRecord->affected_rows() > 0) ? 'true' : 'false';
$delRecord->close();

That doesn't address whether you're correctly checking the result string in your Javascript code - if that's a problem we'll need more information.

Answer 3

Use the return value of mysqli_stmt->execute() to see if the query was executed successful.

Answer 4

if($cmd=="deleterec"){
    $deleteQuery = "DELETE FROM AUCTIONS1 WHERE ARTICLE_NO = ?";
    $delRecord = $con->prepare($deleteQuery);
    if ( $delRecord === false ) {
        echo "false";
    }

    $delRecord->bind_param("s", $pk);
    if ( $delRecord->execute() ) {
        echo "true";
    } else {
        echo "false";
    }
    $delRecord->close();
}

Checking prepare() could be left out because the query is fixed and should be working (unless there is an error on the server side). execute() returns true, if the query was executed successful. Using affected_rows() can be misleading because perhaps there was no item to delete and therefore affected_rows() whould return 0. Nontheless the query was successfully executed.