Undefined method for found_rows

[genista]

Hi,

I am working on an infinite scroll script and am having trouble getting the number of rows using PDO. In the following snippet on the last line I am getting an error: Call to undefined method PDOStatement::query(), what should replace 'query' here?

	$sqlContent="SELECT name, age FROM users WHERE userid= $userid";

//Query start point
$start =($page * $limit)- $limit;

$resContent=$DB_con->query($sqlContent);
//$rows_returned = $resContent->num_rows;
    $rows_returned= $resContent->query("SELECT FOUND_ROWS()");

Thanks,

G

[laserlight]

genista wrote:

I am working on an infinite scroll script and am having trouble getting the number of rows using PDO.

Do you really need the number of rows? If not, it would be better to order by say, date/time, then retrieve based on the newest/oldest row in the previous result set. If that column is indexed, this tends to be more efficient than using LIMIT. Typically, the LIMIT method is convenient when you want pagination, but in an "infinite scroll", you don't: you just need to keep retrieving the next set of results.

genista wrote:

In the following snippet on the last line I am getting an error: Call to undefined method PDOStatement::query(), what should replace 'query' here?

Refer to the PHP manual concerning the PDO extension. You will see that the query method is a member of the PDO class, but once you have called prepare on an object of the PDO class to get a PDOStatement object, you would use the execute method of the PDOStatement class.

What you probably wanted to do is this:

$sql = "SELECT COUNT(*) AS row_count FROM users WHERE userid=:userid";
$statement = $DB_con->prepare($sql);
$statement->bindParam(':userid', $userid, PDO::PARAM_INT);
$statement->execute();
$result = $statement->fetch();
$rows_returned = $result['row_count'];

Remember to do some error checking though, but of course if you use my suggestion of retrieving based on the previous result set, this counting of rows would be unnecessary.

[genista]

Thanks for your reply. I see that ordering by date is important, but with infinite scroll aren't we trying to get a limited set of results to display and then when the user scrolls we pull the next set? These limited sets could be ordered by date so each set is showing the latest data. Your second point about creating a second query, if the table is stacked with data will this add a lot of latency?

I agree with you that efficiency is the way forward so I am trying to understand what your suggestion of ordering by date means.

Thanks,

G

[laserlight]

genista wrote:

I see that ordering by date is important, but with infinite scroll aren't we trying to get a limited set of results to display and then when the user scrolls we pull the next set?

Yes. Hence, if you do use LIMIT, it should only be to limit the number of result rows retrieved for the current set, not skip over rows by an offset. The skipping should be done by the where clause instead, i.e., you select where the date is older than the oldest row in the previous result set, hence skipping all results already retrieved efficiently if the date is indexed.

genista wrote:

These limited sets could be ordered by date so each set is showing the latest data.

Yes, but of course even if you were using LIMIT with an offset you would order by date/time (in reverse).

genista wrote:

Your second point about creating a second query, if the table is stacked with data will this add a lot of latency?

It depends, but if you do not need the query, then even if it is fast, it remains unnecessary.