CODEWITHSUNDEEP
phpmysql
Master Przemo
Master Przemo

Reputation: 35

checking number of records in mySQL table

I am looking for a way to check if there are certain number of records within mysql table. For example: After POST request been before putting data to dabase, it checks first how many records there are. If lets say there are 24 records, then it will delete record with latest date based on timestamp and then inster new value from POST request. Has anyone got idea on how to do it? Looking forward fpr your answers. Below I attached simple code i wrote to insert data from post request into table.

  <?php
        include("connect.php");

        $link=Connection();

        $temp1=$_POST["temp1"];
        $hum1=$_POST["hum1"];

        $query = "INSERT INTO `tempLog` (`temperature`, `humidity`) 
            VALUES ('".$temp1."','".$hum1."')"; 

        mysql_query($query,$link);
        mysql_close($link);

        header("Location: index.php");
    ?>

Upvotes: 1

Views: 90

Answers (1)

TheRk
TheRk

Reputation: 341

When you say delete with the latest date I have to assume you mean the oldest record? Your description doesnt tell me the name of you date field so Lets assume its onDate. You also didnt mention what your primary key is so lets assume that is just id. if you run the below query before inserting it will purge all the oldest records leaving only the newest 23 in the database.

delete from templog where id in (
  select id from ( 
    select @rownum:=@rownum+1 'rowid', t.id from templog t, (select @rownum:=0)r order by t.onDate
  )v where v.rowid > 23
);

Of course you should test on data you don't mind losing.

It is best to do a cleanup purge each time instead of removing a single row before adding a new one because in the event of exceptions it will never clean itself down to the 24 rows you wish to truly have.

I also want to note that you may want to reconsider this method all together. Instead leave the data there, and only query the most recent 24 when displaying the log. Since you are going through the trouble of collecting the data you might as well keep it for future reporting. Then later down the road if your table gets to large run a simple daily purge query to delete anything older than a certain threshold.

Hope this helps.

Upvotes: 1

Related Questions