Designing a dynamic product filtering system for an online store

Ecom
1

Hey everyone, I’m working on a cool project for my online shop. I want to make a smart filter that changes based on what’s available. Here’s what I mean:

Let’s say I’ve got two TVs:

  • TV1: 46-inch Samsung
  • TV2: 80-inch Sharp

I want the filter to show these options:

  • Brand: Samsung, Sharp
  • Size: 46 inch, 80 inch

But here’s the tricky part. If someone picks Samsung, the size filter should only show 46 inch. If they pick both brands, it should show both sizes.

I’m using PHP and MySQL. My database has three tables:

products (id_product, name)
filters (id_filter, name, id_category)
filters_values (id, id_product, id_filter, value)

I thought about running a query for each filter combo, but that’s too many queries. Any ideas on how to make this work without overloading the database?

Thanks for your help!

2

Hey Daisy! That’s a super cool project you’re working on! :shopping::sparkles:

Have you thought about using AJAX? It could be a game-changer for your filtering system! Here’s a quick idea:

  1. Load basic filter options when the page loads
  2. When a user picks a filter, send an AJAX request to the server
  3. The server runs ONE query to get the new filter options
  4. Send the new options back and update the page with JavaScript

This way, you’re not hitting the database for every little change. Plus, it’ll feel super smooth for your customers!

Oh, and a quick tip - have you looked into database indexing? It could really speed things up if you haven’t already.

What do you think? Does this sound like something that might work for your shop? I’d love to hear more about how it goes!

3

hey daisy, super cool projct! try mixin php + javascript. php loads filters initially and js updates stuff on click so db’s not overloaded. caching also helps speed things up. good luck w/ shop!

4

Have you considered using a combination of server-side and client-side filtering? Here’s an approach that might work:

  1. Load all product data into JavaScript on page load.
  2. Use PHP to generate initial filter options.
  3. Implement client-side filtering with JavaScript to update options dynamically.

This method reduces database queries and improves responsiveness. You could also implement lazy loading for product data to handle larger datasets efficiently.

For optimization, consider indexing your filters_values table on id_product and id_filter columns. This should significantly speed up your queries.

Remember to test thoroughly with a variety of product combinations to ensure smooth operation under different scenarios.