PHP/SQL : If statement within the query?

Question

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.

WHERE TOTALNUMBER < 0? I'm not sure about the `TOTALNUMBER * -1.0. — Script47
– Script47, Commented Aug 7, 2015 at 1:05
Hi there, the totalnumber if negative needs to multiply with negative 1 to become positive and then order by desc. — user2515606
– user2515606, Commented Aug 7, 2015 at 1:08
@user2515606 I'm not sure if this will be any help. stackoverflow.com/questions/63447/… — Script47
– Script47, Commented Aug 7, 2015 at 1:15
Saw the link earlier. I understand the SELECT IF but I need it to select no matter what but just flip the negative number to a positive... which I am not sure how to do. — user2515606
– user2515606, Commented Aug 7, 2015 at 1:29

Sagar Devkota · Accepted Answer · 2015-08-07 01:52:56Z

4

use like this

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

answered Aug 7, 2015 at 1:52

Sagar Devkota

1,1928 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2515606 Over a year ago

Is there no way to make my TOTALNUMBER positive opposed to making my negative values positive? I know it sounds silly but thats just how my formula is set up.

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Aug 7, 2015 at 1:32

BK435

3,1863 gold badges22 silver badges27 bronze badges

4 Comments

user2515606 Over a year ago

I've tried this but the numbers still remain negative.

user2515606 Over a year ago

Hi thank you for the answer but unfortunately the way that my formula is set up I need a, b, c to be positive or negative but the totalnumber to always output a positive number.

user2515606 Over a year ago

Thanks for all the help, I have edited my post to see if you can better understand my problem. It's kind of hard to explain

Drew Over a year ago

yo, bk, take a look at the answers to this question for that other question. Plus the guy has 3 semi-colons in today's question, should have 1. Main thing to point out are the extra semi-colons

Collectives™ on Stack Overflow

PHP/SQL : If statement within the query?

2 Answers 2

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related