SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    966
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    MySQL Limit related question

    Hai folks,

    example :
    PHP Code:
    $query="SELECT * FROM properties LIMIT 10,6;
    if (
    $result=mysql_query($query) or die (mysql_error())); 
    $tot=mysql_num_rows($result);
    echo "
    number of records found " . $tot
    Assume data base will have 50 matching records for the above query.
    Now $tot will show 6 records found since we limit the results.
    my question is, do we need to run the same query once without the LIMIT command to get the actual total 50? any other solutions ?

  2. #2
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think something like this should work...

    PHP Code:
    SELECT COUNT(id) AS totalother_fields FROM properties LIMIT 10,6

    $tot 
    $result['total']; 

  3. #3
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,094
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    You can use the query hint SQL_CALC_ROWS and then FOUND_ROWS() to obtain the total number of rows that would have been found without the LIMIT.

    SELECT SQL_CALC_ROWS etc etc

    See MySQL :: MySQL 5.0 Reference Manual :: 11.13 Information Functions

    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  4. #4
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    966
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks folks for the reply, let me chk your suggessions..

  5. #5
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    966
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by tgavin View Post
    I think something like this should work...

    PHP Code:
    SELECT COUNT(id) AS totalother_fields FROM properties LIMIT 10,6

    $tot 
    $result['total']; 
    what is this (id) here? i know count() will count the no.of elemnts in an array.
    but here id

  6. #6
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by afridy View Post
    what is this (id) here? i know count() will count the no.of elemnts in an array.
    but here id
    I was using the id (primary key) field of the table as an example. You may have it named something else.

  7. #7
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    966
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by tgavin View Post
    I was using the id (primary key) field of the table as an example. You may have it named something else.
    alright

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by afridy View Post
    Thanks folks for the reply, let me chk your suggessions..
    please check post #3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    966
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by tgavin View Post
    I was using the id (primary key) field of the table as an example. You may have it named something else.
    hai tgavin,

    Code:
    $query="SELECT count(*) AS total FROM properties WHERE status='1' AND type='$type' ORDER BY dt DESC LIMIT $from,6";
    thats the query now i am using with count(),

    but,

    Code:
    $totrecs = $result['total'];
    echo $totrecs;
    returnes nothing though many records returned. any syntax issue?

  10. #10
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    966
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    You can use the query hint SQL_CALC_ROWS and then FOUND_ROWS() to obtain the total number of rows that would have been found without the LIMIT.

    SELECT SQL_CALC_ROWS etc etc

    See MySQL :: MySQL 5.0 Reference Manual :: 11.13 Information Functions

    This works charm for me !!!!
    then ill settle with this method folks

    Code:
    $query="SELECT SQL_CALC_FOUND_ROWS * FROM properties WHERE status='1' AND type='$type' ORDER BY dt DESC LIMIT $from,6";
    if ($result=mysql_query($query) or die (mysql_error()));
    $tot = mysql_query( "SELECT FOUND_ROWS( )" );
    echo "found " . mysql_result($tot , 0) . " Records";

  11. #11
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    966
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    please check post #3
    Worked


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
  •