[SOLVED] Auto delete all rows for table name "chess" when it reach 800 rows

[sungpeng]

Check is it possible to auto delete all rows for table name "chess" when it reach 800 rows. The primary ID will start from "1" again instead of continue add to 801, 802, 803. Can I have the php mysql code?

[Mchl]

You could use MySQL triggers for that (MySQL 5.0 or higher)

[sungpeng]

How? Triggers? I am inside mysql 5 now, no button for "triggers".

[Mchl]

As far as I know MySQL does not have any buttons at all.

 

http://dev.mysql.com/doc/refman/5.0/en/triggers.html

 

[sungpeng]

Can I have the statement? Is like mysql_query("DELETE FROM table WHERE something...."); rename field ID...

[Mchl]

Ok... it seems triggers might be a bit too sophisticated tool for you at this moment.

 

How about php code like this

 

<?php

$query = "SELECT COUNT(*) AS rowCount FROM chess";
$result =  mysql_query($query);
$row = mysql_fetch_assoc($result);

if ($row['rowCount'] >= 500) {
  $query = "TRUNCATE TABLE chess";
  mysql_query($query);
}

 

 

Note that you need to have DROP privilege for `chess` table to be able to use TRUNCATE.

[sungpeng]

ok Thank I will explore trigger again.