Jump to content


Photo

SELECT statement - display current month only


  • Please log in to reply
7 replies to this topic

#1 Panavision

Panavision
  • Members
  • Pip
  • Newbie
  • 4 posts

Posted 21 January 2003 - 11:32 PM

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

#2 DarthViper3k

DarthViper3k
  • Members
  • PipPipPip
  • Advanced Member
  • 50 posts

Posted 22 January 2003 - 12:26 AM

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
[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--]
switch($php) {
    case = \"given\" : {
       $website = 1;
       break;
    }     case = \"taught\" : {        $website = lifetime;        break;     } } [/span][!--PHP-Foot--][/div][!--PHP-EFoot--] The lazy never learn. The lazy are the downfall of many great things. Take the time to learn.

#3 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 22 January 2003 - 12:46 AM

you can use the mysql date functions for both of these requirements:
http://www.mysql.com..._functions.html
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#4 Panavision

Panavision
  • Members
  • Pip
  • Newbie
  • 4 posts

Posted 22 January 2003 - 08:28 PM

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

$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... :?:

#5 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 23 January 2003 - 10:24 AM

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

$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
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center

#6 Panavision

Panavision
  • Members
  • Pip
  • Newbie
  • 4 posts

Posted 23 January 2003 - 10:36 AM

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


#7 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 23 January 2003 - 10:54 AM

You are too lazy!

Look at http://www.mysql.com..._functions.html

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

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

P., denmark
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center

#8 Panavision

Panavision
  • Members
  • Pip
  • Newbie
  • 4 posts

Posted 23 January 2003 - 03:20 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users