limit the results of a query

I was wondering what happens when I use the multipage feature from tbs suppose I say 'Select * from x' and the result has 10000 records
but I set the $PageSize to 20 then I have 500 pages with records I do not need to retrieve yet as I do not use the data.

Now on MySQL: are only the 20 records which are displayed retrieved in some way, or are all records taken and only the 20 needed displayed, and does that leave me with a resultset of 9980 records too many??

Thanks :)

Hi RwD,

What happens beside TBS about ByPage Mode is described in the manual.

Unfortunatly TBS must send the SQL statement "as is" to MySQL.
TBS is not able to manage with the LIMIT clause because of several reasons : the LIMIT clause may be already used in the SQL statement, or the SQL statement can be a Strored Procedure.

TBS send the SQL statment "as is", but will gonna read only the first records upon to the asked page. Unfortunatly, it has to read the first records event before the asked page because some database system can goto next record only fetching it.

When the asked page is passed, TBS close the recordset.

Yes, I understand. But I thought you couldn't do the LIMIT statement simply because it is not in all SQL languages.

(Transact-SQL kinda gave me a weird look, then explained it never heard of LIMIT ;P)

But I understand that if someone only wants to display the first 100 records, in pages of 20 then a limit 100 would be in the query and you couldn't really change it in a proper way :S

I was only trying to see if you had some other 'smart' way of finding out.

As for myself I use the ByPage feature without my own logic for limits depending on how much I think the user will use the other pages. If it is likely a user will use them I load the entire result into an array, and have tbs Page the array which is a sessionvariable (So I only query onces and if the sessionVariable is gone the query is rerun on the db)
If I think the user will most likely not go beyond the first page I simply set the limit using the variables otherwise sent with the MergeBlock function. Results look the same, just a lower db load I hope

What do you think of this approach? Do you have an even more efficient or simply better way of doing this?

perhaps you must make a cutom function per db.

like the seek function in the DB object I am using:

DB systems without this functionality will simply have to use the next record thing untill they come to the needed record. This might speed tbs up even more for MySQL users (not that I am complaning :P)

>But I thought you couldn't do the LIMIT statement
>simply because it is not in all SQL languages.

Yes that's also true. But some could argue that something sepcial could be done for MySQL specifically.

Your solution with the session is a good one.
For myself, I don"t use session a lot, so when I have very lot of records to display by page, I don't use the TBS By-Page mode, I manage the LIMIT clause int the SQL statement.

About a seek function: I was thinking about a something of that kind.
It is true that most of database system enables PHP to go directly to a specific record because the recordset has been fully saved on the server. And some other database system doesn't.
The seek function will enables us to go directly to the wanted record,
but will the perf be very better ?  I have to test that.

Well, I don't know if the seek function will improve performance. That also depends on the resultset. But it should be saving resources if it is only little...

where and how do you use this function. I also would be interested in using this function because i´m dealing with a large DB

@skrol29: i know that not all dbs support limit. whats about the opportiunity using it via a flag?

I don't want to hijack this thread, but I worked on a little trick to find the efficient paging for large datasets too.

Look here http://tomhenry.us/tbs3/ the the "efficient paging" link 3rd from the bottom.

I use EzSql for my database layer -- but you'll be able to see what what to do at least.

BTW, EzSql also has a query caching feature that makes it automatic to cache a query for repeated use. See the "caching model (2) and (4). The good thing with ezsql is that the cache is for a specific query therefore it can be reused from different php pages too.