[RESOLVED] Displaying thousands and thousands of records via HTML/PHP

[ppowell]

I have currently devised a way to display up to 3,000 records via PHP/HTML by using pagination techniques that stuff an entire DB query resultset into a $_SESSION object to use within pagination.

Here's my question: How good will this work when we start dealing with more than 3,000 records? Like 5,000? 10,000? 10,000+? The $_SESSION object will obviously become too large to be manageable.

What do you guys advise I do to manage this in the foreseeable future? No rush on this of course for now.

Thanx
Phil

[justsomeone]

"How good will this work..." Not very bad at all. Sounds like a very bad idea to me. Unless you have lots of hardware and very few users. Otherwise you'll grind to a slow and painful halt.

My suggestion would be that you should select from the database for each page display. You can use a single session variable to store the current position in the recordset. If you use mysql you can use the "LIMIT" clause in the SELECT statement to only return a preset number of rows from the database, starting from the currrent position. So, if you are only interested in displaying 20 records per page, your query only returns 20 rows. The "current position" variable will dictate whether that's 20 rows starting row 0, row, 40, row 60... row 5,840...

[sneakyimp]

page them by creating a script that fetches only 20 or 100 records at a time from the source.

where do the records come from? a file or a database?

if you a re fetching the records from a file, you can have your script open the file and read lines until it reaches line #x and then read n records.

if you get the records from a query, you can use this kind of sql statement:
"SELECT * FROM DATABASE_TABLE LIMIT 0, 30"

[ppowell]

Using LIMIT will not help because a customer demand is that they be able to:

1) Sort all records by multiple fields
2) Search records according to fulltext search
3) Be able to view Page 127, 40 or 3 entirely on the fly, which LIMIT will prevent from being shown

How could I use LIMIT around these.. "limitations"?

Phil

[sneakyimp]

It is possible and is done all the time on hotmail, gmail, google, etc. the trick is to put all the search parameters into your query string (i.e., the $_GET vars) and formulate an sql query that returns only the records that correspond to the current critieria.

justsomeone is right that if you are storing several thousand records in session then you will be chewing up your server's memory and resources really fast once you have even a moderate number of users.

consider this, it might help you get started.

suppose your dbase had some fields:
id - integer
name - varchar
address - varchar
.
.
.

$sort_field_name = "name"; // this could be any field name you like
$search_text = "joe";
$page_number = 35;
$records_per_page = 10;

$record_offset = $page_number * $records_per_page;

$sql = "SELECT * FROM database_table WHERE name LIKE '%$search_test%' ORDER BY $sort_field_name LIMIT $record_offset, $records_per_page";
$result = mysql_query($sql)
  or die("sql failed:" . sql);
while($row = mysql_fetch_assoc($result)) {
  print_r($row);
}

[ppowell]

That solves only 2/3. You didn't solve the issue involving the requirement where the customer must be able to view page 137 or page 30 or page 40 or page whatever.. this being accomplished by links to these pages at the bottom of the paginated resultset.

Phil

[james_h]

Do as sneakyimp suggested above, but get the $page_number parameter from $_REQUEST

[ppowell]

I'm sorry I don't see how. I can get the page number parameter from $_REQUEST because you already do, but how will the customer see the links at the bottom of the page that display pages 1 - 200 or 1 - 500 or however many?

They require that they see all of the links below with all pages

Phil

[sneakyimp]

You're gonna have to have faith ppowel and meet us half way. Try to write some code and you'll see it'll work. There's a lot of code to be written which looks at your $_GET string and puts all the right values into the query. I've done this many times.

If you want to offer the total page count and let user jump directly to the page of interest, you'll need to run a 2nd query which is like your first but without the limit part. This query returns the total number of records that match your search criteria. something like:

// get this value from the $_GET values--MAKE SURE IT MATCHES ONE OF YOUR PERMITTED FIELD NAMES EXACTLY or some hacker might exploit your db.
$sort_field_name = "name"; // this could be any field name you like
// this also comes from $_GET...you might want to screen it too.
$search_text = "joe";
// this comes directly from $_GET...make sure it's a numeric value.
$page_number = 35;
// you can take this from $_GET if you want or you can set some number you like
$records_per_page = 10;

// query to get number of records matching the search criteria
$sql = "SELECT COUNT(*) FROM database_table WHERE name LIKE '%$search_test%'";
$result = mysql_query($sql)
  or die("count query failed.");
$crow = mysql_fetch_row($result);
$total_records = $crow[0];
$total_pages = ceil($total_records/$records_per_page);

for($i=0; $i<$total_pages; $i++) {
  $page_link_html .= (empty($page_link_html) ? ''  : ' | ');
  $page_link_html .="<a href=\"" . $_SERVER['PHP_SELF'] . "?page_number=" . ($i+1) . "\">$i</a>";
}

$record_offset = $page_number * $records_per_page;

//main query
$sql = "SELECT * FROM database_table WHERE name LIKE '%$search_test%' ORDER BY $sort_field_name LIMIT $record_offset, $records_per_page";
$result = mysql_query($sql)
  or die("sql failed:" . sql);
while($row = mysql_fetch_assoc($result)) {
  print_r($row);
}

I haven't checked this code to see if it works, but you should get the idea.

[Weedpacket]

So get a count of the total results so that you know how many results there are; but only select a limited number of results to display.

[ppowell]

Ok it took a bit of effort and guesswork but I was able to upgrade my existing Pagination class methodology to allow for the optional limitation of the query by adding an additional field

SELECT id, first_name, last_name, (SELECT count(id) FROM people) AS total FROM people GROUP BY id, first_name, last_name, total ORDER BY upper(last_name) ASC, upper(first_name) ASC LIMIT 0, 10

What I have to do is to look for the instance in the resultset for $result[0]->pagination_total and if found, bypass the $_SESSION handling altogether.

Thanx all!

Phil