Get data from last 7 days

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Tassadduq
Forum Commoner
Posts: 60
Joined: Wed Dec 03, 2008 2:53 pm

Get data from last 7 days

Post 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?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Get data from last 7 days

Post by VladSun »

Just add the condition in your WHERE clause:

Code: Select all

WHERE 
    addedon >= NOW() - INTERVAL 1 WEEK
Last edited by VladSun on Mon Jun 20, 2011 3:53 am, edited 1 time in total.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Tassadduq
Forum Commoner
Posts: 60
Joined: Wed Dec 03, 2008 2:53 pm

Re: Get data from last 7 days

Post 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");

?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Get data from last 7 days

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Get data from last 7 days

Post 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.
User avatar
Tassadduq
Forum Commoner
Posts: 60
Joined: Wed Dec 03, 2008 2:53 pm

Re: Get data from last 7 days

Post 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?
User avatar
Tassadduq
Forum Commoner
Posts: 60
Joined: Wed Dec 03, 2008 2:53 pm

Re: Get data from last 7 days

Post by Tassadduq »

anyone help please to sort out this issue?
User avatar
Tassadduq
Forum Commoner
Posts: 60
Joined: Wed Dec 03, 2008 2:53 pm

Re: Get data from last 7 days

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Get data from last 7 days

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Tassadduq
Forum Commoner
Posts: 60
Joined: Wed Dec 03, 2008 2:53 pm

Re: Get data from last 7 days

Post 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 :? ??
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Get data from last 7 days

Post 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).
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply