Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to know if the DELETE query really deletes a row ,using PDO [duplicate]

Tags:

php

mysql

pdo

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?

like image 368
beginner Avatar asked Oct 14 '16 09:10

beginner


2 Answers

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 :)

like image 184
Naresh Kumar P Avatar answered Nov 14 '22 21:11

Naresh Kumar P


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

like image 40
OlajosCs Avatar answered Nov 14 '22 22:11

OlajosCs