Sql delete using inner join more than 3 tables

Question

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

Possible duplicate of [Delete with Join in MySQL](http://stackoverflow.com/questions/652770/delete-with-join-in-mysql) — Patrick Q, Aug 31 '16 at 19:28

score 0 · Answer 1 · answered Aug 31 '16 at 19:26

After DELETE you should tell from what table to delete rows. Try this query:

$db_delete->prepare("DELETE blog_post 
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");

No error but the table didn't delete @Andrej Ludinovskov – Alex Aug 31 '16 at 19:37 — Alex, Aug 31 '16 at 19:37

Asaph · Accepted Answer · 2016-08-31T19:41:58.467

0

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 have 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");

edited Aug 31 '16 at 19:41

answered Aug 31 '16 at 19:29

Asaph

159,146
25
197
199

i got this error `Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'pb.BID' in 'on clause'' 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` – Alex Aug 31 '16 at 19:32
Whoops! Typo. `pb` => `bp`. Just fixed it. Try again, please. – Asaph Aug 31 '16 at 19:33

Sql delete using inner join more than 3 tables

2 Answers2