Echo row 30 with php from sql result?

Question

I want to get a result from sql with unlimited rows, I don't want to use limit in my sql statement because I need to know the total number of rows. But I just want to echo, for example row 30-60.

I use php.

Answer 1

First, run this statement:

SELECT  SQL_CALC_FOUND_ROWS
        *
FROM    mytable
ORDER BY
        mycol
LIMIT 30, 30

, fetch the rows and save them into an array.

Right after that, run this statement:

SELECT  FOUND_ROWS()

, which will show you total count of rows that would be returned by the previous statement be there no LIMIT clause.

Answer 2

$query=mysql_query("SELECT....");
$array=array();
while($row=mysql_fetch_row($query)){
$array[]=$row;
}

Now you have the $array array that contains all the rows. If you want to print only row between 30 and 60 use this:

for($i=30;$i<60;$i++) echo $array[$i][0];

Answer 3

I believe Quassnoi means this:

SELECT SQL_CALC_FOUND_ROWS * FROM  accounts ORDER BY accountID LIMIT 30

and then run this select statement to find the number of rows:

select found_rows();

You're not supposed to do any queries using the * of course because it impairs performance. Use the names of the columns you need. I think this would be the best way because it's the most efficient. There's no need to return your entire table if you only need a count and 30 rows.

Answer 4

http://php.net/manual/en/function.mysql-data-seek.php

mysql_data_seek() moves the internal row pointer of the MySQL result associated with the specified result identifier to point to the specified row number. The next call to a MySQL fetch function, such as mysql_fetch_assoc(), would return that row.

mysql_data_seek($res, 30);
for ($i = 0; $i < 30; ++$i) {
  $row = mysql_fetch_assoc($res);
  var_dump($row);      
}