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'

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).