I have 2 tables in my MySQL DB
Products :
Expand|Select|Wrap|Line Numbers
- +----+--------+
- | id | Product|
- +----+--------+
- | 1 | Prdct1 |
- | 2 | Prdct2 |
- | 3 | Prdct3 |
- | 4 | Prdct4 |
- +----+--------+
comments:
Expand|Select|Wrap|Line Numbers
- +----+---------+---------+
- | id | prdctid | commect |
- +----+---------+---------+
- | 1 | 1 | text |
- | 2 | 4 | text |
- | 3 | 2 | text |
- | 4 | 1 | text |
- | 5 | 2 | text |
- | 6 | 3 | text |
- | 7 | 1 | text |
- +----+---------+---------+
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
- SELECT * FROM comments WHERE prdctid="current product id"
Expand|Select|Wrap|Line Numbers
- 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"]);
- }
Is there any other way to do this?
Thanks / Behzad