CODEWITHSUNDEEP
phpmysqlsql
capernicuzzz
capernicuzzz

Reputation: 3

Display results from past 7 days PHP

What I'm trying to do is display articles from the past 30 days, but the code I have in place keeps giving me a mysql_fetch_assoc error which then traces back to my query

This is the code:

$sql = mysql_query("SELECT * 
                      FROM table 
                     WHERE DATE (datetime) = (CURDATE() - INTERVAL 7 DAY) 
                  ORDER BY ID DESC");

while ($runrows = mysql_fetch_array($sql))

Can someone give me pointers on how to actually make the code work properly? Here is the error

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

Upvotes: 0

Views: 3096

Answers (3)

Warface
Warface

Reputation: 5119

Something like:

$sql = mysql_query("SELECT * 
                    FROM table 
                    WHERE DATE BETWEEN '" . date('Y-m-d',strtotime('now')) . "' AND '" . date('Y-m-d',strtotime('-30 days')) . "' 
                    ORDER BY ID DESC"
);

Should get something like this:

SELECT * 
FROM table 
WHERE DATE BETWEEN '2011-07-26' AND '2011-06-26' 
ORDER BY ID DESC

Note that date('Y-m-d') is the way that the day is recorded in your database. So change it the way it is like your DB.

Upvotes: 0

Andreas Wong
Andreas Wong

Reputation: 60516

I don't think you need the ''s on your WHERE clause. Instead of

WHERE DATE (datetime) = 'CURDATE() - INTERVAL 30 DAY'

try:

WHERE DATE (datetime) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)

Upvotes: 2

Edoardo Pirovano
Edoardo Pirovano

Reputation: 8334

Try this:

$sql = mysql_query("SELECT * FROM table WHERE DATE (datetime) > DATE_SUB(CURDATE(), INTERVAL 7 DAY) ORDER BY ID DESC");

I've changed two things:

  1. Using DATE_SUB() to subtract date.
  2. Using >= instead of just = to find all dates in the past week.

Upvotes: 0

Related Questions