SQL Server and LIMIT clauses on SELECT statements

Hi
I've been reading this thread an have found it interesting. As far as I can see you are all trying to manage paging in ms jet via php. Using sql seems not to do it, so one will have to find another way around the problem.

I have been using ms jet for access 2000 toghether with php for about half a year and I too have felt the lack of the limit clause in this version of sql.

My method is not escpecially sophisticated. I use two queries, one to count the number of records that will be of interest, and another to select the fields of interest in these records.

Then I pick out the fields from the select query and put them into named arrays, using a flow sentence (for..., while can be used as well), whereby field1[0] and field2[0] are from the same row in the query (these names can be changed to other, more descriptive ones, of cource, like first_name[0] and last_name[0]..) . This will result in a series of arrays containing the selected data for the rows in the query, where the index of the array holds fields from corresponding rows.

Now, let's say I want to have a page size of 10 records. The first page then will contain records 0..9, the second records 10..19 and so on. My first query will have provided me with the number of records. I can now compute the number of pages with a

$total_pages=round($number_of_records/$page_size);

and I will also be able to compute the desired array indexes for a given page, by finding the first array index for this page, using:

$page_nr (page number)

$first_index_on_page=$page_nr*$page_size;
$last_index_on_page=$first_index_on_page+$page_size-1;

and loop through these indexes using a for sentence:

for ($index=$first_index_on_page;$last_index_on_page;$index++)

and then display the data and page number using a table row syntax in html.

Using << and >> and page numbers, I can then make the script pick up the correct data in the same script.

This is my workarround for the lacking limit clause in ms jet, but it's not my recipee, I picked it up somewhere on the net earlier this year.

Hope thsi can help someone. If this is not clear enough, let me know.