How to test if a MySQL query was successful in modifying database table data?

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 javascript function, which will update the page via AJAX. I would like to return the string false if the query fails, as I've 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.

please elaborate with more details about why it's apparently not working at the moment, and show the Javascript code too. — Alnitak, Mar 08 '09 at 21:21
Stupid questions first -- $con is a valid connection, right? — jmucchiello, Mar 08 '09 at 23:13
yup, it sure is. Alnitak, javascript code is pasted, however the page never returns 'false' so I don't think it is a javascript problem. — , Mar 09 '09 at 10:15

score 2 · Answer 1 · edited Apr 26 '21 at 00:06

2

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();
    }
}

edited Apr 26 '21 at 00:06

Dharman

30,962
25
85
135

answered Mar 08 '09 at 21:20

James B

8,183
4
33
40

`> 0` is not necessary for this case; a truthy/falsey check is all that is needed. – mickmackusa Apr 28 '21 at 04:29

score 2 · Answer 2 · edited Apr 26 '21 at 00:06

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.

edited Apr 26 '21 at 00:06

Dharman

30,962
25
85
135

answered Mar 08 '09 at 21:24

Alnitak

334,560
70
407
495

`> 0` is not necessary for this case; a truthy/falsey check is all that is needed. – mickmackusa Apr 28 '21 at 04:29
@mickmackusa and is arguably a bad practise; Many coding standards would prohibit such an implicit coercion of an integer value into a boolean. – Alnitak Apr 28 '21 at 11:25
Could you please link me to such documented standards so that I can read up on it? – mickmackusa Apr 28 '21 at 12:15

score 1 · Answer 3 · answered Mar 08 '09 at 21:24

1

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

answered Mar 08 '09 at 21:24

Gumbo

643,351
109
780
844

...but of course this doesn't indicate if the database table was changed. – mickmackusa Apr 28 '21 at 04:29

score -1 · Answer 4 · answered Mar 08 '09 at 23:12

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.

score -1 · Answer 5 · answered May 04 '21 at 06:11

function run_query($query,$conn,$dbname){
   $conn->select_db($dbname);
   if($result = $conn->query($query)){
     return $result; 
   }
   else{
     echo 'error ';
     exit();
   }

}

run_query() accepts three parameters, the query, connection, and DB. will through an error if the query was unsuccessful, else result object will be returned.

How to test if a MySQL query was successful in modifying database table data?

5 Answers5

Linked

Related