Posted on

LEFT JOIN 3 tables with where date GROUP BY and SUM

i have 3 tables : users, deposit, withdraw
table users :

id , username, referral
  1. 1, a1, null
  2. 2, a2, a1
  3. 3, a3, a2
  4. 4, a4, a1
  5. 5, a5, a2

table deposit :

id, users_id, amount, status, approve_date
  1. 1, 1, 10000.00, approve, 2022-10-01 14:52:53
  2. 2, 3, 10000.00, approve, 2022-10-01 14:52:53
  3. 3, 3, 10000.00, approve, 2022-10-01 14:52:53
  4. 4, 3, 10000.00, approve, 2022-10-01 14:52:53
  5. 5, 5, 10000.00, approve, 2022-10-01 14:52:53

table withdraw :

id, users_id, amount, status, approve_date
  1. 1, 1, 20000.00, approve, 2022-10-01 14:52:53
  2. 2, 3, 10000.00, approve, 2022-10-01 14:52:53
  3. 3, 3, 30000.00, approve, 2022-10-01 14:52:53
  4. 4, 3, 40000.00, approve, 2022-10-01 14:52:53
  5. 5, 5, 100000.00, approve, 2022-10-01 14:52:53

I would like to have the below end result, how do I combine the 3 tables together? The end result is sorted by SUM(deposit.amount) SUM(withdraw.amount) and where by approve date and search by referral . I apologize for couldn't display table data in a nice format for easier viewing. Thank you for your help.
what is try is like this sql code :

SELECT a.`referral`, a.`id`, a.`username`, SUM(b.`amount`) AS Total,SUM(c.`amount`) AS Totals FROM users a 
LEFT JOIN  `deposit` b ON a.`id` = b.`user_id` 
LEFT JOIN  `withdraw` c ON a.`id` = c.`user_id` 
WHERE 1
AND b.`approve_date` >= '2022-10-01 00:00:00' 
AND b.`approve_date` <= '2022-11-04 23:59:59' 
AND b.`status` = 'approve'
AND c.`status` = 'approve'
AND a.`referral` = 'a1' 
GROUP BY b.user_id,c.user_id
ORDER BY a.`id` DESC 
LIMIT 500 OFFSET 0

the result i want is like this:

username, total_deposit, total_withdraw

  1. a3, 30000, 80000
Posted on

Shooting fish game

I want to make a fish shooting game, but I don't know where to start maybe here friends can give me directions please help for my learning

Posted on

check multiple login same user codeigniter 4

hii all, btw i want to make login system, and check if user is multiple login and give notification in current user login, how can i make it ?? can someone please suggest/teach me to make system like that? Previously I've searched for it on google but couldn't find the right one,, please help for my learning

Posted on

self join with inner join on other tables together

hii how can i make self join and sum data in other table by username
i have table like this 

members (id, username, referral)
ref_bonus (id, username, bonus)

members
id  | username | referral
----------------------
1    id1        id2
2    id2        null
3    id3        id2

ref_bonus
id | username | bonus
----------------------
11    id2      1.00
21    id2      1.00
31    id2      1.00

the result i want is select all member have referral and sum bonus in ref_bonus
example result is like this : 

id  | username | count_ref | sum_bonus
----------------------
1    d2          2         | 3.0
Posted on

combination number php

i have function for combine number like this

function combin($mainArray, $size, $combinations = [])
    {
        if (empty($combinations)): $combinations = $mainArray; endif;

        if ($size == 1): return str_replace('-','',$combinations); endif;

        $newCombination = array();

        foreach ($mainArray as $key => $val):

            foreach ($combinations as $char):
                if(in_array($val, explode('-', $char))): continue; endif;
                $newCombination[] = $val . '-' . $char;
            endforeach;

        endforeach;

        return combin($mainArray, $size - 1, $newCombination);
    }

    $num = '1111111111';
    $num = str_split($num);
    //--//
    echo '<pre>';
    print_r(combin($num,4));
    echo '</pre>';

i have problem when i input number like this 1111111111
the result is like this and this result i wrong

Array
(
    [0] => 1
    [1] => 1
    [2] => 1
    [3] => 1
    [4] => 1
    [5] => 1
    [6] => 1
    [7] => 1
    [8] => 1
    [9] => 1
)

what i want the result is like this and this is the correct result

Array
(
    [0] => 1111
)

i hope someone can help me

Posted on

how can i combine number php

hello all my name is rico,
i want make some app like can combine number from input
example :
i have 4 variable like this
$num1 = 96;
$num2 = 20;
$num3 = 19;
$num4 = 37;

so how can i get the result like this
9213 9217 9293 9297 .. etc.

and example 2 if $num is empty then the result is like this
213 217 293 .. etc.

note : space in number is for star i can't write start in simbol in there