Welcome to WebmasterWorld Guest from 54.167.46.29

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

SQL update

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

Senior Member

WebmasterWorld Senior Member 5+ Year Member

joined:Dec 13, 2009
posts:943
votes: 0


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)

Preferred Member

10+ Year Member

joined:Nov 12, 2004
posts:393
votes: 0


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

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Apr 24, 2005
posts:697
votes: 0



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)

Full Member

5+ Year Member

joined:June 21, 2007
posts:316
votes: 0


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)

Senior Member

WebmasterWorld Senior Member 5+ Year Member

joined:Dec 13, 2009
posts:943
votes: 0


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.