We help IT Professionals succeed at work.
Get Started

MySQL query with Score

ApOG
ApOG asked
on
546 Views
Last Modified: 2012-05-11
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!
Comment
Watch Question
Chief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE