SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2010
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    limit - mysql query

    I would like to know how can i limit the output by the number of id_offers and not the number of rows. For example

    Code:
     SELECT  A.id_offer, T.tags  
                FROM offer A
                INNER JOIN offer_has_tags Z
                ON A.id_offer = Z.offer_id_offer
    
                INNER JOIN tags T
                ON Z.tags_id_tags = T.id_tags
    
                WHERE state = 0 
                ORDER BY date
                DESC LIMIT 0, 10
    output:

    Code:
     id_offer  tags
        77       xx 
        76       xx 
        76       xx 
        75       xx 
        75       xx 
        74       xx 
        74       xx
        73       xx
        73       xx
        72       xx

  2. #2
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is one of the most asked question I've ever seen in dev communities! it's called pagination.
    It's been asked too many times, so I thought this might be the first tip I should add here !
    Simply, all you have to do is add the LIMIT clause to your MySQL query and it should work fine!
    Sounds easy, doesn't it ?

    Now all let's see an example:

    PHP Code:
    <?PHP
    //this query will fetch 3 records only!
    $fetch = mysql_query("SELECT * FROM table LIMIT 3")or
    die(mysql_error());
    ?>
    Last edited by paul_wilkins; Jan 11, 2012 at 17:33. Reason: removed fake signature

  3. #3
    SitePoint Enthusiast
    Join Date
    Sep 2010
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Did you see my query ? What you suggest is exactly what i have.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Felito View Post
    I would like to know how can i limit the output by the number of id_offers and not the number of rows.
    may i ask what you're going to do with the tags?

    perhaps a GROUP_CONCAT of the tags for each id is feasible?

    so instead of producing this --
    Code:
    id_offer  tags
        77       xx 
        76       xx 
        76       xx 
        75       xx 
        75       xx 
        74       xx 
        74       xx
        73       xx
        73       xx
        72       xx
    the query will produce this --
    Code:
    id_offer  tags
        77       xx 
        76       xx,xx
        75       xx,xx
        74       xx,xx
        73       xx,xx
        72       xx
    and then LIMIT will work on the summary rows, one per id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Sep 2010
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    this solve my problem: thanks

    Code:
    SELECT  A.id_offer, T.tags  
    FROM 
      ( SELECT *
        FROM offer
        WHERE state = 0
        ORDER BY date DESC
        LIMIT 10
      ) A
    JOIN offer_has_tags Z
      ON A.id_offer = Z.offer_id_offer
    JOIN tags T
      ON Z.tags_id_tags = T.id_tags

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    that's nice... not all versions of mysql support LIMIT in a subquery, though

    http://dev.mysql.com/doc/refman/5.0/...ry-errors.html
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •