MySQL query with Score
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:
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!
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
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!
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Howdy.
Firstly, before I forget, please ensure to double check (' . $sql_category . ' OR target_categories = "") -- it would appear the other part of the criteria is missing unless your PHP code for $sql_category includes the column and comparative operator in addition to the actual category value.
For the score, you can use CASE or IF in MySQL to achieve this.
Basically, as you said, just have a column called `Score` that is of formula:
(IF(target_categories = "'. $sql_category . '", 1, 0) + IF(target_countries like "%'. $sql_country . '%", 1, 0))
Hope that makes sense.
Firstly, before I forget, please ensure to double check (' . $sql_category . ' OR target_categories = "") -- it would appear the other part of the criteria is missing unless your PHP code for $sql_category includes the column and comparative operator in addition to the actual category value.
For the score, you can use CASE or IF in MySQL to achieve this.
Basically, as you said, just have a column called `Score` that is of formula:
(IF(target_categories = "'. $sql_category . '", 1, 0) + IF(target_countries like "%'. $sql_country . '%", 1, 0))
Hope that makes sense.
Hey! Thanks for your post.
But how do I add that to the query??
The $sql_category is given in the following format by php:
$sql_category = 'target_categories LIKE "%cat name1%" OR target_category LIKE "%cat name2%"';
Thanks!!
But how do I add that to the query??
The $sql_category is given in the following format by php:
$sql_category = 'target_categories LIKE "%cat name1%" OR target_category LIKE "%cat name2%"';
Thanks!!
You would add it like this:
'SELECT camp_id, url, IF('. $sql_category .', 1, 0)+IF(target_countries like "%'. $sql_country . '%", 1, 0) as score FROM run_camps WHERE served < total AND (' . $sql_category . ' OR target_categories = "") AND ((target_countries LIKE "%' . $sql_country . '%") OR target_countries = "") ORDER BY score DESC, last_click ASC LIMIT 1'
'SELECT camp_id, url, IF('. $sql_category .', 1, 0)+IF(target_countries like "%'. $sql_country . '%", 1, 0) as score FROM run_camps WHERE served < total AND (' . $sql_category . ' OR target_categories = "") AND ((target_countries LIKE "%' . $sql_country . '%") OR target_countries = "") ORDER BY score DESC, last_click ASC LIMIT 1'
Premium Content
You need an Expert Office subscription to comment.Start Free Trial