SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Hybrid View

  1. #1
    SitePoint Enthusiast owentech's Avatar
    Join Date
    Apr 2006
    Location
    Nairobi, Kenya
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Counting Total Records

    Hi,
    I have a quesry that sometimes retrieves very many records. This made it take a while. So i Introduced LIMIT and Previous and Next links.
    This all works quite well.

    My problem is that I'd like to know how many records in total will be returned (in all the pages) but i fear that this would be the same us running the query without the LIMIT, which would make it slow again.

    How can I overcome this?

    Abe
    Life is too short to think small - John Mason
    What is any life if not the pursuit of a dream? - Vanilla Sky
    PHP Membership Script

  2. #2
    SitePoint Zealot mwasif's Avatar
    Join Date
    Apr 2007
    Location
    Pakistan
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can run a query with COUNT() e.g.
    PHP Code:
    $rs mysql_fetch_array(mysql_query("SELECT COUNT(*) FROM table"));
    $total_results $rs[0]; 
    Or you can use SQL_CALC_FOUND_ROWS in the query to get the total records.
    PHP Code:
    $result mysql_query("SELECT SQL_CALC_FOUND_ROWS * FROM table LIMIT 0,10");
    $rs mysql_query(mysql_query("SELECT FOUND_ROWS()"));
    $total_results $rs[0];

    $rs mysql_fetch_array($result); 

  3. #3
    SitePoint Enthusiast owentech's Avatar
    Join Date
    Apr 2006
    Location
    Nairobi, Kenya
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by mwasif View Post
    You can run a query with COUNT() e.g.
    PHP Code:
    $rs mysql_fetch_array(mysql_query("SELECT COUNT(*) FROM table"));
    $total_results $rs[0]; 
    Or you can use SQL_CALC_FOUND_ROWS in the query to get the total records.
    PHP Code:
    $result mysql_query("SELECT SQL_CALC_FOUND_ROWS * FROM table LIMIT 0,10");
    $rs mysql_query(mysql_query("SELECT FOUND_ROWS()"));
    $total_results $rs[0];

    $rs mysql_fetch_array($result); 
    Is that faster than actually running the original unlimited query?
    Life is too short to think small - John Mason
    What is any life if not the pursuit of a dream? - Vanilla Sky
    PHP Membership Script

  4. #4
    SitePoint Zealot mwasif's Avatar
    Join Date
    Apr 2007
    Location
    Pakistan
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is that faster than actually running the original unlimited query?
    If you use mysql_num_rows() to count the total records then it is certainly faster.

  5. #5
    SitePoint Evangelist praetor's Avatar
    Join Date
    Aug 2005
    Posts
    479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code MySQL:
    select sql_calc_found_rows * from table limit 10; --the normal query
    select found_rows(); -- gives you the total rows

  6. #6
    SitePoint Enthusiast owentech's Avatar
    Join Date
    Apr 2006
    Location
    Nairobi, Kenya
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks.
    Life is too short to think small - John Mason
    What is any life if not the pursuit of a dream? - Vanilla Sky
    PHP Membership Script


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
  •