homepage Welcome to WebmasterWorld Guest from 54.163.91.250
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
SQL update
Readie




msg:4106380
 1:12 pm on Mar 29, 2010 (gmt 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?

 

syber




msg:4106394
 1:54 pm on Mar 29, 2010 (gmt 0)

It shouldn't be CPU intensive if you have an index on news_id.

Anyango




msg:4106490
 5:29 pm on Mar 29, 2010 (gmt 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

Tommybs




msg:4106502
 5:48 pm on Mar 29, 2010 (gmt 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.

Readie




msg:4106509
 5:59 pm on Mar 29, 2010 (gmt 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved