8

I am passing a DELETE query to mysql using PDO statements. I want to know, whether the query really deletes a row or not? I try to use return value of $stmt->execute()

function delete(){
    $stmt = $this->db->prepare("DELETE FROM users WHERE id= :id");
    $stmt->bindValue(':id',$_POST[id]);

    var_dump($stmt->execute());
}

But This gives always true, even no row is deleted. This gives true even if I don't post any value Or even post same value again and again. May be because query is successful with 0 row deleted , so return value of $stmt->execute() does not help.

So Is There any other way in PDO to know whether any row is really deleted or not, more specifically I want to be sure that only one row was deleted.

So i want something like this

function delete(){
    $stmt = $this->db->prepare("DELETE FROM users WHERE id= :id");
    $stmt->bindValue(':id',$_POST[id]);
    $stmt->execute();
    if($rowdeleted == 1){ echo "success";}
    else{echo "failure";}
}

Any ideas?

beginner
  • 1,770
  • 2
  • 24
  • 46
  • Statement suppose to return number of columns affected by query. At least in postgres. – simar Oct 14 '16 at 09:32
  • 1
    [PDOStatement::rowCount()](http://www.php.net/manual/en/pdostatement.rowcount.php) – Mark Baker Oct 14 '16 at 09:34
  • i get it , I thought `rowcount()` only works for select staetement – beginner Oct 14 '16 at 09:37
  • @beginner. I have provided with the explanations about the process how to get the affected rows after the operations and some sample code along with the output. Have a try and share thoughts about your view. Thanks :) – Naresh Kumar P Oct 14 '16 at 09:44

4 Answers4

13

Explanation

In order to know how many rows have been affected for the last operation during the select and Delete you have to GET THE ROW COUNT after the execution of the code.

Conditions:

  • If the count is greater than 0 - It means the code has affected some operations.
  • If the count does no return greater than 0 - It means that there is no records to match in the WHERE Clause.

Examples

PDOStatement::rowCount - Returns the number of rows affected by the last SQL statement

PDOStatement::rowCount() returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object.

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.

Sample Code:

<?php
/* Delete all rows from the FRUIT table */
$del = $dbh->prepare('DELETE FROM fruit');
$del->execute();

/* Return number of rows that were deleted */
print("Return number of rows that were deleted:\n");
$count = $del->rowCount();
print("Deleted $count rows.\n");
?>

Sample Output:

Return number of rows that were deleted:
Deleted 9 rows.

Happy Coding :)

Naresh Kumar P
  • 3,937
  • 2
  • 13
  • 32
10

If you are using PDO then you need the rowCount method. In case of mysqli you need the affected rows

OlajosCs
  • 221
  • 2
  • 9
1

A simple trick is to check the number of row affected

$count = $stmt->rowCount();

There is another answer, more detailled here : What does a successful MySQL DELETE return? How to check if DELETE was successful?

Community
  • 1
  • 1
Aks
  • 397
  • 3
  • 11
0

You could try somethign like this: 

if($stmt[0]->rowCount() > 0)
{ 
echo "success";
}
else{
echo "failure";
}
kerv
  • 315
  • 2
  • 13