Member Avatar for jonsan32

I'd like to create something like this: http://www.million-monkeys.com/tvcyo/

I'd love to use Google docs, but I wouldn't mind plugging it all in by hand. I've done so before when I used this site: to create a searchable database.

I'm just wondering, however, how would I go about doing that with filter settings like the first site I mentioned. Any help would be great. Thanks!

  • 2 Contributors
  • 3 Replies
  • 239 Views
  • 21 Hours Discussion Span
  • Latest Post Latest Post by AleMonteiro

Dani AI

Generated

Short, practical pattern that builds on 's suggestion and the DataTables idea mentioned by .

Keep two server endpoints: one that returns the lists used to populate the filter controls (distinct values or precomputed lists) and one that returns paged, filtered rows. On the client, initialize a DataTable (or equivalent) in server-side mode and include current filter values in every AJAX request. This avoids shipping all rows to the browser and keeps UI snappy once the dataset grows.

Example client pattern:

var table = $('#results').DataTable({
  serverSide: true,
  ajax: {
    url: '/api/rows',
    data: function(d) {
      d.court = $('#court').val() || null;
      d.league = $('#league').val() || null;
      d.q = $('#textSearch').val() || '';
    }
  },
  columns: [ /* column defs */ ]
});

$('.filter').on('change input', function(){ table.ajax.reload(); });

Server-side notes: build the WHERE clause only for provided filters, use parameterized queries/prepared statements to prevent SQL injection, and return structured JSON with total/filtered counts and the current page of rows (DataTables expects draw, recordsTotal, recordsFiltered, data). Index columns that are frequently filtered or ordered, and use EXPLAIN/Query Plan to find bottlenecks. For free-form text search, consider DB full-text indexes or a lightweight search engine if queries are slow.

Google Sheets is fine for prototypes or under-a-few-hundred rows: publish a CSV/JSON feed or use the Sheets API and cache results on the server. For production, import the sheet into a proper DB and use the server pattern above. Usability tips: include an "All" option, debounce text input, show a loading state, and surface counts in filters when possible. If results are incorrect, check parameter names, JSON encoding, and server-side pagination math first.

Recommended Answers

All 3 Replies

Member Avatar for AleMonteiro

I suppose you are using a DataBase to store the records, right?
So, if you table is normalized (one table for each type of record) you can make a select from each table that you want to use as a filter to populate the dropdowns. Then, if a dropdown is selected, you use that value in a WHERE clause.

If you table is not normalized (I.E. the Court name is wrote in the Game table, instead of having a Court table) you need to make a select distinct for each column that you want touse as a filter. Then the WHERE clause is the same as the first case.

Member Avatar for jonsan32

Wow, sounds easy enough. Thank you.

Member Avatar for AleMonteiro

You're welcome.

Just so you know, you could do it all in the UI with JS, but I particularly don't like this because you'd be retrieving all the records from the DataBase, transfering all to the browser and then processing all the data in the UI. If it was few records, there is no problem, but if you're working with lots of records the performance would be very poor.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.