FOUND_ROWS always returns 1?

PHP
1

SELECT SQL_CALC_FOUND_ROWS * FROM products;
SELECT FOUND_ROWS();


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

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

querybrowser and phpmyadminboth put additional queries after your query, so found_rows() will not return an accurate number in those tools.

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

if the statistics for the primary key are inaccurate, then count(*) and found_rows() will return incorrect numbers.

5

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 :frowning:

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

I have stripped the mysql-class to a minimum but it still does not work.


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->result, MYSQLI_NUM);
	return $row[0];
	}
}

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

7

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:


$this->result = @mysqli_query($this->connection, $query);
$this->getFoundRows();
return new resultset($this->connection, $this->result);

Hope this works for you as well.

8

Interesting, that did the trick… thanks.