SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict tlacaelelrl's Avatar
    Join Date
    Apr 2011
    Location
    Mexico city, Mexico
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    query a determined amount of results and count the total amount of records in a table

    Hello everyone, I am having trouble implementing COUNT in a query and would like some advice

    Given a query like

    Code:
    SELECT * FROM #__sometable ORDER by value DESC LIMIT 0, 20
    I already get the records but how can I get those 20 records plus the total amount of records in that table, what I am trying to do is provide a table which displays the records, 20 at a time and provide a list of numbers say 1, 2, 3, 4 where 1 represents records 0-20, 2 represents 21-40 and so on but want to create those numbers dynamically, so if the table contains only 20 records I want to be able no to print those numbers but if the table contains 60 records then I want to place the numbers 1, 2, 3 with a link to make the query to the respective results, in that case if I click on 3 I will dynamically create a query like

    Code:
    SELECT * FROM #__somtable ORDER by value DESC LIMIT 41, 20
    I know I can use COUNT but don't know how to implement it in the same query so I don't have to query the database twice
    Do you get bothered because I do the same thing every day?
    Do you question why I do it?
    Then find something that you actually like doing!!!

    Stop thinking on what I do.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you don't need COUNT

    look up the FOUND_ROWS function in da manual
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict tlacaelelrl's Avatar
    Join Date
    Apr 2011
    Location
    Mexico city, Mexico
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you don't need COUNT

    look up the FOUND_ROWS function in da manual
    Thank you for the quick answer, I read it and that does get the number of rows however I do still have to run another query right?
    Do you get bothered because I do the same thing every day?
    Do you question why I do it?
    Then find something that you actually like doing!!!

    Stop thinking on what I do.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    no, just your SELECT (using the SQL_CALC_FOUND_ROWS option), then retrieve the value of FOUND_ROWS
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict tlacaelelrl's Avatar
    Join Date
    Apr 2011
    Location
    Mexico city, Mexico
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Okay, I will try it, thank you for your help!
    Do you get bothered because I do the same thing every day?
    Do you question why I do it?
    Then find something that you actually like doing!!!

    Stop thinking on what I do.


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
  •