I've taken over a web application that someone else built. It's a pretty simple application that queries ALL records from a db and displays them in a list. They've also provided a way to 'filter' the data. When the user enters in data into a "filter by" text box, via Ajax the system re-queries the database to only select records that begin with that data. it's basically doing a
"select * from widgets where name like '" + input_from_user + "'"
Now we've gotten to the point where there are too many records being returned initially and I'm mullling over the follow design decisions.
If we want to introduce some sort of paging mechanism where the SQL statement itself is a)sorting by a certain field, b)limiting the results to the next 100 sorted records, do I still need a filter? I don't think so. It's either paging, or filtering in my mind, especially if I limit the amount of data we show at a time to 5o -100 records (something small that will fit on one screen).
Here's what I was thinking of doing (pseudocode)
SELECT * FROM MyTable WHERE SomeColumn > LastValue ORDER BY SomeColumn LIMIT 100
I wanted to throw it out there to see if anyone had any comments / suggestions / wanted to tell me that I'm way off base here.