如何使用php / pdo从MySQL数据库中删除友谊并检查它是否成功
问题描述:
Im trying to delete a friendship between two users from the db
the friendship table is simple:
friend_one |friend_two
100 |142
142 |100
Here is the code I have, but its not working:
if (!empty($_POST)) {
$remover_id = $_POST['remover_id'];
$removed_id = $_POST['removed_id'];
try {
$query = "DELETE * FROM
`friendships`
WHERE
(friend_one = :remover_id AND friend_two = :removed_id)
OR
(friend_two = :remover_id AND friend_one = :removed_id)
";
$sth = $connection->prepare($query);
$sth->execute(
array(
':remover_id' => $remover_id,
':removed_id' => $removed_id
));
if($sth->rowCount () >=0){
$response["success"] = $http_response_success;
die(json_encode($response));
$connection = null;
} else {
$response["success"] = $http_response_server_error;
$response["message"] = $http_message_server_error;
die(json_encode($response));
$connection = null;
}
} catch (PDOException $ex) {
$response["success"] = $http_response_server_error;
$response["message"] = $http_message_server_error;
die(json_encode($response));
$connection = null;
}
} else {
$response["success"] = $http_response_bad_request;
$response["message"] = $http_message_bad_request;
die(json_encode($response));
$connection = null;
}
First of all I dont think the way I check for success is correct, second of all, the friendship doesnt get removed from the DB anyway.
When I run this I find myself in the else statement:
if($sth->rowCount () >=0){
$response["success"] = $http_response_success;
die(json_encode($response));
$connection = null;
} else {
$response["success"] = $http_response_server_error;
$response["message"] = $http_message_server_error;
die(json_encode($response));
$connection = null;
}
答
You have an SQL error for your DELETE statement
DELETE FROM `friendships` WHERE
(friend_one = :remover_id AND friend_two = :removed_id)
OR
(friend_two = :remover_id AND friend_one = :removed_id)
You had an asterisk after delete
, where there shouldn't be one. https://dev.mysql.com/doc/refman/5.0/en/delete.html
As for checking for PDO Errors, you shouldn't use $sth->rowCount()
.
if(!$sth->execute($data)) {
// Error (SQL Error)
}
if($sth->rowCount() > 0) {
// At least 1 record was updated / inserted / deleted / (Possibly Selected)
}