SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    Apr 2006
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    multiple query comparison

    Hi All,

    I am looking for help from experts, eventhough the solution for my query might be simple.

    I have 3 queries to display latest news

    Query 1. Select latest 1 record to show the bigger thumbnail.
    Query 2 Select the second and third record in the query to show smaller than image in the query 1.

    Third query is to show 10 results from the rest of the date from db. But since the second query returns 2 results, I am not able to pass the id to a variable and compare that in the query like I am able to do for the second query where I need to compare only one record, but second query return return 2 results.

    So I like to write a query which omit first 3 records in the database and return the remining 10 results from the news list database.

    Could some one help me on this...

    Thanks in advance

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    first, which database system is this?

    second, could we see the queries please?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Apr 2006
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile

    Thanks for your reply.

    It is mysql and following are the queries I have used for first 2

    For displaying the first news with Big Thumb Image
    -------------------------------------------------
    $sql = "SELECT id, title, LEFT(before_src, InSTR(before_src, '.')) as Content, date, thumb FROM newsArticles WHERE published='yes' AND thumb != ''
    ORDER BY id DESC LIMIT 1";


    For displaying the latest news other than first one with small Thumb Image
    -----------------------------------------------------------------------

    SELECT id, title, LEFT(before_src, InSTR(before_src, '.')) as Content, date, thumb FROM newsArticles WHERE id != $id AND published='yes' AND
    thumb != '' ORDER BY id DESC LIMIT 2";

    Here $id get the value from the first query. Also check that if no thumb image available, omit the news.

    Third query should avoid the first query result of 1 and second query result of 2. So next five news (also include the omitted news item due to empty image) should be fetched in third.

    Hope my explanation is clear....

    Thanks in advance

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    use LIMIT 3,10
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Apr 2006
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi
    Thank you for your reply. But actually I am omitting records in the first and second query where there is no thumbnail images even if the record is the latest one. So like to include those records as well in the third query where there are no images displayed. So Limit will not work in this scenario. Is there any other way?

    Thanks in advance

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    If the rows selected by the first two queries aren't the first three (ordering by id DESC), because among the first rows there are some that have an empty image, in the 3rd query you might exclude them using
    Code:
    WHERE id NOT IN ($id1, $id2, $id3)

  7. #7
    SitePoint Member
    Join Date
    Apr 2006
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Guido 2004,

    The fact is the second query got 2 results and how could we pass the values to variables ($id2 and $id3) ? Since the result is an array . I am a learner and sorry for the silly queries.....

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    How do you pass the first id to $id?

  9. #9
    SitePoint Member
    Join Date
    Apr 2006
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Since first query fetch only one result it is passing as

    $sql = "SELECT id, title, LEFT(before_src, InSTR(before_src, '.')) as Content, date, thumb FROM newsArticles WHERE published='yes' AND thumb != ''
    ORDER BY id DESC
    LIMIT 1";

    $result = mysql_query($sql);
    $row = mysql_fetch_assoc($result);
    extract($row);

    $id= $row['id']; // *** here passes the value to variable ***

    But second one got 2 results and i am stuck there.....

  10. #10
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    But I'm sure your doing something with those 2 results? A while loop?
    Inside that loop, put the two ids in an array, or construct part of the IN condition.


Tags for this Thread

Bookmarks

Posting Permissions

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