CODEWITHSUNDEEP
phpmysqlsqlpdophp-5.3
mehran
mehran

Reputation: 62

mysql FOUND_ROWS() return wrong value

I'm trying to use FOUND_ROWS() in my query, but the function returns wrong values sometimes.

SELECT SQL_CALC_FOUND_ROWS adminslog.*, admins.fullName FROM adminslog 
JOIN admins ON admins.id=adminslog.userId ORDER BY date DESC LIMIT 0,12

In this query, I get the right value for some, but in others the limit has the wrong value.

LIMIT 0,12        164rows        right
LIMIT 12,12       164rows        right
LIMIT 36,12       164rows        right
LIMIT 48,12       164rows        right
LIMIT 50,12       60rows         wrong
LIMIT 62,12       60rows         wrong

Here is my class construct:

class list_table
{
public $number,$page_number,$all_rec,$table_rows,$query_str,$query,$fetch,$table,$db,$fields,$i=0,$page_n,$onclick;

function __construct($query_str,$fields,$page_n,$onclick,$page_number,$number,$db)
{
    $this->fields = $fields; $this->page_number = (((int)$page_number<1)?1:(int)$page_number); $this->number = (int)$number; $this->db = $db;
    $this->i = $this->page_number*$this->number-$this->number; $this->page_n = $page_n; $this->onclick = $onclick;
    $this->query_str = substr(trim($query_str),0,7)."SQL_CALC_FOUND_ROWS ".substr(trim($query_str),7)." LIMIT ".(($this->page_number*$this->number)-$this->number).",".$this->number;

    $this->query = $this->db->query($this->query_str);
    $this->table_rows = $this->db->query("SELECT FOUND_ROWS()")->fetchColumn();

    $this->all_rec = $this->query->rowCount();
    $this->fetch = $this->query->fetch();

    //$this->table_rows = $this->table_rows->fetch();
    //$this->table_rows = $this->table_rows['cnt'];
    print $this->table_rows;
}

other functions...
}

Upvotes: 1

Views: 1583

Answers (2)

rodjin
rodjin

Reputation: 21

A mysql bug can be responsible for this issue, depending on which version you use: http://bugs.mysql.com/bug.php?id=1468

You can workaround it by using a GROUP BY clause in your query.

Upvotes: 2

Pooya
Pooya

Reputation: 1558

question: you think your first FOUND_ROWS() is right every time in every query?

check that,if that is true you can run this code only in first query and save it to session.

if($this->page_number==1) $_SESSION['cr'] = $this->table_rows = $this->db->query("SELECT FOUND_ROWS()")->fetchColumn();

in this way you have not to check row counts every time.

Upvotes: 1

Related Questions