Bob Myles Bob Myles - 5 months ago 9
MySQL Question

PHP Mysql select statement to display that day records ONLY

I am developing a system that should output the total number of sms sent in a day. It also should separate the delivered ones and the ones that failed.
It should only out put the records for that day only... like only the records for that day.
My database is smsdb.

$sql = "CREATE TABLE smsdb (
id INT(6) AUTO_INCREMENT PRIMARY KEY,
sms_text VARCHAR(300) NOT NULL,
receiver_number VARCHAR(30) NOT NULL,
time_sent DATETIME,
status VARCHAR(20) )";


//I tried to use the command to display todays records but I get all records even for other days.

$sql = ("SELECT DATE(time_sent) AS date, COUNT(*) as total_sent, SUM(status = 'Success') as delivered,SUM(status = 'failed') AS failed FROM smsdb GROUP BY DATE(time_sent) ");


I only want total records for this day only.
Kindly, anyone help me

Answer

You're not putting in a date filter. You can either pass it in as a variable, so you can select other days:

$sql = ("SELECT DATE(time_sent) AS date, COUNT(*) as total_sent, SUM(status = 'Success') as delivered,SUM(status = 'failed') AS failed FROM smsdb WHERE DATE(time_sent) = '$date_selected'");

Note that you really ought to bind parameters for this, but this will give you an idea.

The other option if you always want it to be the current day is use the CURDATE() function:

$sql = ("SELECT DATE(time_sent) AS date, COUNT(*) as total_sent, SUM(status = 'Success') as delivered,SUM(status = 'failed') AS failed FROM smsdb WHERE DATE(time_sent) = CURDATE()");