How to query records by current date?

Question

The following code allows me to count the total number of visits that are registered by IP:

$stmtIP = $con->prepare("SELECT COUNT(*) FROM visitor_ip WHERE ip_remote=?");
$stmtIP->bind_param("s",$IP_ADDRESS);
$stmtIP->execute();
$count_ip_record = null;
$stmtIP->bind_result($count_ip_record);
$stmtIP->fetch();
$stmtIP->close();

echo $count_ip_record;

I have a column in the data table called: visitor_date of type datetime, records a:

2021-01-18 19:40:15

Which I want to count the IP records by current date. How can I pass that condition to WHERE?

id'7238 · Accepted Answer · 2021-01-19T05:55:17.607

1

Use CURDATE function:

SELECT COUNT(*) FROM visitor_ip WHERE DATE(visitor_date) = CURDATE() AND ip_remote=?

Or any date via parameter:

$date = date('Y-m-d');
$sql = "SELECT COUNT(*) FROM visitor_ip WHERE DATE(visitor_date) = ? AND ip_remote=?";
$stmtIP = $con->prepare($sql);
$stmtIP->bind_param("ss", $date, $IP_ADDRESS);
$stmtIP->execute();

If visitor_date has index:

$date = date('Y-m-d');
$sql = "SELECT COUNT(*) FROM visitor_ip "
     . "WHERE (visitor_date BETWEEN ? AND ? + INTERVAL 1 DAY) AND ip_remote=?";
$stmtIP = $con->prepare($sql);
$stmtIP->bind_param("sss", $date, $date, $IP_ADDRESS);
$stmtIP->execute();

edited Jan 19 '21 at 05:55

answered Jan 19 '21 at 03:08

id'7238

2,428
1
3
11

FYI, calling a function on the column's value ruins the index use – Your Common Sense Jan 19 '21 at 05:26
@YourCommonSense, you're right. The question is closed, but the answer has been updated with the condition for the index anyway. – id'7238 Jan 19 '21 at 05:57

How to query records by current date?

1 Answers1