SELECT statement - display current month only

[Panavision]

FROM (tblNews LEFT JOIN tblNews_Picture ON tblNews.News_ID = tblNews_Picture.News_ID),  

tblAuthor WHERE tblNews.Author_ID = tblAuthor.Author_ID  

 

My problem is to DISPLAY news items where tblnews.newsdate = CURRENT MONTH. Also, how do I display news from previous months (for archive)?

[DarthViper3k]

hmmm

 

I\'m not 100% sure bout this

but

you can give the news posts a variable of the month

 

and you can use either and switch or if statement to determine what month to display

 

as for archives....

I\'m not sure bout that part

I\'m just catching up on SQL

lol

[effigy]

you can use the mysql date functions for both of these requirements:

http://www.mysql.com/doc/en/Date_and_time_..._functions.html

[Panavision]

I tried this and didn\'t work, geez I suck at SQL statements

 

$news=mysql_query(\\\"SELECT *

FROM (tblNews LEFT JOIN tblNews_Picture ON tblNews.News_ID = tblNews_Picture.News_ID),  

tblAuthor WHERE tblNews.Author_ID = tblAuthor.Author_ID  

 

AND tblNews.NewsDate = month(now())

 

Help please... :?:

[pallevillesen]

I tried this and didn\'t work, geez I suck at SQL statements    

 

$news=mysql_query(\\\"SELECT *

FROM (tblNews LEFT JOIN tblNews_Picture ON tblNews.News_ID = tblNews_Picture.News_ID),  

tblAuthor WHERE tblNews.Author_ID = tblAuthor.Author_ID  

 

AND tblNews.NewsDate = month(now())

 

Help please... :?:

 

Try and show us a:

 

select * from tblNews LIMIT 5;

 

Your problem is probably comparing a DATE to a MONTH ? SO yuo should format your tblNews.Newsdate to a month (dependent on format) as well...

 

Probably

$news=mysql_query("SELECT * 

FROM (tblNews LEFT JOIN tblNews_Picture 

ON tblNews.News_ID = tblNews_Picture.News_ID), tblAuthor 

WHERE (tblNews.Author_ID = tblAuthor.Author_ID)

AND (

MONTH(tblNews.NewsDate) = MONTH(CURDATE())

)";

 

will do the job... (HINT: if your NewsDate is a varchar - well you just need a number from [1, 12] to compare with the MONTH(CURDATE()), which returns a number [1,12])

 

P., denmark

[Panavision]

Thanks, I did solve it in the end doing something similar

 

What if I wanted to check against, say JULY 2002?

 

 

news=mysql_query("SELECT * 

FROM (tblNews LEFT JOIN tblNews_Picture ON tblNews.News_ID = tblNews_Picture.News_ID), 

tblAuthor WHERE tblNews.Author_ID = tblAuthor.Author_ID 



AND MONTH(tblNews.NewsDate = month******)

[pallevillesen]

You are too lazy!

 

Look at http://www.mysql.com/doc/en/Date_and_time_..._functions.html

 

extract(YEAR_MONTH(date)) is what you\'re looking for....

 

It will return the date \'2002-07-23\' like 200207

 

P., denmark

[Panavision]

:oops: Missed it when I browsed it earlier, thanks