How to Use MySQL Found_Rows() in PHP

How to use MySQL Found_Rows() in PHP?

SQL_CALC_FOUND_ROWS is only useful if you're using a LIMIT clause, but still want to know how many rows would've been found without the LIMIT.

Think of how this works:

SELECT SQL_CALC_FOUND_ROWS * FROM Users;

You're forcing the database to retrieve/parse ALL the data in the table, and then you throw it away. Even if you aren't going to retrieve any of the rows, the DB server will still start pulling actual data from the disk on the assumption that you will want that data.

In human terms, you bought the entire contents of the super grocery store, but threw away everything except the pack of gum from the stand by the cashier.

Whereas, doing:

SELECT count(*) FROM users;

lets the DB engine know that while you want to know how many rows there are, you couldn't care less about the actual data. On most any intelligent DBMS, the engine can retrieve this count from the table's metadata, or a simple run through the table's primary key index, without ever touching the on-disk row data.

Is there a way to use SELECT FOUND_ROWS() in php and mysqli?

You already have the counting query, just fetch that value. No need for another second query FOUND_ROWS().

$stmt = $conn->prepare('SELECT count(*) FROM owned_assets WHERE uid=? AND type=?');
$stmt->bind_param('ii', $uid, $assetType);
$stmt->execute();
$result = $stmt->get_result();
$total = $result->fetch_assoc();
echo $total['count(*)'];

Sidenote: You can use an alias to count(*) AS total in the query, and access the index as $total['total'] in the return value.

MySQL how to store FOUND_ROWS() in a PHP variable

If this is Wordpress, you could do alternatively for the second statement, otherwise you were asking for an array an not the number:

$count = $wpdb->get_var('SELECT FOUND_ROWS()');

Also take care that the database class of wordpress is open to change of any query due to filters and it suppresses errors by default doing the query and fetching from the resultset.

You should enable screaming to see if you run into specific errors.

  • $wpdb->show_errors = true shows errors automatically, if WP_DEBUG is set to true. (ref)

SQL_CALC_FOUND_ROWS / FOUND_ROWS() does not work in PHP

Thank you.

When I ran something analogous to your example on the mysql command line, it would work; but running it from php, it failed. The second query has to "know about" the first one, so I figure somehow that persistence/memory linking the two queries was getting messed up by the php.

(It turns out that Wordpress uses this type of query to do its pagination - so our larger problem was that the pagination in a wordpress install suddenly stopped working when we moved to php 5.2.6 ... eventually tracked it down to the FOUND_ROWS()).

Just for the sake of posting for people who may run into this in the future... for me it was the php setting "mysql.trace_mode" - this defaulted "on" in 5.2.6 instead of "off" like previously, and for some reason prevents the FOUND_ROWS() from working.

As a "fix", we could either put this in every php page (actually, in a common "include"):

ini_set("mysql.trace_mode", "0");

or add this to the .htaccess:

php_value mysql.trace_mode "0"

Thanks again,
Jerry

How can I use FOUND_ROWS() function in PDO?

All you need to do is run a ->query('SELECT FOUND_ROWS()') after your original execute.

$sth = $this->dbh->prepare("SELECT SQL_CALC_FOUND_ROWS *,
u.id user_id,
u.avatar,
u.date_time,
CONCAT(u.user_fname, ' ', u.user_lname) name,
sum($this->table_alias.vote_value) vote_value,
sum($this->table_alias.score) score,
$category_in_the_select AS tc $tag_in_the_select AS tt
FROM users u
JOIN reputations $this->table_alias ON u.id = $this->table_alias.owner_id $query_join
WHERE $time_limitation $query_where
GROUP BY user_id, u.avatar, u.date_time, name, tc, tt
ORDER BY score DESC, vote_value DESC
LIMIT :j, $this->per_page");
$this->parameters[":j"] = $j;
$sth->execute($this->parameters);
$users = $sth->fetchAll(PDO::FETCH_ASSOC);

$count = $this->dbh->query('SELECT FOUND_ROWS()')->fetchColumn();

PHP PDO SQL_CALC_FOUND_ROWS / FOUND_ROWS() strange issue

Well looks like the culprit was New Relic. I disabled the daemon quick to see if that was the issue and counts are perfect again.

Found my answer here: PHP PDO returning inconsistent results for SELECT FOUND_ROWS()

I decided to wrap my queries with a single space at the beginning and end (only beginning is necessary). Note that "\n" characters work as well. This way I didn't have to mess with the New Relic configuration.

Thanks New Relic!

Mysql FOUND_ROWS with HAVING statement

As it turns out, it appears that the code IS running as intended. It was just a debugging issue. Sorry to disturb!



Related Topics



Leave a reply



Submit