Limit - mysql query

Databases
1

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

 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:

 id_offer  tags
    77       xx
    76       xx
    76       xx
    75       xx
    75       xx
    74       xx
    74       xx
    73       xx
    73       xx
    72       xx
2

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 ? :wink:

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

3

Did you see my query ? What you suggest is exactly what i have.

4

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 –


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 –


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

5

this solve my problem: thanks

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

that’s nice… not all versions of mysql support LIMIT in a subquery, though

http://dev.mysql.com/doc/refman/5.0/en/subquery-errors.html