Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Posts
    15

    Question Unanswered: Select Statement?

    $news=mysql_query("SELECT * FROM tblNews, tblAuthor, tblNews_Picture
    WHERE
    tblNews.Author_ID = tblAuthor.Author_ID
    AND
    tblNews.News_ID = tblNews_Picture.News_ID

    ORDER BY NewsDate DESC") OR die ("cant get data");
    There are THREE MAIN tables:

    tblNews
    tblAuthor
    tblPicture

    And Fourth table for connecting tblNews.News_ID to tblPicture.Picture_ID
    tblNews_Picture - Any news item can use the same picture, which is why there is this table.


    Some news items might not have a picture, so the original SELECT statement is wrong, as it will only output records which have Pictures. So, basically, I want to output all records for January, and display picture if need be, if no picture, still display the other fields of the record.

    Could I have multiple queries? How would do that within the PHP code?
    I'm a novice at MySQL, so be gentle.

  2. #2
    Join Date
    Sep 2002
    Location
    Kyiv, Ukraine
    Posts
    77
    You need you to use LEFT JOIN in your query:

    Code:
    SELECT *
       FROM (tblNews LEFT JOIN tblNews_Picture ON tblNews.News_ID = tblNews_Picture.News_ID), tblAuthor
       WHERE tblNews.Author_ID = tblAuthor.Author_ID
       ORDER BY NewsDate DESC;
    Very good and understandable article about SQL joins on DevShed:
    http://www.devshed.com/Server_Side/M...ins/page1.html
    Yours faithfully,
    Yaroslav Zaremba

  3. #3
    Join Date
    Jan 2003
    Posts
    15

    Question

    Thanks AzA

    Now, if I want to display just the current month's news?

    And also, if I want to archive last month's news, or even July of last year, how do I do it?


    tblNews.NewsDate = currentmonth...?

  4. #4
    Join Date
    Sep 2002
    Location
    Kyiv, Ukraine
    Posts
    77
    Originally posted by Panavision
    ...
    And also, if I want to archive last month's news, or even July of last year, how do I do it?


    tblNews.NewsDate = currentmonth...?
    Exactly. Just put comma after your first condition in "WHERE" and write another one there related to your date needs, like this:
    Code:
    WHERE ..... , (tblNews.NewsDate >= _startdate AND tblNews.NewsDate <= _enddate)
    Yours faithfully,
    Yaroslav Zaremba

  5. #5
    Join Date
    Jan 2003
    Posts
    15
    FOR PREVIOUS MONTHS
    How could I make _startdate a date, if you see what I mean.

    AND for CURRENT MONTH, I tried this and didn't work

    $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())
    Thanks

  6. #6
    Join Date
    Jan 2003
    Posts
    15
    Sorted the problem for Current Month, but not sure how to use the function to retrieve data from another MONTH like December 2002.

    This is for Current Month:

    AND month(tblNews.NewsDate) = month(now())

  7. #7
    Join Date
    Jan 2003
    Posts
    15
    I can't get my pictures to display, they come from a seperate PHP page. Everything else works, but can't get the picture to display using this query:

    My SQL 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(now())
    ORDER BY NewsDate DESC LIMIT
    I tested and I got these headings with data using PHPmyAdmin

    TBLNEWS table:
    News_ID
    NewsTitle
    Author_ID
    NewsText
    NewsDate

    NEWS_PICTURE table (connects TBLNEWS to TBLPICTURE)
    News_ID
    Picture_ID

    TBLAUTHOR
    Author_ID
    AuthorName
    AuthorEmail


    Should I include the TBLPICTURE table in the query? I ask because all pictures are processed on a seperate page. I thought I only need the many to many table which is NEWS_PICTURE.

    How could Implement the tblpicture table in my query?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •