Result Set paging

Hi, I'm pretty new to PHP and was wondering if there was any way to do result set paging without using MySql. I'm using MS Access 2000 for my database. Any help with this would appreciated. Thanks.

If you want something quick and easy I would suggest you use ASP. That of course assumes you have some basic knowledge of vbscript of javascript, and that you aren't adverse to learning some.
To connect up to the database in asp, use the following code.

<%
sqlQuery = "SELECT field FROM table WHERE condition"

set Connection = Server.CreateObject("ADODB.connection")
Connection.Provider = "Microsoft.Jet.OLEDB.4.0"
Connection.Open "C:\inetpub\wwwroot\accessDB\db.mdb"

set Recordset = Server.CreateObject("ADODB.recordset")
Recordset.CursorType = adOpenKeyset
Recordset.LockType = adLockOptimistic
Recordset.Open sqlQuery, Connection
%>

line 2 is your query, set that to whatever you want. line 6 contains the absolute windows shell path to your database. line 8 creates a recordset, lines 9 and 10 make it so that you can make changes to the recordset and save them, and so you can browse the recordset in any direction. Line 11 opens the recordset.

The rest is just displaying your output, which you should be able to figure out. If you want to learn vbscript (which is what i used above) i suggest going to www.w3schools.com, they have good tutorials. y ou can also read up on ADO while you're there.

Thanks for your reply...

I'm pretty good with ASP, now I'm trying to become good at PHP! So my best bet would be MySql for paging or is it possible with Access? Thanks again for your reply.

You can connect to asp using php, you need the PEAR extension for your PHP. There's info on that at php.net. You'll use the DB class, typically people just use DB::function() or DB::Variable rather than creating an instance of the class, but it can be done either way. as far as paging the recordset, you have to do only a few things, but it can be confusing for a php newbie sometimes.

You will first want a function to set the LIMIT in your query based on the page number, usually passed as $_GET[page] , which is the same as request.querystring("page") in asp. Once you have that, you will need to count the number of results from your query, and divide that by how many records you want on the page, then you'll know how many pages you have.

You also need to process the page variable to make sure you're not getting malicious input, so do an if (!isnumeric($_GET[page])) { what to do if the page is invalid } to control this.

You will do something like this to handle paging links:


it may look confusing and it may not work perfectly right off hand, you do have to add your own database connection code, and put in the query code yourself, as I don't remember all the pear db stuff to do it right now.

Oh yeah, this will definitely work for me. Thanks a million!

no problem, hope it doesn't require too much modification. i had to type it up offhand because i couldn't find any of my files that used it

mysql -> MySQL
pgsql -> PostgreSQL
ibase -> InterBase
msql -> Mini SQL
mssql -> Microsoft SQL Server
oci8 -> Oracle 7/8/8i
odbc -> ODBC (Open Database Connectivity)
sybase -> SyBase
ifx -> Informix
fbsql -> FrontBase

those are the only databases supported in PEAR. (pear.php.net). i guess because access dbs require a driver. try using sql ?

uck i'm dumb, use ODBC for access

crazytrain81, just a quick note.

$sql = 'SELECT * FROM table WHERE 1 LIMIT '. $lowerBound.' , '. $upperBound.';';

should be

$sql = "SELECT * FROM table WHERE 1 LIMIT $lowerBound, $upperBound";

when you use double quotes, you dont have to escape for variables

$linkOutput .= "<a href='{$_SERVER[PHP_SELF]}?page={$_GET[page]-1}'>BACK</a>";

just makes it easier to code.

ben, i have a question. have you (or anyone you know of) tested the difference in load time when using " v using concatenations? I try to use " as little as possible to avoid having php parse a lot of data that doesn't need to be parsed, primarily because the apps I make in php are usually for large scale use. I try to just stick to ' across the board so I don't parse a lot of text for no reason; and I don't use " sometimes and ' others just for readability and all.

Any ideas?

also, this is how you could use your access db, using ado

I forgot all about that! Thanks for your help...I should be a pro in no time!

The age old debate about single or double quotes. We ran a test a while back printing a table in php. Using a while loop we printed the table 300 times, a production run with single quotes and once with a production run with double quotes. On average, the HTML table echoed using the single quotes out benched the one with double quotes almost 3 to 1.

Now, the difference is minimal in a sense as we are talking about tenth's of a second in our test's, but when you create, lets say a statistical application that uses alot of loops (looping the log file) and formatting and printing HTML tables, using single quotes around your HTML output is indeed gonna prove substantial.

One more benefit of using single quotes as you can see above that when using your IDE, the syntax highlighting stands out and thus makes your code muck more legible.....

---------------------------

Of course my post has no bearing on what this thread was actually about as you are talking about using single/double quotes in a query...... Just wanted to add my own personal experience with the single/double quote debate.......

haha, i had a feeling it was a pretty significant benefit to use the quoes the way i have been when an app scales up a good bit. thanks for the info