Database Performance

programming
1

I have a question about pulling 1000’s of records from a database and efficiently displaying them.

My record set has 5000 records and I want to display it using HTML.  It would not be right to make a table with 5000 rows becuase it would take a long time and scrolling would be out of control.  So I want to only write the first 50 to the screen and have previous and next buttons to bring up the next or previous set of 50 records.  I know this is done all over the net(this site does it with these forums).  How do I do this?  Are there different options?

Thanks,

2

if you want 50 recods per page, your should be something like this:

$pageNumber = $_GET['p']-1; // pass the "page number" is the variable "p" to indicate current page
$startAt = $pageNumber*50 // calculate record# to start at, based on page number

$myQuery  = "SELECT *";
$myQuery .= "FROM myTable"; 
$myQuery .= "WHERE ID>'$startAt'";
$myQuery .= "LIMIT 50"; //select only 50 records

hope that helps :slight_smile:

3

Thanks ahmed, that does help. But there is still more.

OK, lets say I want to pull all records with a specific UserID and the UserID is not the primary key(i.e. there are a bunch of records for this user). Your method won’t work. Get what I’m saying?

Any solutions? Thanks

4

hm… i’ll have to look into that, never done anything like that :slight_smile:

5

I am using PHP and mySQL…

Could’nt you create a temp table with just the records for the user then use your method?

I don’t know temp tables…

6

Wouldn’t you just go?:

SELECT *
FROM Tables
WHERE UserID = ‘users_id’

7

cause that would return 1000’s of records when he only needs 50 records

8

Ah ok, I’m not totally following… doesn’t your code before do that?

9

no…

$myQuery .= “LIMIT 50”;

10

top