Welcome to WebmasterWorld Guest from 54.167.216.93

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

SQL update

     
1:12 pm on Mar 29, 2010 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



On a news post/comment script I'm working on I want to have a link to the comments saying something like "30 comments on this article" - now my question is how should I go about getting the "30"?

One fool-proof method would be to simply do

$sql = 'SELECT COUNT(*) AS total FROM news_comments WHERE news_id = ' . $someVariable;

But I'm worried that might be a little CPU intensive, so I think a column on the news posts table with a count of the comments would be more effective.

My concern is users posting comments within just a few milliseconds of each other.

With a script like (semi pseudo code)
if(submit button pressed / validation passed){
// Do comment insert
$sql = 'SELECT t_comments FROM news WHERE news_id = ' . $someVariable;
$res = mysql_query($sql);
$total = mysql_result($res, 0, "total_comments");
$total += 1;
$sql = 'UPDATE news SET t_comments = ' . $total . ' WHERE news_id = ' . $someVariable;
mysql_query($sql);
}
Is there any risk of 2 users adding comments so close together that this fails and only adds 1, when there are 2 new comments?
1:54 pm on Mar 29, 2010 (gmt 0)

10+ Year Member



It shouldn't be CPU intensive if you have an index on news_id.
5:29 pm on Mar 29, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member




Is there any risk of 2 users adding comments so close together that this fails and only adds 1, when there are 2 new comments?


None, not that i know of
5:48 pm on Mar 29, 2010 (gmt 0)

5+ Year Member



Change the sql to "select count(news_id)" as well if that's your index. Saves select * back and all the extra fields that aren't necessary if you want to optimize a bit more.
5:59 pm on Mar 29, 2010 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



Thanks for the replies :)

So, the options are an optimised version of what I know 100% is fail safe, or something that we're pretty sure is fail safe.

I think I shall use "COUNT($news_id) AS total" however, just to be on the safe side.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month