nV News Forums

 
 

nV News Forums (http://www.nvnews.net/vbulletin/index.php)
-   Software Development (http://www.nvnews.net/vbulletin/forumdisplay.php?f=53)
-   -   MySQL SORT by Column and LIMIT Rows using classic ASP (http://www.nvnews.net/vbulletin/showthread.php?t=106014)

technoid 01-10-08 07:23 PM

MySQL SORT by Column and LIMIT Rows using classic ASP
 
I started to add paging code to my project at work and I can't seem to get sorting and paging to work at the same time. I'll put the querries below for them separately but how do I start at a row offset and sort too.

Example: 50 Rows in table
I want to start at row 0, 10, 20, 30, 40 and display 10 rows but also sort them by any column. Here's the two querries I used before I tried to add paging and just tried to add display row offsets. That works but I can't seem to get the right combination to get paging and sorting together. I'm using classic ASP to write the code.

Quote:

QUERRY TO LIMIT ROWS
Set oRS = oConn.Execute("SELECT * FROM `tracking` LIMIT " & startRecord & "," & endRecord)

Quote:

QUERRY TO SORT BY ONE OF THE COLUMNS IN THE TABLE
Set oRS = oConn.Execute("SELECT * FROM `tracking` ORDER BY `model`")
Thanks for any help.

Sycario 01-12-08 02:10 AM

Re: MySQL SORT by Column and LIMIT Rows using classic ASP
 
have you tried:
Set oRS = oConn.Execute("SELECT * FROM `tracking` ORDER BY `model` LIMIT " & startRecord & "," & endRecord)

technoid 01-12-08 08:32 PM

Re: MySQL SORT by Column and LIMIT Rows using classic ASP
 
Quote:

Originally Posted by CaNiBuS
have you tried:
Set oRS = oConn.Execute("SELECT * FROM `tracking` ORDER BY `model` LIMIT " & startRecord & "," & endRecord)

Sorry, I tried that but didn't think to put it in the question. It gave me the correct number of rows but sorted the whole table not just the rows the limit set. I did find a work around since our usage is small by using some ASP code to semi sort it by storing all the row IDs in an array and then going through the whole table and only outputing the correct rows. That gave me a sorted limited page but it wasn't somthing I'd do for anything large.

It turns out the Boss didn't like the limited page sort so we got rid of it. I worked for over a day on it and then when he saw it running he decided he would rather have the whole table sorted like it did by default :) Oh well, at least they paid me for it

Thanks for your help.

t3hl33td4rg0n 01-19-08 09:52 AM

Re: MySQL SORT by Column and LIMIT Rows using classic ASP
 
Personally I always found it annoying when "search results" were limited without any orthodox means of increasing the limit.

Like, searching for an item on siteX returns 900 results with each page only showing 5 items! wtf! lol...

I always try to include an options for sorting, number of results per page, etc.

technoid 01-20-08 10:42 PM

Re: MySQL SORT by Column and LIMIT Rows using classic ASP
 
Quote:

Originally Posted by t3hl33td4rg0n
Personally I always found it annoying when "search results" were limited without any orthodox means of increasing the limit.

Like, searching for an item on siteX returns 900 results with each page only showing 5 items! wtf! lol...

I always try to include an options for sorting, number of results per page, etc.

I ended up providing lots of options for page size, 10, 15, 20, 25, 30, 40, 50, All. That way each person should be able to pick a page size that fills their screen. The page size is stored in a persistent cookie so they have the same number of lines per page when they return to the site. You can also sort on any column.


All times are GMT -5. The time now is 04:55 AM.

Powered by vBulletin® Version 3.7.1
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 1998 - 2014, nV News.