Only show last 5 days records in php

Question

I have a website with a simple PHP query that return records from a MYSQL table without issue.

$query = mysqli_query( $connection, "SELECT * FROM $shopname

I am trying to figure out how to only return the last 5 days of records based on a field called "date". The date field is a VARCHAR type in mysql. The format of the dates in the date field are like this: 8/9/2016 5:52:17 PM. I did try the following query from some researching, but this doesn't return anything. What am I doing wrong?

$query = mysqli_query( $connection, "SELECT * FROM $shopname WHERE `date` >= DATE_SUB(CURDATE(), INTERVAL 5 DAY)");

*IMPORTANT - I can't change mysql field to DATE. It has to be VARCHAR.

Please change the type of the `date` column into `date`. Then you can use functions that are meant to be used with dates (not characters). — ZZ-bb, Oct 10 '16 at 06:49
_Never_ store dates in a `varchar`. This is why the query isn't returning anything. — Tim Biegeleisen, Oct 10 '16 at 06:49
Try with `NOW()` -> `WHERE date >= DATE_SUB(NOW(), INTERVAL 5 DAY)")` and change your date column to `timestamp` — S.I., Oct 10 '16 at 06:49
The issue is that I import a CSV file into this field and the dates don't import if I change to DATE type — Maria Nolorbe, Oct 10 '16 at 06:57
if I import my CSV with the field as DATE type it imports all 0's like 0000-00-00 00:00:00 — Maria Nolorbe, Oct 10 '16 at 07:00

score 1 · Answer 1 · answered Oct 10 '16 at 06:51

1

You can use STR_TO_DATE() here for VARCHAR date format:

Example:

$query = "SELECT * FROM $shopname 
WHERE DATEDIFF(CURDATE(), STR_TO_DATE(date,'%Y-%m-%d')) < 5";

STR_TO_DATE() for DateFormat, will use to compare date with CURDATE().

DATEDIFF() for DateDiff, will use to get date difference in between date and current date.

But, it's better to use MYSQL Date format, instead of VARCHAR field.

answered Oct 10 '16 at 06:51

devpro

16,184
3
27
38

still no result – Maria Nolorbe Oct 10 '16 at 07:07
@MariaNolorbe: its better to use DATE format or you can also control it from PHP – devpro Oct 10 '16 at 07:12
I would love to use DATE format, but I'm at the mercy of a CSV file that is formatted like: 8/9/2016 5:52:17 PM. – Maria Nolorbe Oct 10 '16 at 07:15
@MariaNolorbe: alright, u can also control it from php – devpro Oct 10 '16 at 07:16
@MariaNolorbe: you can also check the duplicate links – devpro Oct 10 '16 at 07:19

score 1 · Answer 2 · answered Oct 10 '16 at 06:51

assuming that your vatchar date column is formatted as dd-mm-yyyy you shuold convert in date this way

$query = mysqli_query( $connection, 
    " SELECT * 
      FROM $shopname 
      WHERE str_to_date(`date`, '%d-%m-%Y')  >= DATE_SUB( CURDATE(), INTERVAL 5 DAY)");

score 0 · Answer 3 · answered Oct 10 '16 at 06:51

Try this:

SELECT * FROM TABLE t WHERE t.date >= DATE(NOW()) - INTERVAL 5 DAY

Provide the date should be if the char-type as date

Or else you can use this method to retrieve the data for last 5 days.

SELECT name,date 
from TABLE
WHERE date >= DATE_SUB(CURDATE(), INTERVAL 5 DAY) AND date <= CURDATE()
ORDER BY date DESC

score 0 · Answer 4 · edited Oct 10 '16 at 06:55

0

you can use this method by mysql UNIX_TIMESTAMP.this can change time to timestamp!

$query = mysqli_query( $connection, "SELECT * FROM $shopname WHERE `date` >= UNIX_TIMESTAMP(8/9/2016 5:52:17 PM)");

edited Oct 10 '16 at 06:55

devpro

16,184
3
27
38

answered Oct 10 '16 at 06:52

邓小武

1

Only show last 5 days records in php

4 Answers4