display data from the database (summarizes data)

display data from the database (summarizes data) - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: display data from the database (summarizes data) (/showthread.php?tid=73410)

Pages: 1 2

display data from the database (summarizes data) - DELE - 04-21-2019

please help me to get this enlightenment.
I want to display data from the database to be like this (summarizes data from the database).
[Image: i725ax.png]

this is just an example and ignore total penjualan and pendapatan.

from this table
[Image: 9k8ear.png]

thanks before

RE: display data from the database - php_rocs - 04-21-2019

@DELE,

I'm unable to see the images. Can you try to fix them?

RE: display data from the database - DELE - 04-21-2019

(04-21-2019, 11:01 AM)php_rocs Wrote: @DELE,

I'm unable to see the images. Can you try to fix them?

I fixed it

RE: display data from the database (summarizes data) - php_rocs - 04-22-2019

@DELE,

I need to understand what the third column is suppose to be. I get the first column. Ignore the second column. What is the third column? is it a summary of a specific user? Do you want a fourth and fifth column? if so what values? Have you come up with a basic query?

RE: display data from the database (summarizes data) - DELE - 04-22-2019

Example: See the table 'income_user' => '00002' the result I want is like this.

ID NAMA INCOME/COUNTRY TOTAL SALES TOTAL INCOME
00001
00002 - 4US, 1PH 5 $ 53.00
00003

Information :
INCOME/COUNTRY = the result of merging 'income_country' based on 'income_user' and summed and separated by country.
TOTAL SALES = total sales from all countries (4US + 1PH = 5).
TOTAL INCOME = the result of adding 'income_value' based on 'income_user'.

for query problems I don't understand what to do. my mind is still blank.
my query is still like this:
SELECT * FROM incomes

RE: display data from the database (summarizes data) - InsiteFX - 04-22-2019

You need to find another free image hosting service, that one is erring all over the net.

RE: display data from the database (summarizes data) - DELE - 04-22-2019

(04-22-2019, 08:23 AM)InsiteFX Wrote: You need to find another free image hosting service, that one is erring all over the net.

picture is available

RE: display data from the database (summarizes data) - Wouter60 - 04-22-2019

I think the table structure is making this very complex.
If you store the income values, you'd better save every entry for every country separately.

E.g.
ID, income_user, income_country, income_count, income_amout.

User '00003' would get 4 records in stead of one:
00001, US, 1, 10
00001, PH, 3, 30
00001, CN, 2, 20
00001, TH, 9, 90

This would make it much easier to count the total counts and amounts per user and per country, and display them in a summary.

RE: display data from the database (summarizes data) - DELE - 04-22-2019

thank you for those who have tried to help me.

I have got what I want.

by using a query like this:

Code:
$this->db->query("

    SELECT income_user, SUM(income_value) AS income_value, GROUP_CONCAT(income_country SEPARATOR ',') AS income_country

    FROM incomes

    GROUP BY income_user

")->result();

results :

Code:
Array

(

    [0] => stdClass Object

        (

            [income_user] => 00001

            [income_value] => 1962.00

            [income_country] => 1US,2US,3US,3US,3US,3US,3US,3US,3US,3US,1US,1PH,1US,1US,1ID,1IN,1IN,1US,1US

        )

    [1] => stdClass Object

        (

            [income_user] => 00002

            [income_value] => 53.00

            [income_country] => 2US,1US,1US,1PH

        )

    [2] => stdClass Object

        (

            [income_user] => 00003

            [income_value] => 150.00

            [income_country] => 1US, 3PH, 2CN, 9TH

        )

)

the question:

how do I sum income_country per-country?

the method I just created is like this:

Code:
<tbody>

    <?php foreach ($data['users_incomes'] as $row): ?>

        <tr>

            <td>

                <?php echo $row->income_user ?>

            </td>

            <td>

            </td>

            <td>

                <?php foreach (explode(',', $row->income_country) as $test): ?>

                    <?php echo $test ?> // HERE

                <?php endforeach ?>

            </td>

            <td>

                <?php echo array_sum(explode(',', $row->income_country)) ?>

            </td>

            <td>

                <?php echo '$ '.$row->income_value ?>

            </td>

        </tr>

    <?php endforeach ?>

</tbody>

RE: display data from the database (summarizes data) - php_rocs - 04-24-2019

@DELE,

Getting income per country becomes a little trickier because you have multiple countries per record. Does each country get the whole income_value or is it divided between the countries?

demo of data above [ http://sqlfiddle.com/#!9/a8074e/1 ]