Page 1 of 1

Compare MySQL Result Sets

Posted: Thu Feb 01, 2007 8:39 pm
by csingsaas
Example:

I have a form allowing customers to search for a used automobile. The form has the following fields:

Car Model (pull down menu)
Car Color (pull down menu)
Car Year (pull down menu)
Car Features (multiple select menu)
Max Price (text field)

The customer must search using at least one of the above criteria, all of the criteria may be used if desired. On the search results page, I want 15 records to show per page. If the search results yield less than 15 total records, I will supplement the remainder (15 - # of search results) with featured advertisers. The featured advertisements will not come up in the natural search results. So, if someone searched for a green car and received 10 qualified records, I would have 5 records showing from featured advertisers that were NOT green cars.

The qualified search result query might look like:

Code: Select all

SELECT car_owner, car_location, car_condition FROM CAR_ADS WHERE car_model = '002' LIMIT 15
If I found that this query returned less than 15 results I would perform the featured advertisement query to supplement:

Code: Select all

SELECT car_owner, car_location, car_condition FROM CAR_ADS WHERE car_model != '002' LIMIT <remainder>
This is simple enough, and I can be absolutely sure that the two queries do not contain a similar record. However, if I change the query around and search by car features, I am unable to be certain that the two queries do not contain a shared record.

Code: Select all

SELECT CAR_ADS.car_owner, CAR_ADS.car_location, CAR_ADS.car_condition FROM CAR_ADS, CAR_FEATURES WHERE (CAR_FEATURES.feat_id = '014' AND CAR_FEATURES.car_id = CAR_ADS.car_id) AND CAR_ADS.car_adtype = 'featured' LIMIT 15
Again, I want to perform the query to select ads that do not match the search result set.

Code: Select all

SELECT DISTINCT CAR_ADS.car_owner, CAR_ADS.car_location, CAR_ADS.car_condition FROM CAR_ADS, CAR_FEATURES WHERE (CAR_FEATURES.feat_id != '014' AND CAR_FEATURES.car_id = CAR_ADS.car_id) AND CAR_ADS.car_adtype = 'featured' LIMIT <remainder>
The above will however return the same records if multiple features are assigned to a car.

Car# 1:
Features = air conditioning, 4 wheel drive, heated seats

Car# 2:
Features = air conditioning

Car # 3
Features = heated seats, tinted windows, automatic door locks


If we searched for a car with Air Conditioning, the results would be Car #1 and Car $2. The featured ads that are displayed after the qualified search results would display Car #1 and Car #3. Car #1 is repeated! Can anyone help me out with how I can accomplish this task?


Is there a simple way to compare two result sets and weed out the duplicate records?

Posted: Thu Feb 01, 2007 9:03 pm
by superdezign
Well if what you have works aside from duplicates, just make an array to store the ids of entries that you've already displayed, and traverse the array to decide whether or not something has been displayed yet.

Posted: Thu Feb 01, 2007 9:18 pm
by csingsaas
The main reason I would avoid implementing your suggestion is that I want to make sure there are 15 results showing. If I come up with 10 qualified search results and then perform the second query to get the remaining 5, the second query may contain duplicates. If I filter them out during the process when I display the results, a total of less than 15 may be displayed.

I really need to figure out a way to do this in the query if possible.

Posted: Thu Feb 01, 2007 9:22 pm
by superdezign
And you can't do that? How do you count how many you display right now??

Use a variable that only increments when a result is valid, and retrieve results until that number reaches 15, or you run out of results to retrieve.

Posted: Thu Feb 01, 2007 9:38 pm
by csingsaas
Well I know how many results are returned that match the search criteria. So, the size of the result set for query A is 10 (for instance).

I want to then select 5 featured ads totally at random for query B. I could put the sql query in a loop and run through it five times. If it comes up with a record that is in result set A, don't add it, if it already exists in result set B, don't add it.

This method seems like it would work - does anyone have a differnt approach that would eliminate the looping and repeated queries?

Posted: Fri Feb 02, 2007 2:41 am
by mikeq
Well when the first query returns you would construct an array with the CAR_ADS.car_id, you are going to be iterating through the result set anyway so no extra processing required.

The implode it

Code: Select all

$StringOfCarIDs = implode(",",$ArrayOfCarIDs);
(if you car ids are not numbers then you will need to use " ',' " within the implode then add a ' to the start and end of the string of car ids.

Then use the string of car ids to ensure your second query does not include those records in the result set

Code: Select all

$Query = "SELECT DISTINCT CAR_ADS.car_owner, CAR_ADS.car_location, CAR_ADS.car_condition 
FROM CAR_ADS
INNER JOIN CAR_FEATURES ON (CAR_ADS.car_id = CAR_FEATURES.car_id)
WHERE CAR_ADS.car_id NOT IN ($StringOfCarIDs) 
AND CAR_ADS.car_adtype = 'featured' LIMIT <remainder>";
Get your database to do the work where possible, it is built to do it. Iterating through your code and issuing the same query over and over is not efficient coding.