troubleshooting Question

MySQL query with Score

Avatar of ApOG
ApOG asked on
MySQL Server
4 Comments1 Solution549 ViewsLast Modified:
I am writing a query to get the best result out of all requirements. As the query always returns a result, I will need to give scores to the results so then I can get the one getting the better score.

The query now is:
SELECT camp_id, url FROM run_camps WHERE served < total AND (' . $sql_category . ' OR target_categories = "")  AND ((target_countries LIKE "%' . $sql_country . '%") OR target_countries = "") ORDER BY last_click ASC LIMIT 1

Open in new window

$sql_category it's just the result of php foreach that returns the possible categories, and $sql_country is the country code of the visitor.

So the query must search for a campaign that matches one of the categories or that has no categories, and that matches the country or has no countries.

For the best result would be to get first the campaign that matches country and category, then the ones that match country or category and then the others.

So I thought about a scoring system to order the results. So campaigns that match the category they get a score of 1 also campaigns that match country get a score of 1, so if it matches both, they get a score, then I order by score asc and I will get the results with the highest scores.

Is there a way to do that?

Thanks!
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros