CODEWITHSUNDEEP
mysql
user2881809
user2881809

Reputation:

Mysql Really get rows from result?

Good day.

For page navigation useally need use two query: 1) $res = mysql_query("SELECT * FROM Table");

-- query which get all count rows for make links on previous and next pages, example <- 2 3 4 5 6 ->)

2) $res = mysql_query("SELECT * FROM Table LIMIT 20, $num"); // where $num - count rows for page

Tell me please really use only one query to database for make links on previous and next pages ( <- 2 3 4 5 6 -> ) and output rows from page (sql with limit) ?

p.s.: i know that can use two query and SELECT * FROM Table LIMIT 20 - it not answer.

Upvotes: 0

Views: 75

Answers (2)

Dima Tisnek
Dima Tisnek

Reputation: 11781

Many database APIs don't actually grab all the rows of the result set until you access them.

For example, using Python's built-in sqlite:

q = cursor.execute("SELECT * FROM somehwere")

row1 = q.fetchone()
row2 = q.fetchone()

Of course the library is free to prefetch unknown number of rows to improve performance.

Upvotes: 0

John Conde
John Conde

Reputation: 219804

If you want to know how many rows would have been returned from a query while still using LIMIT you can use SQL_CALC_FOUND_ROWS and FOUND_ROWS():

A SELECT statement may include a LIMIT clause to restrict the number of rows the server returns to the client. In some cases, it is desirable to know how many rows the statement would have returned without the LIMIT, but without running the statement again. To obtain this row count, include a SQL_CALC_FOUND_ROWS option in the SELECT statement, and then invoke FOUND_ROWS() afterward:

$res = mysql_query("SELECT SQL_CALC_FOUND_ROWS, * FROM Table");
$count_result = mysql_query("SELECT FOUND_ROWS() AS found_rows");
$rows = mysql_fetch_assoc($rows);
$total_rows = $rows['found_rows'];

This is still two queries (which is inevitable) but is lighter on the DB as it doesn't actually have to run your main query twice.

Upvotes: 4

Related Questions