How to get number of new records from database in php?

Question

Let's say I have table that has column that stores CURRENT_TIMESTAMP. I am using PHP code to show:

1. Records Enter Today
2. Records entered this month

Any idea?

Answer 1

You need appropriate ways of saying

 WHERE record_timestamp >= something

In the case of "today" something should be CURDATE(). That returns all records timestamped after midnight today.

In the case of "this month" something should be

 DATE_FORMAT(CURDATE(), '%Y-%m-01')

That chooses midnight of the first day of the present month.

Taking it a little further, you get "yesterday" like this:

 WHERE record_timestamp >= CURDATE() - INTERVAL 1 DAY
   AND record_timestamp <  CURDATE()

You get "last month" like this:

WHERE record_timestamp >= DATE_FORMAT(CURDATE(), '%Y-%m-01') - INTERVAL 1 MONTH
  AND record_timestamp  < DATE_FORMAT(CURDATE(), '%Y-%m-01') 

A detail: Because you're using the TIMESTAMP data type, all these computations will be performed using the current time zone setting of your connection to the MySQL server.

Answer 2

You can get records as like that:

SELECT * FROM table WHERE dateColumn > DATE_SUB(NOW(), INTERVAL 1 DAY);        
SELECT * FROM table WHERE dateColumn > DATE_SUB(NOW(), INTERVAL 1 MONTH);

Answer 3

You can apply query like:

• To get today enter record.

  select * from table where DATE( from_unixtime( date_column )) = '2016-01-06';
  

• To get particular month record.

  SELECT * FROM table WHERE MONTH( from_unixtime( date_column )) = month_no;
  

Answer 4

To get today's records:-

SELECT * FROM `table` WHERE date_format(`created`,'%Y-%m-%d') = CURDATE();

To find current month record follow :-

Select current months records mysql from timestamp column

Answer 5

If you want to get the records entered today, then take today's date & append time to it as 00:00:00, then calculate the timestamp of it and store it in variable $start, similarly calculate $end by appending time as 24:00:00 & in sql pass where clause with condition date >= $start and date <= $end.

Same logic you can apply by getting month's 1st date time stamp and month's end date time stamp.

Hope this will help you.

Answer 6

Complete PHP code using the function num_rows as it says here.

---

$conn = new mysqli($servername, $username, $password, $database);
$SQL = "SELECT * FROM table WHERE column > DATE_SUB(NOW(), INTERVAL 1 DAY)";
$results = $conn -> query($SQL);
if($results -> num_rows > 0){
     $noOfResults = $results -> num_rows;
}
else{
     $noOfResults = 0;
}
echo $noOfResults;

If you want the results from the last month just use INTERVAL 1 MONTH instead of 1 DAY, as @devpro answer states.