You have chosen to sponsor your bid up to a maximum amount of .
The goal of this project is to build a php/mysql page(s) that provides users feedback of how their filter changes affect the Qty of records selected on a report builder web app. This will be used as a proof of concept demonstrating an efficient method so you may build your own test data so long as the qty of records and primary key data type match what is described below. I do not care about the styling of the page and can be very basic with only enough fields to demonstrate the filtering process.
I currently have a PHP/mysql web application that lets users select large lists of street addresses based on various filters they create. It is a 2 page process in which they first enter criteria about which addresses they want by location (ie county, zip code, city, etc) . When they click to the next page, it shows them the current count of how many addresses they have selected, and also a count of how many distinct zip codes are in their selection. Each address is identified by a unique address_id that is a 4byte INT. This first selection typically ranges from 10,000 to 200,000 records but potentially as high as 10,000,000 records.
On the next page, they can choose to keep or exclude addresses from their selection that also appear on any of their previous saved lists. For instance, they might have a list called "Bad Addresses" that they want any overlap excluded from their current selection, and they might have another saved list called "High Margins" and they only want to keep addresses from the current selection that exist on the "High Margins" list. These saved lists are typically 1,000 to 20,000 addresses but can get up towards 2,000,000 addresses.
I would like to determine the most efficient way to incrementally narrow down the address_ids to keep. I currently build the SQL statement incrementally and end up running the SQL statement multiple times as each page builds onto it, but I'm thinking there is likely a better way to just store the resultant address_ids of the first query, and then filter that list down based on the address_ids in the selected lists. However, I'm not sure if that should be done using Temporary Tables in mysql or some kind of stored session variable in PHP.
Please state what experience you have building a similar solution and which method you think would be better and why.