Update left join
Posted: Sat Aug 23, 2008 9:31 am
I have 2 tables, 1 with comments and 1 with users.
What I want is when I remove an article, it should remove the comments and profile.comments-1 for each comment.
here's what I have so far:
But when a user has more then 1 comment on the article it still does -1 and not for example -4 (the rows that are affected is the same as the amount of different users that have posted).
I hope that you understand it and can give me a way of doing it
.
Thanks
What I want is when I remove an article, it should remove the comments and profile.comments-1 for each comment.
here's what I have so far:
Code: Select all
public function removeArticleComments($articleid)
{
$sql = "UPDATE comments AS c
LEFT JOIN profiles AS p ON c.author = p.id
SET p.comments=p.comments-1 WHERE c.articleid=%d";
$vars = array($articleid);
$result = dbConnect::query($sql,$vars);
echo mysql_info();
$sql = "DELETE FROM comments WHERE articleid=%d";
$vars = array($articleid);
$result = dbConnect::query($sql,$vars);
}
I hope that you understand it and can give me a way of doing it
Thanks