Page 1 of 1

Get data from last 7 days

Posted: Fri Jan 21, 2011 6:05 am
by Tassadduq
i have a table in y database named 'news'. i have a field named 'hits' which is auto updated by views of news. i saved the news submission date in 'addedon' field of my table. Every things working fine. now i want to make a new section which will shows the 'Most Viewed News of this week'. for this purpose i need to differentiate the current date and news submission date for one week/ 7days. which query i should use to get the most viewed news of last 7 days? the news submission date stored in database in this format (15/09/2010). how to get the most viewed news of the last 7 days?

Re: Get data from last 7 days

Posted: Fri Jan 21, 2011 6:23 am
by VladSun
Just add the condition in your WHERE clause:

Code: Select all

WHERE 
    addedon >= NOW() - INTERVAL 1 WEEK

Re: Get data from last 7 days

Posted: Fri Jan 21, 2011 6:43 am
by Tassadduq
VladSun wrote:Just add the condition in your WHERE clause:

Code: Select all

WHERE 
    addedon >= NOW() - INTERVAL 1 WEEK

you mean to say like this

$query = mysql_query("SELECT * FROM news WHERE addedon >= NOW() - INTERVAL 1 WEEK");

?

Re: Get data from last 7 days

Posted: Fri Jan 21, 2011 7:55 am
by VladSun
The query is syntactically right. Now you have to add the "top most visited" feature to it.
Take a look at ORDER BY and LIMIT in the MySQL manual.

Re: Get data from last 7 days

Posted: Fri Jan 21, 2011 9:12 am
by John Cartwright
VladSun wrote:Just add the condition in your WHERE clause:

Code: Select all

WHERE 
    addedon >= NOW() - INTERVAL 1 WEEK
I always assumed INTERVAL was only a parameter for the date_* functions and did not know you can use it directly in operations. Interesting.

Re: Get data from last 7 days

Posted: Fri Jan 21, 2011 11:59 pm
by Tassadduq
VladSun wrote:The query is syntactically right. Now you have to add the "top most visited" feature to it.
Take a look at ORDER BY and LIMIT in the MySQL manual.

here is my complete query to fetching data.

Code: Select all

[syntax=php]$sqlnews = mysql_query("SELECT * FROM news WHERE addedon >= NOW() - INTERVAL 1 WEEK ORDER BY newsid DESC LIMIT 10");
$numnews = mysql_num_rows($sqlnews);
if($numnews > 0){
	while($getnews = mysql_fetch_array($sqlnews)){
$date1 = $getnews['addedon'];
echo $date1;		
	}
}
[/syntax]
if the query is okay so why it shows 0 record?

Re: Get data from last 7 days

Posted: Sat Jan 22, 2011 12:58 am
by Tassadduq
anyone help please to sort out this issue?

Re: Get data from last 7 days

Posted: Sat Jan 22, 2011 3:14 am
by Tassadduq
i made a query to get the current date and subtract 7 days from it and try to get the record from database where addedon date is greater than subtracted date. here is my code
<?PHP
$cdate = date('d/m/Y');
$subdate = $cdate - 7;
$fdate = $subdate.date('/m/Y');
$sqlnews = mysql_query("SELECT * FROM news WHERE addedon > $fdate ORDER BY hitcounter DESC LIMIT 5");
$numnews = mysql_num_rows($sqlnews);
if($numnews > 0){
while($getnews = mysql_fetch_array($sqlnews)){
$addedon = $getnews['addedon'];
echo $addedon.'<br />';
}
}
?>


but this got the 5 most viewed news from the overall table. i need to get the 5 news which are most viewed from last 7 days.
please anyone help. thanks in advance.

Re: Get data from last 7 days

Posted: Sun Jan 23, 2011 7:07 am
by VladSun
You don't need to do it in PHP, MySQL would do it for you...

You wrote:

Code: Select all

SELECT * FROM news WHERE addedon >= NOW() - INTERVAL 1 WEEK ORDER BY newsid DESC LIMIT 10
Then you wrote:

Code: Select all

... ORDER BY hitcounter DESC LIMIT 5
I think it should be:

Code: Select all

SELECT * FROM news WHERE addedon >= NOW() - INTERVAL 1 WEEK ORDER BY hitcounter DESC LIMIT 10
Also, what's the addedon column type? It SHOULD not be VARCHAR or CHAR - should be DATE... If you are using char type for storing your date data, then you will have a lot of troubles.

Re: Get data from last 7 days

Posted: Mon Jan 24, 2011 3:40 am
by Tassadduq
unfortunately the datatype of my addedon field is 'varchar' :( :banghead: and the news submission date is stored in database in this (24/01/2011) :(((
is there any solution for this :? ??

Re: Get data from last 7 days

Posted: Mon Jan 24, 2011 6:08 am
by VladSun
1. Add a new column (e.g. addedon_temp) of type DATE.
2. Use MySQL function STR_TO_DATE to get DATE values from addedon column into the addedon_temp column.
3. Check that conversion is OK.
4. Remove the addedon column and rename addedon_temp to addedon.

You may always use the DATE_FORMAT() MySQL function to get whatever date format you want, though I would prefer doing it in PHP (in the View).