CODEWITHSUNDEEP
phpmysql
user2515606
user2515606

Reputation: 182

PHP/SQL : If statement within the query?

Hi I am trying to create something but I can't have my total number being a negative. So basically I got values that are negative that can be multiplied into a somewhat complex mathematics equation.

So essentially... user inputs a data from -1 to 1. (-1, 0, 1) And it get's multiplied into my formula. So my SQL query looks like this... (this part works!)

SELECT *, a, b, c AS TOTALNUMBER FROM MATH
ORDER BY TOTALNUMBER DESC

However, I need the total number to always be positive. So I have been trying for the past few hours to figure this out. I am sort of new to php/sql.

I am trying to include something like...

if (TOTALNUMBER < 0 ) {
TOTALNUMBER * -1.0
}

However I have no idea where to include this in the query or how to write it properly.

To clarify and update what I am looking for... User can input -1,0,1

Data for A, B, C is for example. 10, 15, 20 User inputs: 1, -1, 0

A total = 10
B total = -15
C total = 0
Total ABC = -5

However, I need total to be 5 instead of -5 without changing any A, B, C values.

Upvotes: 1

Views: 76

Answers (2)

BK435
BK435

Reputation: 3176

IF(expr1,expr2,expr3)

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used.

SELECT a, b, c, If (c <0 , c * -1, c) AS TOTALNUMBER FROM MATH
ORDER BY TOTALNUMBER DESC;

If you want the sum of all the fields to be your total number, assuming you have an id field:

SELECT a,b,c,ABS((sum(a)+(b)+(c))) AS TOTALNUMBER FROM MATH
group by mathid ORDER BY TOTALNUMBER DESC;

Here is an example:

mysql> select * from math;
+--------+----------+
| idmath | mathcol1 |
+--------+----------+
|      1 |        1 |
|      2 |        3 |
|      3 |       -1 |
|      4 |       -3 |
+--------+----------+
4 rows in set (0.00 sec)

mysql> SELECT idmath, If (mathcol1 <0 , mathcol1 * -1, mathcol1) AS TOTALNUMBER
FROM MATH ORDER BY TOTALNUMBER DESC;
+--------+-------------+
| idmath | TOTALNUMBER |
+--------+-------------+
|      2 |           3 |
|      4 |           3 |
|      1 |           1 |
|      3 |           1 |
+--------+-------------+
4 rows in set (0.00 sec)

mysql> SELECT idmath, mathcol1, If (mathcol1 <0 , mathcol1 * -1, mathcol1) AS TO
TALNUMBER FROM MATH ORDER BY TOTALNUMBER DESC;
+--------+----------+-------------+
| idmath | mathcol1 | TOTALNUMBER |
+--------+----------+-------------+
|      2 |        3 |           3 |
|      4 |       -3 |           3 |
|      1 |        1 |           1 |
|      3 |       -1 |           1 |
+--------+----------+-------------+
4 rows in set (0.00 sec)

mysql> SELECT mathcol1,mathcol2,mathcol3, (sum(ABS(mathcol1))+(mathcol2)+(mathco
    l3)) AS TOTALNUMBER FROM MATH group by idmath ORDER BY TOTALNUMBER DESC;
    +----------+----------+----------+-------------+
    | mathcol1 | mathcol2 | mathcol3 | TOTALNUMBER |
    +----------+----------+----------+-------------+
    |        3 |        2 |        3 |           8 |
    |       -3 |        2 |        3 |           8 |
    |       -1 |        2 |        3 |           6 |
    |        1 |        2 |        3 |           6 |
    +----------+----------+----------+-------------+
    4 rows in set (0.00 sec)

You can read more here at dev.mysql.

Upvotes: 1

Sagar Devkota
Sagar Devkota

Reputation: 1192

use like this

SELECT *, a, b, ABS(c) AS TOTALNUMBER FROM MATH
ORDER BY TOTALNUMBER DESC

Upvotes: 4

Related Questions