PHP和MySQL：SUM值BY DISTINCT类别

Question

thank you before for take an interest into my case. In short i want to tell that i wonder if Value in MySQL table can be counted using by just MySQL query or need PHP code to count. And i have no idea how to code it.

Case :

I have tbl_transaction which has format like this :

id | ref | description | value | date | category | sub_category

Value is an INT type column, for example it contains 10000, 20000, etc..

Let's say that i want to SUM value column, but by DISTINCT Category

So for example, it's going to work like this :

category A SUM minus(-) category B SUM plus(+) category C SUM = Result of Counted SUM

Experiment :

I tried something like this, obviously wont work but i try to show my logic for solve a case here.

SELECT SUM(value) as MAIN_CAT_TOTAL_VAL 
  FROM tbl_transaction 
 WHERE monthTransaction LIKE '$newmonthminusone%' ONE
     , ONE+3

Desired Learning :

I want to know how mySQL query can be printed into PHP. And code behind that if possible, i actually need the code. Some say that mysql_ function isnt really up-to-date anymore and shouldn't be used. But i have no bright knowledge on how to use new function that replace mysql_ function, like maybe PDO and some kind like that. I really need explanation. I learn from code and case, that's how i adapt at least. So code would be help me much.

Desired Output :

Number / value, result of SUMMED UP table calculation (math). 

I have no idea at all, how to code and start. Been stuck for hours. I sincerely need help for this. Ty so much.

Answer 1

mysqli_* is pretty much a drop-in replacement for mysql_*, and is a good transition to learning PDO, as you can use your (mostly) unmodified existing code, and transition toward the object-oriented approach and prepared statements as you learn about them.

That said, what you are probably looking for in a query is something like:

SELECT
    category,
    SUM(value) AS MAIN_CAT_TOTAL_VAL
FROM tbl_transaction
WHERE monthTransaction LIKE '$newmonthminusone%' AS ONE
GROUP BY category;

Then you'll be able to work with it like so:

$link = mysqli_connect("localhost", "dbuser", "dbpassword", "dbname");
$query = mysqli_query($link, $queryStringFromAbove);
$total = 0;
while($row = mysqli_fetch_assoc($query)) {
    switch($row['category']) {
        case "A":
        case "C":
            $total += $row['MAIN_CAT_TOTAL_VAL'];
            break;
        case "B":
            $total -= $row['MAIN_CAT_TOTAL_VAL'];
            break;
    }
}
echo $total;

Edit: It's difficult to tell precisely your conditions of adding or subtracting, but the switch() block should be flexible enough to customize to your needs.