Go Back   nV News Forums > Software Forums > Software Development

Newegg Daily Deals

Reply
 
Thread Tools
Old 01-10-08, 06:23 PM   #1
technoid
Registered User
 
Join Date: Jul 2004
Location: Denton, Texas
Posts: 505
Default 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.
__________________
Antec-SOLO | ASUS-P5Q-SE | C2D-E8600 | AC-Freezer-7-Pro | Seasonic-S12-550W | 3GB-KVR-PC2-6400 | GTX-560Ti | SB-XtremeGamer | Pioneer-BDR-205 | Samsung-S223B | Dell-U2412M
technoid is offline   Reply With Quote
Old 01-12-08, 01:10 AM   #2
Sycario
Registered User
 
Join Date: Nov 2006
Location: St. Louis, MO
Posts: 363
Send a message via AIM to Sycario
Default 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)
__________________

Comp Specs:
Intel Q9650 @ 3.95Ghz
Asus Rampage Extreme
2x2GB Mushkin DDR3-1600 @ 6-6-6-17
eVGA GTX 280 SSC
BFG ES-800 PSU
Sycario is offline   Reply With Quote
Old 01-12-08, 07:32 PM   #3
technoid
Registered User
 
Join Date: Jul 2004
Location: Denton, Texas
Posts: 505
Default 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.
__________________
Antec-SOLO | ASUS-P5Q-SE | C2D-E8600 | AC-Freezer-7-Pro | Seasonic-S12-550W | 3GB-KVR-PC2-6400 | GTX-560Ti | SB-XtremeGamer | Pioneer-BDR-205 | Samsung-S223B | Dell-U2412M
technoid is offline   Reply With Quote
Old 01-19-08, 08:52 AM   #4
t3hl33td4rg0n
Dethklok Returns!
 
t3hl33td4rg0n's Avatar
 
Join Date: Dec 2004
Location: Kent, OH
Posts: 2,142
Send a message via AIM to t3hl33td4rg0n Send a message via MSN to t3hl33td4rg0n Send a message via Yahoo to t3hl33td4rg0n
Default 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.
__________________


Intel QX9650 @ 3.83GHzXFX 780ieVGA GTX 560Ti8GB GSkill DDR800Creative Sound Blaster X-Fi Titanium PCI-ESeagate 1.5TB . 4x Samsung 2TB

Quote:
Originally Posted by MowTin View Post
....it's so clear how government makes things worse not better...
t3hl33td4rg0n is offline   Reply With Quote
Old 01-20-08, 09:42 PM   #5
technoid
Registered User
 
Join Date: Jul 2004
Location: Denton, Texas
Posts: 505
Default 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.
__________________
Antec-SOLO | ASUS-P5Q-SE | C2D-E8600 | AC-Freezer-7-Pro | Seasonic-S12-550W | 3GB-KVR-PC2-6400 | GTX-560Ti | SB-XtremeGamer | Pioneer-BDR-205 | Samsung-S223B | Dell-U2412M
technoid is offline   Reply With Quote
Reply


Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


All times are GMT -5. The time now is 07:12 AM.


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