Alex Alex - 3 months ago 11
PHP Question

Sql delete using inner join more than 3 tables

I run this sql statement to delete from database using inner join, but i get too many errors

Error


Fatal error: Uncaught exception 'PDOException' with message
'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an
error in your SQL syntax; check the manual that corresponds to your
MariaDB server version for the right syntax to use near 'INNER JOIN
ipaddress_likes_map ON ipaddress_likes_map.postat = blog_post.BID INN'
at line 3' in C:\xampp\htdocs\codejail.net_inc\dbcontroller.php:57
Stack trace: #0
C:\xampp\htdocs\codejail.net_inc\dbcontroller.php(57):
PDOStatement->execute() #1
C:\xampp\htdocs\codejail.net\user\deleteaccount.php(88):
DBController->execute() #2 {main} thrown in
C:\xampp\htdocs\codejail.net_inc\dbcontroller.php on line 57


here is my code

<?php
if(isset($_POST['delete'])){
$pass = $_POST['pasyske'];
$userkey = $_POST['username'];
$db_delete = new DBController();
$db_delete->prepare("DELETE
FROM blog_post
INNER JOIN ipaddress_likes_map ON ipaddress_likes_map.postat = blog_post.BID
INNER JOIN flagpost ON flagpost.postId = blog_post.BID
INNER JOIN postviewmap ON postviewmap.viewpostId = blog_post.BID
INNER JOIN replys ON replys.rid = blog_post.BID
INNER JOIN votepoint_map ON votepoint_map.postlike_id = blog_post.BID
WHERE blog_post.UserName = :alluserpost");
$db_delete->bind(":alluserpost", $userkey);
//$db_delete->bind(":password", $encrypt_password);
$db_delete->execute();
$pdeleted = $db_delete->getAll();
$db_delete->free();

if($pdeleted){
print_r($pdeleted);
}

}?>


I'm sure my db controller is okay because when i run single delete statement it work very fine

Answer

Give each table an alias and reference it right after the word DELETE. Also, use LEFT JOIN instead of INNER JOIN because I'm guessing you still want the deletes to work event if some of the joined tables don't matching rows.

db_delete->prepare("DELETE bp, i, f, p, r, v
FROM blog_post AS bp
LEFT JOIN ipaddress_likes_map AS i ON i.postat = bp.BID
LEFT JOIN flagpost AS f ON f.postId = bp.BID
LEFT JOIN postviewmap AS p ON p.viewpostId = bp.BID
LEFT JOIN replys AS r ON r.rid = bp.BID
LEFT JOIN votepoint_map AS v ON v.postlike_id = bp.BID
WHERE bp.UserName = :alluserpost");