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

[freebs]

• 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 
• 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!

Edited March 23, 2019 by freebs

[Barand]

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;

 

[freebs]

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...

Edited March 23, 2019 by freebs

[Barand]

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.

[freebs]

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. 

 

 

[Barand]

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>

 

[freebs]

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! 

 

Edited March 23, 2019 by freebs

[Barand]

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>

 

Edited March 23, 2019 by Barand

[freebs]

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.

[Barand]

Just create a pdo connection of your own, however you do it. I left that in just to show it was using PDO.

[freebs]

no matter what I do I cannot get that code to return anything..I can't even seem to var_vump the db connection..

Am I missing something here.. likely something silly I'm missing.

[Barand]

<?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>

 

[freebs]

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 ? 

[Barand]

It has to be a .php file. If it's a .html file the php code won't execute and will be treated as text.

[freebs]

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..

Edited March 23, 2019 by freebs

[Barand]

It was working when it left the shop.

[freebs]

lol.. that's a good one... it's likely either mysql version or php version then maybe..

[Barand]

Have you got php error reporting turned on?

[Barand]

What version of PHP are you using?

[freebs]

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

[freebs]

I also checked file permissions

 

[Barand]

Line 29:         $customer = $_GET['customer'] ?? '';

That code requires PHPv7 and should have given you an error.

Change it to

$customer = isset($_GET['customer']) ? $_GET['customer'] : '' ;

;

[freebs]

now it worked.. thank you.. not sure why i didn't get an error 

 

[Barand]

Have you got display errors ON or are they being written to the error log?

[freebs]

php ini says they are on.. but I guess not to screen.. as I don't get them.. screen just goes blank when i have an error. How would I turn that on?