Pulling data from mysql database (this today/week/month)

[ukscotth]

Hi,

 

I have a table that contains posts and each post has a datetime field.

 

Im trying to work out how I can show entries from today, this week and this month so I can have a link that shows all posts from today or this month etc.

 

Any one know how I can do this ? My current piece of code that pulls the data from the db looks like this :

 

$texts= mysql_query("SELECT * FROM submittedtexts Order by id DESC LIMIT " . (($page - 1) * 6) . ", 6");

 

I want to add a bit that acts like : WHERE date = today

 

 

Hope that makes sense, Im a bit of a newbie

 

Thanks in advance,

 

Scott

[fugix]

you can use the date() function to get the current date with the desired format/timestamp

[ukscotth]

Hi,

 

Thanks, Ive been reading up about the date function but cant seem to get it working properly

[fugix]

can you show some of your code

[ukscotth]

sure.

 

Basically it just pulls the data from the database and displays it :

 

$texts= mysql_query("SELECT * FROM submittedtexts Order by id DESC LIMIT " . (($page - 1) * 6) . ", 6");

while ($got_texts = mysql_fetch_array($texts)){the code that simply displays the data}

[fugix]

what are you cancatenating on the end of your query...try

$texts= mysql_query("SELECT * FROM submittedtexts Order by id DESC LIMIT " . (($page - 1) * 6) . ", 6") or die(mysql_error());

to debug

[Pikachu2000]

You really don't want to use the PHP date function for this if you can avoid it. MySQL has time/date functions built-in HERE that are faster and more efficient. In what format are you storing the date? Is it in a DATE or DATETIME type data field? If it is, for today's date:

 

// if the field is DATETIME . . .
SELECT field1, field2 FROM table WHERE some_field = 'some_value' AND DATE(date_field) = CURDATE()

// if the field is DATE . . .
SELECT field1, field2 FROM table WHERE some_field = 'some_value' AND date_field = CURDATE()

 

Then you can add a BETWEEN clause in conjunction with MySQL's DATE_SUB() to get the last 7 days, or last month, etc.

 

SELECT field1, field2 FROM table WHERE some_field = 'some_value' AND date_field BETWEEN DATE_SUB( CURDATE(), INTERVAL 7 DAY ) AND CURDATE()

[ukscotth]

thanks very much for the replies and sorry for the slow reply.

 

That looks great but instead of grabbing the last 7 days I need it to grab data from this week so it would be all posts since Monday. If that makes sense.

 

Would that be easy to do ?

 

Thanks again.

 

Scott

[Pikachu2000]

For the current week of the current year . . .

 . . . WHERE DATE_FORMAT(`date_field`, '%u%Y') = DATE_FORMAT(CURDATE(), '%u%Y') . . . 

[xyph]

To grab the posts since Monday is tricky. Since Sunday is easier, as DAYOFWEEK() returns 1 for Sunday, 2 for Monday etc. So you'd need to use a conditional statement, I think.

 

Lemme test out some code.

[xyph]

Never mind, found WEEKDAY

 

SELECT `col1`, `date` FROM `table` WHERE `date` > DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY)

[ukscotth]

Excellent. Thanks so much, il give it a try when i get back on the main pc

[ukscotth]

Seems to work great. Sorry for being stupid but will this return posts from today or posts from this week ?

 

$sql = 'SELECT * FROM `submittedtexts` WHERE approved = 1 AND `date` > DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) Order by id DESC LIMIT '. (($page - 1) * 6) .', 6';

[xyph]

You probably want to use where `date` >= DATE_SUB(...

 

That's for this week.

 

For a day, use

 

SELECT `whatever` FROM `table` WHERE DAY(`date`) = 20 AND MONTH(`date`) = 6 AND YEAR(`date`) = 2011