Select data that was added the last 7 days

Question

I have a table where the time is a date type. I would like to select all the records that were added the last 7 days and then out put them in an xml file. I can select all data and output it fine without a WHERE statement.

Here is the code:

$query_feed = "SELECT * FROM keysound_data WHERE time >=DATE_SUB(CURDATE(), INTERVAL 7 DAY AND time <= CURDATE()";
$feed = mysql_query($query_feed, $dconn) or die(mysql_error());
$row_feed = mysql_fetch_assoc($feed);
$totalRows_feed = mysql_num_rows($feed);



echo'<items>';
while ($row_feed = mysql_fetch_assoc($feed)){
            echo'

        <item>
            <name>'.$row_feed['Name'].'</name>
            <email>'.$row_feed['email'].'</email>
            <date>'.$row_feed['Date'].'</date>
            <description>'.$row_feed['Make'].' '.$row_feed['Model'].' '.$row_feed['Type'].'</description>
            <logon>'.$row_feed['Logon'].'</logon>
            <category>'.$row_feed['Type'].'/'.$row_feed['Make'].'</category>
            <product_search_code>'.$row_feed['Product_search_code'].'</product_search_code>
            <order_ref>'.$row_feed['Invoice'].'</order_ref>
            <product_link>'.$row_feed['Product_link'].'</product_link>
            <customer_ref>'.$row_feed['Invoice'].'</customer_ref>
            <amount>'.$row_feed['Price'].'</amount> 
            <currency>GBP</currency>
        </item>'; 
 }
 echo '</items>';

Not sure what's going wrong. Any help welcome

It's not necessary to invoke the DATE_SUB function. You could just `CURDATE() - INTERVAL 7 DAY` — spencer7593, Mar 20 '14 at 18:13

score 0 · Answer 1 · answered Mar 20 '14 at 18:07

0

You are missing a closing bracket.

Try this-

time >=DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND time <= CURDATE()

answered Mar 20 '14 at 18:07

Sahil Mittal

20,697
12
65
90

mathielo · Accepted Answer · 2014-03-20T18:19:12.697

0

You're missing a bracket in your SQL to close DATE_SUB function.

Try this:

SELECT * 
FROM keysound_data 
WHERE 
    time >=DATE_SUB(CURDATE(), INTERVAL 7 DAY)
    AND time <= CURDATE()

Better yet, you could use BETWEEEN to optmize your query:

SELECT * 
FROM keysound_data 
WHERE 
    time BETWEEN (CURDATE() - INTERVAL 7 DAY) AND CURDATE()

EDIT: As @spencer7593 noticed, CURDATE() - INTERVAL 7 DAY should be used instead of DATE_SUB(CURDATE(), INTERVAL 7 DAY) for even better optimization. The query above was updated to make use of that.

edited Mar 20 '14 at 18:19

answered Mar 20 '14 at 18:10

mathielo

6,725
7
50
63

1

For even more optimization, remove the DATE_SUB function entirely, and just use `CURDATE() - INTERVAL 7 DAY` – spencer7593 Mar 20 '14 at 18:15
That works great thanks. The problem is that when actually trying to output it in the xml no data is displayed. have you got an idea why that might be? – Ria Mar 20 '14 at 18:40
@Ria Have you turned on [error reporting](http://stackoverflow.com/a/845025/2752041) to see if any errors are being thrown? Also, how many rows you get when echoing `$totalRows_feed`? – mathielo Mar 20 '14 at 18:44
echoing out totalRows_feed gives me 1, which is correct. Just don't know whit is not showing in xml output – Ria Mar 20 '14 at 18:54
Removed the $row_feed = mysql_fetch_assoc($feed); $totalRows_feed = mysql_num_rows($feed); and now it does find the first and only row, not sure why this is happening but it was. Thanks for your help – Ria Mar 20 '14 at 18:56
@Ria That was exactly the problem: `mysql_fetch_assoc()` works as a **pointer**. Read [it's description](http://br1.php.net/mysql_fetch_assoc#refsect1-function.mysql-fetch-assoc-description) for better understanding. Once you call it, the first row fetched is stored in the variable (`$row_feed`, in your case) and the internal pointer is set for the next (second) row, but it doesn't exists. When you call `$row_feed = mysql_fetch_assoc($feed)` in your `while` it tries to fetch the second row, which doesn't exists. That's why removing the first `$row_feed` worked. ;) – mathielo Mar 20 '14 at 19:00

score 0 · Answer 3 · answered Mar 20 '14 at 18:16

0

SELECT * FROM keysound_data WHERE time BETWEEN (now() - INTERVAL 7 DAY) AND now()

answered Mar 20 '14 at 18:16

4EACH

2,132
4
20
28

Select data that was added the last 7 days

3 Answers3