Paging 10 records per page

[itechC]

Hi all,

I'm displaying data from a database, something like a yellow pages. I need to display 10 records per page. How do go about displaying 10 records per page if my search returns 100 records. Thanks

 

[dkdude]

Hi,

Something linke this should do the trick:

"SELECT * FROM field ORDER BY id DESC LIMIT 0,20" ....

Where 0 is the ofset record (in this case the very first record in the DB)

... and ...

20 is the number of records to be returned by the DB (in this case 20 records).

Good Luck


Jakob

 

[DRJ478]

Use the LIMIT clause in your SQL statament:

SELECT * FROM table LIMIT $offset, $numOfRecords

Keep track of the offset via session.

 

[bastienk]

You could look at using stored procedures where you pass the offset and limit to the sp to get the appropriate data

google sql server recordset paging for more links

here is some code from winnetmag

Listing 4: SELECT_WITH_PAGING Stored Procedure
CREATE PROCEDURE SELECT_WITH_PAGING (
   @strFields varchar(4000),
   @strPK varchar(100),
   @strTables varchar(4000),
   @intPageNo int = 1,
   @intPageSize int = NULL,
   @blnGetRecordCount bit = 0,
   @strFilter varchar(8000) = NULL,
   @strSort varchar(8000) = NULL,
   @strGroup varchar(8000) = NULL)

/* Executes a SELECT statement that the parameters define,and returns a particular page of data (or all 
rows) efficiently. */
 AS
   DECLARE @blnBringAllRecords bit
   DECLARE @strPageNo varchar(50)
   DECLARE @strPageSize varchar(50)
   DECLARE @strSkippedRows varchar(50)
   DECLARE @strFilterCriteria varchar(8000)
   DECLARE @strSimpleFilter varchar(8000)
   DECLARE @strSortCriteria varchar(8000)
   DECLARE @strGroupCriteria varchar(8000)
   DECLARE @intRecordcount int
   DECLARE @intPagecount int

/* Normalize the paging criteria.
If no meaningful inputs are provided, we can avoid paging and execute a more efficient query, so we will 
set a flag that will help avoid paging (blnBringAllRecords). */
   IF @intPageNo < 1
      SET @intPageNo = 1
   SET @strPageNo = CONVERT(varchar(50), @intPageNo)
   IF @intPageSize IS NULL OR @intPageSize < 1         -- Bring all records, don't do paging.
      SET @blnBringAllRecords = 1
   ELSE
      BEGIN
         SET @blnBringAllRecords = 0
         SET @strPageSize = CONVERT(varchar(50), @intPageSize)
         SET @strPageNo =  CONVERT(varchar(50), @intPageNo)
         SET @strSkippedRows = CONVERT(varchar(50), @intPageSize * (@intPageNo - 1))
      END

/* Normalize the filter and sorting criteria.
If the criteria are empty, we will avoid filtering and sorting, respectively, by executing more efficient 
queries. */
   IF @strFilter IS NOT NULL AND @strFilter != ''
      BEGIN
         SET @strFilterCriteria = ' WHERE ' + @strFilter + ' '
         SET @strSimpleFilter = ' AND ' + @strFilter + ' '
      END
   ELSE
      BEGIN
         SET @strSimpleFilter = ''
         SET @strFilterCriteria = ''
      END
   IF @strSort IS NOT NULL AND @strSort != ''
      SET @strSortCriteria = ' ORDER BY ' + @strSort + ' '
   ELSE
      SET @strSortCriteria = ''
   IF @strGroup IS NOT NULL AND @strGroup != ''
      SET @strGroupCriteria = 'GROUP BY' + @strGroup + ' '
   ELSE
      SET @strGroupCriteria = ''

/* Now start doing the real work. */
IF @blnBringAllRecords = 1                   -- Ignore paging and run a simple SELECT.
BEGIN

   EXEC (
      'SELECT ' + @strFields + 'FROM' + @strTables + @strFilterCriteria +
         @strGroupCriteria + @strSortCriteria
)

END                                                          -- We had to bring all records.
ELSE                                                        -- Bring only a particular page.
BEGIN
   IF @intPageNo = 1                                -- In this case we can execute a more efficient
                                                                  -- query with no subqueries.
      EXEC (
         'SELECT TOP' + @strPageSize + ' ' + @strFields + 'FROM' + @strTables +
            @strFilterCriteria + @strGroupCriteria + @strSortCriteria
)
   ELSE                                                     -- Execute a structure of subqueries that brings the correct page.
      EXEC (
         'SELECT' + @strFields + 'FROM' + @strTables + 'WHERE' + @strPK + 'IN' + '
            (SELECT TOP' + @strPageSize + ' ' + @strPK + 'FROM' + @strTables +
               ' WHERE' + @strPK + 'NOT IN' + '
                  (SELECT TOP' + @strSkippedRows + ' ' + @strPK + 'FROM' + @strTables +
                      @strFilterCriteria + @strGroupCriteria + @strSortCriteria + ') ' +
               @strSimpleFilter +
               @strGroupCriteria +
               @strSortCriteria + ') ' +
            @strGroupCriteria +
            @strSortCriteria
)
END                                                         -- We had to bring a particular page.

/* If we need to return the recordcount: */
IF @blnGetRecordCount = 1
   IF @strGroupCriteria != ''
      EXEC (
         'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM' +
            @strTables + @strFilterCriteria + @strGroupCriteria + ') AS tbl (id)
)
   ELSE
      EXEC (
         'SELECT COUNT(*) AS RECORDCOUNT FROM' + @strTables + @strFilterCriteria +
            @strGroupCriteria
)
GO

Bastien

Cat, the other other white meat

 

[bastienk]

A little more research also uncovered the identity column type...a sequential integer datatype that you coud use like a limit...this column could be added dynamically during execution and by using the id column in the where clause you should be able to implement some paging simply....

$sql = "select top 50  from table where identity_column > $offset

where the $offset is a calculation of the page number * 50...


Bastien

Cat, the other other white meat

 

[sleipnir214]

bastienk:
Why are you posing non-PHP scripts in the PHP forum?



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[bastienk]

sleipnir214

There are two solutions for paging with SQL Server

1. Use a stored procedure as indicated in the first post

2. Use the identity column as shown in the second post.

While most PHPers use MySQL which currently (in production modes) support SPs and therefore [perhaps] don't know the usefulness of those SPs. Those that use SQL Server probably know and use them in their code...even though its not a pure PHP solution.

Which is why I included the second solution. I felt it important to offer both views, thats all.


Bastien

Cat, the other other white meat