Keeping only 40 comments for each product

[bnashenas1984]

Hi everyone

I have 2 tables in my MySQL DB

Products :

+----+--------+
| id | Product|
+----+--------+
| 1  | Prdct1 |
| 2  | Prdct2 |
| 3  | Prdct3 |
| 4  | Prdct4 |
+----+--------+

And then I have another table for comments posted by visitors for each product

comments:

+----+---------+---------+
| id | prdctid | commect |
+----+---------+---------+
| 1  |   1     |  text   |
| 2  |   4     |  text   |
| 3  |   2     |  text   |
| 4  |   1     |  text   |
| 5  |   2     |  text   |
| 6  |   3     |  text   |
| 7  |   1     |  text   |
+----+---------+---------+

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.

SELECT * FROM comments WHERE prdctid="current product id"

And then :

if (mysql_affected_rows() > 40){
  $query = mysql_query('SELECT id FROM comments WHERE prdctid="current product id" LIMIT 1');
  
  $tmpVar=mysql_fetch_array($query);
  
  mysql_query('DELETE FROM comments WHERE id='.$tmpVar["id"]);
}

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

[Dormilich]

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 …

[bnashenas1984]

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

[Dormilich]

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.