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.
Is the filter really working well? Meaning, if we leave everything as is and query all records, as the user is filtering the data, why do I need to requery the database? Should I just "somehow" use the data I already have and filter that? For example, couldn't I somehow use javascript / jquery to filter client side?
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.
Thanks.