CODEWITHSUNDEEP
phpmysql
user3304303
user3304303

Reputation: 1043

Can you do if/else logic in MYSQL statement?

I have football players in a database. They each have a ranking (1 is best) and I calculate their age on the fly in the MYSQL query. That query does an ORDER BY using their ranking plus their age. So I first have this...

$rankquerytext="ranking + age";

Then I have the query...

$sql = "SELECT *, FLOOR(DATEDIFF(CURDATE(), born) / 365.25) AS age 
    FROM players
    ORDER BY " . $rankquerytext . "";

This works just fine. However, I'd like to get a little more specific than just adding their age to their ranking. For example, I'd prefer to do an if/else either in the MySQL query somehow, or just before it, such as...

if (age < 25) {
$ageadjustment=-5;
} else if (age >= 29) {
$ageadjustment= 8;
} else {
$ageadjustment=0;
}

And then I'd like to add the "$ageadjustment" to their ranking, instead of the "age".

For example, when using "ranking + age", if Calvin Johnson is ranked #1, then his final ORDER BY figure is 30 (1 + 29).

Ideally, using "ranking + $ageadjustment", he would be 9 (1 + 8).

Is this doable? I am limited in my mysql knowledge, but this doesn't seem doable.

Thanks

Upvotes: 3

Views: 78

Answers (1)

Gyrocode.com
Gyrocode.com

Reputation: 58880

You can use CASE operator as shown below:

$rankquerytext = "ranking + adj";

$sql = "SELECT *,
 CASE WHEN FLOOR(DATEDIFF(CURDATE(), born) / 365.25) < 25 THEN -5 
 WHEN FLOOR(DATEDIFF(CURDATE(), born) / 365.25) >= 29 THEN 8
 ELSE 0 
 END AS adj 
 ORDER BY " . $rankquerytext;

Upvotes: 2

Related Questions