show top article by interval

[Linddha]

i have a problem to show the top article in 7 days..

        $arc=mysql_query("select *from arc_news where status='aktif' and date IN (SELECT DATE_(NOW(), INTERVAL -14 DAY)) order by counter desc limit 4");
        while($a=mysql_fetch_array($arc)){
            $banner=mysql_query("select * from arc_sub where id_sub='$a[id_sub]'");
            while($b=mysql_fetch_array($banner)){
            echo"
                <tr>
                <td class='ling'><b style='color:GoldenRod'>&bull;</b></td><td><h1><a href='article".$bb."-$a[id_news]-$b[id_banners]-$a[judul_seo].html' title='$a[judul]' target='_blank'>$a[judul]</a></h1></td>
                </tr>";
            }
        }

i want to show the best article base on the 'counter' every week, limit 4 ?
but unfortunately my script didn't work at all,, what's wrong with my query?

[pritaeas]

Try:

SELECT * FROM `arc_news` WHERE `status` = 'aktif' 
AND `date` BETWEEN DATE_ADD(NOW(), INTERVAL -14 DAY)) AND NOW() ORDER BY `counter` DESC LIMIT 4

[Linddha]

it didn't work.. :(

bdw i use datatype datetime as my date fields..
is it relate to this problem?

then if i use datatype datetime.. how about the script

[pritaeas]

it didn't work

That's too vague. Be more specific, did you get errors? Without more information it's very hard to test this (table structure and sample data needed).

[Linddha]

there is no error
but only can't showing the data from table

[pritaeas]

You can always use DATEDIFF as shown in your other thread.

[Linddha]

okey thanks alot pritaeas :)
all works !