I created a forum in PHP and ATM I’m creating the delete topic button.
When this button is pressed obviously the topic get’s deleted, but also the users posts counts must decrement my the amount of posts they had in that topic.
The problem I’m having is that I can’t see how to do this efficiently! The only way I can think of doing this is by updating each users’s row in the database and changing their post count using multiple queries.
For example:
foreach ($users as $user)
mysql_query("UPDATE users SET posts='$postCount' WHERE id='$id'");
$postCount would depend on the users post count and how many posts they had in the soon to be deleted topic.
$id is the user’s id.
If I had a topic which 500 users had posted in, that would mean having to run 500 queries which I’m not to sure would work out very well XD.
Hey you should do it this way without the foreach:
UPDATE users SET posts = posts - 1 WHERE id= '$id'
What I’d do is:
1: Get the topic’s primary key,
2: DELETE FROM posts WHERE assign_topic = ‘$topicKey’
3: DELETE FROM topics WHERE id = ‘$topicKey’
4: Run the Query at the top to update the users posts…
Actually, are you saying – You want to count all the users posts within a bunch of posts, before you delete them?
So if UserA had 50 posts in the topic of Horse, and UserB had 50 posts in the topic of Dog… I can see how this could get complex actually. Hrmm
I would, but what if the topic had multiple users posts? I need to decrement the post count for each user who posted in that topic. I can’t use -1 also, because they user may have posted in the topic multiple times.
UPDATE users
INNER
JOIN ( SELECT userid
, COUNT(*) AS posts
FROM posts
WHERE topic = 937
GROUP
BY userid ) AS delete_counts
ON delete_counts.userid = users.id
SET users.posts = users.posts - delete_counts.posts