Jump to content

looking to SUM 2 separate columns as each.. not together.


freebs

Recommended Posts

  • Apache/2.4.6 (CentOS) OpenSSL/1.0.2k-fips mod_fcgid/2.3.9 PHP/5.5.38
  • Database client version: libmysql - 5.6.43
  • PHP extension: mysqli Documentation
  • PHP version: 5.5.38
  • Server: Localhost via UNIX socket
  • Server type: MySQL
  • Server version: 5.6.43 - MySQL Community Server (GPL)
  • Protocol version: 10

Current code

I used Pastebin to post the current PHP page I'm trying to figure out. I' need to get the values of the array total_value and total_balance to the HTML table.

Link to Array results - well one array.

var_dump of total_value array.

I'm not sure if it's a MySQL and a join with a derived table or what is best and how to accomplish this. Don't hammer on me to bad this is really my first time trying this. I'm going through PHP and MySQL course right now, but haven't really touched on this sort of thing yet.. mostly simple things.

 

Here is a link to the HTML table after a search for a customer/company was made. I search a company to get these results but now I'd like to total the value and balance on the bottom.

HTML table results

Any help or pointers is appreciated!

Link to comment
Share on other sites

Using a cut-down version of your array

$array = [
            'Aecon' => [ 0 => [ 'total_value' => '4991732.24' ] 
                       ],
            'All Weld' => [ 0 => [ 'total_value' => '341518.00' ] 
                       ],
            'Wolverine' => [ 0 => [  'total_value' => '4875.00' ]
                       ]
        ];

$total = 0;
foreach ($array as $company => $cdata) {
    $total += $cdata[0]['total_value'];
}
echo $total;           //--> 5338125.24

A more sensible structure for that data would be

$array = [ 'Aecon'     =>  4991732.24,
           'All Weld'  =>  341518.00,
           'Wolverine' =>  4875.00
         ];
$total = array_sum($array);
echo $total;             //--> 5338125.24

 

Now, about that data in your database table.

Do not store formatted numbers. The numeric value of "$372,568.00" is 0. You may as well try to get a total of your descriptions.

Store that as column type DECIMAL (eg decimal(12,2) ) with a value of 372568.00.

Once you have corrected the data then

SELECT customer
     , SUM(value) as total_value
     , SUM(balance) as total_balance
FROM mytable
GROUP BY customer;

 

Link to comment
Share on other sites

4 hours ago, Barand said:

Using a cut-down version of your array


$array = [
            'Aecon' => [ 0 => [ 'total_value' => '4991732.24' ] 
                       ],
            'All Weld' => [ 0 => [ 'total_value' => '341518.00' ] 
                       ],
            'Wolverine' => [ 0 => [  'total_value' => '4875.00' ]
                       ]
        ];

$total = 0;
foreach ($array as $company => $cdata) {
    $total += $cdata[0]['total_value'];
}
echo $total;           //--> 5338125.24

A more sensible structure for that data would be


$array = [ 'Aecon'     =>  4991732.24,
           'All Weld'  =>  341518.00,
           'Wolverine' =>  4875.00
         ];
$total = array_sum($array);
echo $total;             //--> 5338125.24

 

Now, about that data in your database table.

Do not store formatted numbers. The numeric value of "$372,568.00" is 0. You may as well try to get a total of your descriptions.

Store that as column type DECIMAL (eg decimal(12,2) ) with a value of 372568.00.

Once you have corrected the data then


SELECT customer
     , SUM(value) as total_value
     , SUM(balance) as total_balance
FROM mytable
GROUP BY customer;

 

 

Thanks for your answer , If I'm understanding you correctly, you're assuming I added this array manually?

The Aarray I have is a variable got by $totalv = $stmt->fetchAll(\PDO::FETCH_GROUP|\PDO::FETCH_ASSOC);

This was a simple CRUD and I need to do calculations on it now.. so when a person uses the SEARCH for Company, the result returned needs to be summed for the value and the balance columns. Also the format in mysql for the actual data is simply 123456.00 I used a function to format that data, I thought this was the correct way to do this.

From what I have read and told by a couple others is that I need a derived table and a join to the data into a table.

+---------------------+-------------+
| customer            | total_value |
+---------------------+-------------+
| Aecon               |  4991732.24 |
| All Weld            |   341518.00 |
| ANJ                 |        0.00 |
| Casadei Steel       |   205000.00 |
| CIMS                |     3690.00 |
| Covia               |  9048899.00 |
| Curran Contractors  |  1022036.00 |
| Great Lakes Fab     |    62131.00 |
| Shell Canada        |   196780.00 |
| St Clair Mechanical |     4800.00 |
| Suncor Ethanol      |    15080.00 |
| Wolverine           |     4875.00 |
+---------------------+-------------+

that's the sum group by customer from CLI.  Would it be best to create a derived table and join? This is where I'm a little lost...

Link to comment
Share on other sites

7 minutes ago, freebs said:

Would it be best to create a derived table and join?

Without knowing your your current structures and what you propose to put in the "derived table" then I cannot say.

If you are storing totals that is already derived data and should be avoided. Instead you should query the data that makes up the totals to get them when required.

12 minutes ago, freebs said:

Thanks for your answer , If I'm understanding you correctly, you're assuming I added this array manually? 

The Aarray I have is a variable got by $totalv = $stmt->fetchAll(\PDO::FETCH_GROUP|\PDO::FETCH_ASSOC);

Sorry I am not clairvoyant. All you showed was a link to an array and a link to what you said was you table data.

Link to comment
Share on other sites

yes sorry, I'm not always clear on my descriptions but I try.

here is a link to the page I'm trying to figure out. 

Current code page

Of course, I have a couple of things out with // as I'm trying different things. Just trying to get the SUM for Value and Balance on the RESULTS page of the search.

Also, others have said I need a sub-query to get what I want.. there is likely a few ways to do this, but I have tried without success.. this is the closest I've gotten so far is the Array with the data required. 

 

 

Link to comment
Share on other sites

You only need a single connection per page (unless you want to connect to multiple servers.

Also you only need one query

$db = pdoConnect();                                    

$customer = $_GET['customer'] ?? '';                   // you want data for display only, so use GET instead of POST
$tdata = '';

if ($customer) {
    $res = $db->prepare("SELECT work_order
                              , customer
                              , description
                              , value
                              , balance
                              , status
                              , notes
                         FROM jobtest
                         WHERE customer = ?     
                        ");
    $res->execute([$customer]);
    $tot_value = $tot_balance = 0;
    
    foreach ($res as $row) {
        $tot_value += $row['value'];
        $tot_balance += $row['balance'];          // accumulate totals as they are fetched
        $tdata .= "<tr><td>{$row['work_order']}</td>
                       <td>{$row['customer']}</td>
                       <td>{$row['description']}</td>";
        $tdata .= '<td>' . toCurrency($row['value']) . '</td>';               
        $tdata .= '<td>' . toCurrency($row['balance']) . '</td>';               
        $tdata .= "<td>{$row['status']}</td>
                   <td>{$row['notes']}</td></tr>";
    }
    $tdata .= "<tr><td colspan='3'>Totals</td><td>" . toCurrency($tot_value) . '</td><td>' . toCurrency($tot_balance) . "</td><td colspan='2'></td></tr>";
}

function toCurrency($number)
    {
        return '$' . number_format($number,2);
    }
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="creation-date" content="03/23/2019">
<title>Sample</title>
</head>
<body>

        <table border='1' style='border-collapse:collapse'>
            <tr>
            <th>Work Order #</th>
              <th>Customer</th>
              <th>Description</th>
              <th>Value</th>
              <th>Balance</th>
              <th>Status</th>
              <th>Notes</th>
            </tr>
            
            <?=$tdata?>
</body>
</html>

 

Link to comment
Share on other sites

Wow, thanks very much! that is quite different from what I have. However, I do need the submit since there is a form on the bottom which is my search function. I'll try an incorporate this into it. When you first come from the main php page to this find customer.. it's a simple search submit function button. once criteria are entered and valid it returns the rows else is says can't find the customer.

I do see that you are consecutively adding the values together to get total_value.. I didn't even think of that. I was trying to get the values from the SUM array.

Is it possible to use the search function on the page with what you've given me? I'll try and see what happens!

Thanks so much for your time and effort on this.... I can send you money for a couple beers! 

 

Link to comment
Share on other sites

39 minutes ago, freebs said:

Is it possible to use the search function on the page with what you've given me? I

Yes, it already searches for the customer in $_GET['customer'].

Just add

<form>
    <input type="text" name="customer">
    <input type="submit" name="btnSubmit" value="Search">
</form>

 

Link to comment
Share on other sites

Hmm I can't get this to work at all.. does  $db = pdoConnect();  require a class connection php page ? I haven't got that.. just a config.php with host; username..password... and so on with error checking with PDO:: ATTR_ERRMODE and exception.

Link to comment
Share on other sites

<?php
define("HOST",'localhost');
define("USERNAME",'????');
define("PASSWORD",'????');
define("DATABASE", "????");

function pdoConnect() 
{
    $dsn = "mysql:dbname=".DATABASE."; host=".HOST."; charset=utf8";

    $db = new pdo($dsn, USERNAME, PASSWORD, 
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_EMULATE_PREPARES => false,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::MYSQL_ATTR_LOCAL_INFILE => true
        ]);
    return $db;
}

function toCurrency($number)
{
    return '$' . number_format($number,2);
}


$db = pdoConnect();                                    

$customer = $_GET['customer'] ?? '';                   // you want data for display only, so use GET instead of POST
$tdata = '';

if ($customer) {
    $res = $db->prepare("SELECT work_order
                              , customer
                              , description
                              , value
                              , balance
                              , status
                              , notes
                         FROM jobtest
                         WHERE customer = ?     
                        ");
    $res->execute([$customer]);
    $tot_value = $tot_balance = 0;
    
    foreach ($res as $row) {
        $tot_value += $row['value'];
        $tot_balance += $row['balance'];          // accumulate totals as they are fetched
        $tdata .= "<tr><td>{$row['work_order']}</td>
                       <td>{$row['customer']}</td>
                       <td>{$row['description']}</td>";
        $tdata .= '<td>' . toCurrency($row['value']) . '</td>';               
        $tdata .= '<td>' . toCurrency($row['balance']) . '</td>';               
        $tdata .= "<td>{$row['status']}</td>
                   <td>{$row['notes']}</td></tr>";
    }
    $tdata .= "<tr><td colspan='3'>Totals</td><td>" . toCurrency($tot_value) . '</td><td>' . toCurrency($tot_balance) . "</td><td colspan='2'></td></tr>";
}

?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="creation-date" content="03/23/2019">
<title>Sample</title>
</head>
<body>
<form>
    <input type="text" name="customer">
    <input type="submit" name="btnSubmit" value="Search">
</form>
<hr>
        <table border='1' style='border-collapse:collapse'>
            <tr>
            <th>Work Order #</th>
              <th>Customer</th>
              <th>Description</th>
              <th>Value</th>
              <th>Balance</th>
              <th>Status</th>
              <th>Notes</th>
            </tr>
            
            <?=$tdata?>
</body>
</html>

 

Link to comment
Share on other sites

Thanks Barand.!! the file works kind of.. I had to change the name to HTML, but the top portion is like a var_dump of the SELECT statement.. weird.. The search function yields no results..... shouldn't the file end in .php ? 

Link to comment
Share on other sites

that's what I thought.. this is bizarre .. HTML work as in it displays things.. I then name it test.php and nothing.. blank screen no errors. I even tried to echo something out.

I'm using bluefish as an editor if that makes a difference..

Link to comment
Share on other sites

error_reporting = E_ALL & ~E_DEPRECATED & ~E_STRICT in php.ini   PHP 5.5.38 (cli) (built: Jul 21 2016 12:25:20)
Copyright (c) 1997-2015 The PHP Group
Zend Engine v2.5.0, Copyright (c) 1998-2015 Zend Technologies
    with Zend OPcache v7.0.6-dev, Copyright (c) 1999-2015, by Zend Technologies

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.