mssql and php

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Guy
Forum Commoner
Posts: 53
Joined: Sun Jan 12, 2003 3:34 am

mssql and php

Post by Guy »

when getting a recordset using
$rs = MSSQL_QUERY($sSql);
is it possible to use the PageSize ,RecordCount , AbsolutePage
properties ? when i tried to get them or set them
straight forward like $rs->PageSize = 20;
i got nothing
Thanks
Guy
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post by Stoker »

mssql_query does not return an object, it returns a reosurce identifier (int).

I am not familiar with Bill Gates SQL and unaware of how paging functions work there, I don't see any PHP mssql_ functions for that, you can get recordcount with mssql_num_rows()

http://www.php.net/manual/en/ref.mssql.php
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post by fractalvibes »

What you are asking for are actually ADO-specific properties of the recordset object.

It looks like what you can do via PHP is:
rs.RecordCount (ASP/ADO) now becomes

myrecordcount = mssql_num_rows(result_id)

The paging - you may have to handle this yourself, though I could be totally wrong. I do think that SequelServer does support a variation of the Standard SQL Select Top N rows from myTable.......

So if you can store off that last ID you should be able to effectively display
the desired results, N rows at a time, and by saving the Min and Max ID values, scroll through the recordset, N rows at a time.

My SqlServer syntax may not be exact, as I use DB2 mostly, and it is
Fetch First N rows optimize for N rows...same concept though.

Hope this might be a small help.

Phil J.
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post by fractalvibes »

OK - straight from the manual - I was a complete Idiot for not realizing this in the first place, but never used/tried in MySql:

From the manual:
============
The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments. The arguments must be integer constants. If two arguments are given, the first specifies the offset of the first row to return, the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1): To be compatible with PostgreSQL MySQL also supports the syntax: LIMIT # OFFSET #.
mysql> SELECT * FROM table LIMIT 5,10; # Retrieve rows 6-15
To retrieve all rows from a certain offset upto the end of the result set, you can use -1 for the second parameter:
mysql> SELECT * FROM table LIMIT 95,-1; # Retrieve rows 96-last.
If one argument is given, it indicates the maximum number of rows to return:
mysql> SELECT * FROM table LIMIT 5; # Retrieve first 5 rows
In other words, LIMIT n is equivalent to LIMIT 0,n.

=============

So given that you can save off the ID of the last and first row retrieved, you should be able to approximate ADO paging, and also optimize your sequel to fetch only N rows each time. Don't know how/if connection/recordset pooling/caching is done with PHP, etc.

Sorry so late in responding with a better answer!

Phil J.
Post Reply