| 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.
|
|
|
