checking number of records in mySQL table

Question

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");
    ?>

You can use `count()` on SELECT with a conditional statement or num_rows — Funk Forty Niner, Apr 01 '15 at 20:22
Sidenote: Your present code is open to [**SQL injection**](http://stackoverflow.com/q/60174/). Use [**`mysqli` with prepared statements**](http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php), or [**PDO with prepared statements**](http://php.net/pdo.prepared-statements), *they're much safer*. — Funk Forty Niner, Apr 01 '15 at 20:25

score 1 · Accepted Answer · answered Apr 01 '15 at 22:19

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.

Agreed that the original approach is somewhat inefficient, but in the absence of clustering and transaction rollbacks, if there is an auto increment id, one could simply `delete from table where id<=last_insert_id()-24` — symcbean, Apr 01 '15 at 22:33
Correct, assuming he is using an auto increment id of 1 and doesn't run into auto increment locks on multiple requests (which can cause skips in the id) then your query is much simpler and easier to implement. my recommendation probably is much less efficient when you hit a high volume table, I imagine it would be somewhat of a resource hog at 1million + records. — TheRk, Apr 01 '15 at 22:42

checking number of records in mySQL table

1 Answers1