By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
461,998 Members | 484 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 461,998 IT Pros & Developers. It's quick & easy.

Keeping only 40 comments for each product

100+
P: 258
Hi everyone

I have 2 tables in my MySQL DB

Products :
Expand|Select|Wrap|Line Numbers
  1. +----+--------+
  2. | id | Product|
  3. +----+--------+
  4. | 1  | Prdct1 |
  5. | 2  | Prdct2 |
  6. | 3  | Prdct3 |
  7. | 4  | Prdct4 |
  8. +----+--------+
  9.  
And then I have another table for comments posted by visitors for each product

comments:
Expand|Select|Wrap|Line Numbers
  1. +----+---------+---------+
  2. | id | prdctid | commect |
  3. +----+---------+---------+
  4. | 1  |   1     |  text   |
  5. | 2  |   4     |  text   |
  6. | 3  |   2     |  text   |
  7. | 4  |   1     |  text   |
  8. | 5  |   2     |  text   |
  9. | 6  |   3     |  text   |
  10. | 7  |   1     |  text   |
  11. +----+---------+---------+
  12.  
Now I need to keep (at most) 40 comment for each product and delete the older ones. I mean when someone writes a comment on a product which has 40 comments the oldest comment will be removed.

I know that I can only show 40 by LIMIT command in MySQL but I actualy need to keep only 40 comments for each product on the DB and remove the older ones.

Here's what I am doing right now.

Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM comments WHERE prdctid="current product id"
  2.  
And then :
Expand|Select|Wrap|Line Numbers
  1. if (mysql_affected_rows() > 40){
  2.   $query = mysql_query('SELECT id FROM comments WHERE prdctid="current product id" LIMIT 1');
  3.  
  4.   $tmpVar=mysql_fetch_array($query);
  5.  
  6.   mysql_query('DELETE FROM comments WHERE id='.$tmpVar["id"]);
  7. }
  8.  
This works but as you can see it's not fast enough and not professional.

Is there any other way to do this?

Thanks / Behzad
Jun 12 '10 #1
Share this Question
Share on Google+
3 Replies

Dormilich
Expert Mod 5K+
P: 8,639
personally, I wouldn’t delete any comments*, if I can LIMIT my SQL clause, that’d be fine for me (plus you cannot incidentaly delete something).

* - databases are able to handle millions of entries …
Jun 12 '10 #2

100+
P: 258
Hi Dormilich
Actualy i'm working on a project for several companies with a shared server and the problem is that the database is limited to 100 MB and 27 requests per second.

I'm already using a lot of that space. So, letting visitors post unlimited comments might exceed that limit.

Thanks / Behzad
Jun 13 '10 #3

Dormilich
Expert Mod 5K+
P: 8,639
the problem is that the database is limited to 100 MB and 27 requests per second.
that are some hard restraints. I’ll think about it.
Jun 13 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.