james Oduro james Oduro - 1 month ago 10
MySQL Question

Delete from two or more tables

lets says i have these variable:

$post_id = 222;

$IsLoggIn =2;

I have same column "post_id" name in five different tables.
I want to write an sql statement to check and see if there is a row with column name "post_id" whose value is equal to variable "$post_id" and delete that row

Note: Apart from the "public_feed_table", it is possible there could be no row with post_id equal to variable "$post_id" so the sql statement should check if there is a row before deleting.

I want a single sql statement that can do this job.

What I tried is below. Please help:

global $dbc_conn,
$public_feed_table,
$images_table,
$comments_table,
$rating_table,
$notification_table,
$IsLoggIn;

$sql = "DELETE
p,i,c,r,n
FROM
$public_feed_table p
LEFT JOIN
$images_table i,
$comments_table c,
$rating_table r,
$notification_table n
ON
i.post_id,
c.post_id,
r.post_id,
n.post_id=p.post_id

WHERE p.post_id='$post_id'
AND p.user_id='$IsLoggIn'

";
//query database
$query = mysqli_query($dbc_conn,$sql);

Answer

You join Syntax is worng. It must be:

       $sql    =   "DELETE 
                    p,i,c,r,n
                        FROM
                            $public_feed_table p
                                LEFT JOIN 
                                $images_table i on p.post_id = i.post_id
                                LEFT JOIN
                                $comments_table c on i.post_id = c.post_id
                                LEFT JOIN
                                $rating_table r on  c.post_id = r.post_id 
                                $notification_table n
                                    ON
                                    n.post_id=r.post_id

                                    WHERE p.post_id='$post_id'
                                    AND p.user_id='$IsLoggIn'

                                    ";

For more Information see: http://www.w3schools.com/sql/sql_join_left.asp