SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    FOUND_ROWS always returns 1?

    SELECT SQL_CALC_FOUND_ROWS * FROM products;
    SELECT FOUND_ROWS();

    Code:
    FOUND_ROWS()  
    1
    What could be wrong? Running MySQL 5.0.13..
    I have tried both in a php-script, phpmyadmin and in mysql query browser.
    Yes the table has more than 1 record, tried with other tables to.

    Edit:


    Forgot, this also does not work:
    SELECT SQL_CALC_FOUND_ROWS * FROM products LIMIT 0,3;
    SELECT FOUND_ROWS();

  2. #2
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok now it works, i did repair and optimize on all my tables.
    any know what caused this and how come it works after repair/optimize?

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    querybrowser and phpmyadminboth put additional queries after your query, so found_rows() will not return an accurate number in those tools.
    Code:
    mysql> select count(*) from test;
    +----------+
    | count(*) |
    +----------+
    |   410012 |
    +----------+
    1 row in set (0.00 sec)
    
    mysql> select sql_calc_found_rows * from test limit 0, 3;
    +----+
    | id |
    +----+
    |  1 |
    |  2 |
    |  3 |
    +----+
    3 rows in set (0.00 sec)
    
    mysql> select found_rows();
    +--------------+
    | found_rows() |
    +--------------+
    |       410012 |
    +--------------+
    1 row in set (0.00 sec)

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jessica_s76
    ok now it works, i did repair and optimize on all my tables.
    any know what caused this and how come it works after repair/optimize?
    if the statistics for the primary key are inaccurate, then count(*) and found_rows() will return incorrect numbers.

  5. #5
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    this is getting crazy, ok if i just do a simple

    $query1 = "SELECT SQL_CALC_FOUND_ROWS * FROM products LIMIT 0,3";
    $query2 = "SELECT FOUND_ROWS();";
    $result1 = mysqli_query($link, $query1);
    $result2 = mysqli_query($link, $query2);

    It works... but when i use my own mysqlclass it does not work, i have cheked to see that the same $link is used when doing both queries in my class but still it does not work, how can i debug?

    Now its more of a php-question i know

    I do this with my class

    $db = &new db("login_details");
    $result1 = $db->query("SELECT SQL_CALC_FOUND_ROWS * FROM products LIMIT 0,3;");
    $result2 = $db->query("SELECT FOUND_ROWS();");

  6. #6
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have stripped the mysql-class to a minimum but it still does not work.


    PHP Code:
    class db {
        public 
    $connection;
        private 
    $host;
        private 
    $port;
        private 
    $username;
        private 
    $password;
        private 
    $database;
        
        function 
    __construct($host$username$password$database$port "3306") {
            
    $this->host $host;
            
    $this->username $username;
            
    $this->password $password;
            
    $this->database $database;
            
            
    $this->connect();
        }
        
        private function 
    connect() {
            
    $this->connection = @mysqli_connect($this->host$this->username$this->password$this->database);
            return 
    true;
        }
        
        public function 
    query($query) {
            
    $this->result = @mysqli_query($this->connection$query);
            return new 
    resultset($this->connection$this->result);
        }
    }

    class 
    resultset {
        function 
    __construct($connection$result) {
            
    $this->connection $connection;
            
    $this->result $result;
        }
        
        public function 
    fetchrow() {
        
    $row mysqli_fetch_array($this->resultMYSQLI_NUM);
        return 
    $row[0];
        }

    Maybe a moderator should move this thread to the php forums?

  7. #7
    SitePoint Enthusiast Zeldinha's Avatar
    Join Date
    Sep 2004
    Location
    Barcelona [Spain]
    Posts
    89
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I had to make something similar once, so what I did was to create a function that calls the FOUND ROWS query, and then put it right after the query. So your query() function would look like:

    PHP Code:
    $this->result = @mysqli_query($this->connection$query);
    $this->getFoundRows();
    return new 
    resultset($this->connection$this->result); 
    Hope this works for you as well.

  8. #8
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Interesting, that did the trick.. thanks.


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
  •