Dividing MySQL output into several pages

[toncu]

I have been using PHP-MySQL only for one week. I'm usin them to write database search pages.

The databases that I am dealing with are very large (smallest of them are 30,000 records and largest is the 600,000 records).

Since sometimes the resulting set from a query is very large, I want to implement a scheme to divide this set into pages and put regular page numbers at thye bottom of each page.

Please help me to solve in this problem.

Thank you

Talat Oncu

[toncu]

Sorry, I know there are hundreds of questions similar to mine, but since my tables are very big, I can't use LIMIT option of SQL (each query executes approximately 30 seconds on a Pentium 4 2.0 machine). Hence I need other ways to solve my problem.

Talat

[jmcneese]

http://www.phpbuilder.com/board/showthread.php?s=&threadid=10206378

[toncu]

Thank you " jmcneese" for your reply. It seems that your code will work (by the way, what is that "results" parameter after the "action" word?).

But, if I am not wrong, everytime you enter in the code, you are searching the database from scratch. This is not what I really want, because, searching will take a long time. I am looking for a solution where I search the database only once, and use that $result from the mysql_query in the subsequent calls! Since I am very new in using PHP I really don't know whether this is possible or not.

Probably I have to use a "persistent connection" but, if the user response time is very long, then the OS may cancel the connection due to time-out (is this correct?)!

I don't know whether implementing this problem by using "session" facility is OK. I have never used this facility before, but I think to use $response parameter from the MySQL call as a global variable and pass this variable to subsequent calls.

$result = msql_query("SELECT * FROM table WHERE id=1", $link);

if $response along with the $link variables may be defined as session_global and the database is connected in a persistent manner, then the problem will be solved.

Hence, I really want to know, whether the above-mentioned way works or not!

[leatherback]

Hi,

I am not sure what the result would be of the well-known options (Most databases are pretty fast in querying, but the more complex your queries, the more time they take) Maybe somebody else has an answer to that?

BUT

I have no clue as to what sort of data and stuff you are working with. BUT

what would be an option perhaps: Use sessions and create tables.

Once a query has run, you output to a new table on your server. The table would be named after the session, which would mean a new table for each user, or you could include a session row in the table, then you could use one table for all users. The choice is ytours, and depends a bit on number of users, and the differences you'd expect in output-types.

That way you can build your output pages/index according to the "well-known"method for building an indexlisting, and you do not have to query everytime.

Once the session expires you delete the temorary data. You could run into problems with storage capacity on your server if you have many simultanious users, with large output datasets.

Hop ethis is usefull?

[jweissig]

hi,

this is funny i have been working on something to solve the problem for a past couple days. I have a database with a little over 500,000 records which have every day around 12am.

You need to create an index on your primary key! this will cut that query time down by a lot!! also if you are doing any WHERE filter you should also make an index for that field!

what i was programming to solve my problem is somethink that will read a temporary table with data about my large table. I am useing this because my data should not change very often (only updated at 12am!.) so i can tell the script that there are going to be x number of returned rows and you should do what you can.

for example say i know that with this filter there are going to be 50,000 results. if i wanted to page thjrough then i could show 50 records a page and then show 1,000 pages. I would not have to search the entire table to see how many records there are to get this number all i have to do is check the temporary table and i know. this saves a lot of time.

• Justin

p.s. i was running a select * from table; is about 2.5 seconds for a database of 500,000 records on a pII 400 using index's. The database size is about 100MBs.

[jmcneese]

glad i can help.

the $result you are seeing is a data object returned by the PEAR:😃B class i use for my application.

$result = $db->getAll($sql);

this gets all the results from the query contained in $sql. for more information, look here.

as far as the action you see, this is the way i preserve my search query between pages. i utilize a custom-coded session handler, which stores my search criteria in a $_SESSION['query'][$criteria] format. now, you might not have to do this if you are using MySQL, due to the availability of the LIMIT command. you could do something like:

$sql = "SELECT foo
	    FROM bar
	    WHERE $condition 
	    ORDER BY $orderby ASC 
	    LIMIT $offset, $limit";

then all you need do is make a few modifications to the code to build an appropriate URI that includes the $limit and $offset values.

[greg252]

Wouldn't a temp table containing the result set be the best way?

[jmcneese]

if your DB supports derivative tables, yes, it would probably make things faster, depending on the size of the resultset.