CODEWITHSUNDEEP
phpmysqlarrays
iffy
iffy

Reputation: 146

Sum array values there key is the same

I am extracting some data from MySQL, then I am trying to sum up the values of the resulting array where the keys are the same. I have done this before just fine in this piece of code:

$q = "SELECT * FROM comenzi";
        $result = $odb->query($q);

        $sumArr = array();
        while($row = $result->fetch(PDO::FETCH_ASSOC)) {
            if ( !isset($sumArr[ $row['meniu_name'] ]) ) {
                $sumArr[ $row['meniu_name'] ] = $row['meniu_name'];
            }
            $sumArr[ $row['meniu_name'] ] += $row['cantitate'];
        }
        arsort($sumArr);
        $rowCount = 1;
        echo "<table><tr id='tableHeader'><td>#</td><td>produs</td><td>cantitate</td></tr>";
        foreach ($sumArr as $key => $value) {
            echo "<tr><td>" . $rowCount . "</td><td>" . $key . "</td><td>" . $value . "</td></tr>";
            $rowCount++;
        }
        echo "</table>";

That is my working example, which brings us to my next one that is somewhat similar, yet for some reason it does not yield the required results:

$q = "SELECT * FROM comenzi";
        $result = $odb->query($q);

        $sumArr = array();
        while($row = $result->fetch(PDO::FETCH_ASSOC)) {
            if ( !isset($sumArr[ $row['utilizator'] ]) ) {
                // $sumArr[ $row['utilizator'] ] = $row['utilizator'];
            }
            $sumArr[ $row['utilizator'] ] += $row['cantitate'];
        }
        arsort($sumArr);
        print_r($sumArr);
        $rowCount = 1;
        echo "<table><tr id='tableHeader'><td>#</td><td>utilizator</td><td>cantitate produse</td></tr>";
        foreach ($sumArr as $k => $v) {

            $odb                =           new PDO("mysql:host=".$host.";dbname=".$db, $user, $pass);

            $stmtCheck  =       $odb->prepare('SELECT nume, prenume, email, telefon FROM utilizatori WHERE id=?');
            $stmtCheck->execute(array($k));
            $r = $stmtCheck->fetch();


            echo "<tr><td>" . $rowCount . "</td><td>" . "<b>nume: </b>" . $r[0] . "<b> prenume: </b>" . $r[1] . "<b> email: </b>" . $r[2] . "<b> telefon: </b>" . $r[3] . "</td><td>" . $v . "</td></tr>";
            $rowCount++;
        }
        echo "</table>";

What happens here, is quite dubious: the commented line // $sumArr[ $row['utilizator'] ] = $row['utilizator'];, if it is commented out I get the expected results, however having it commented out I get the errors:

Notice: Undefined offset

If I do not comment that line I do not get the Undefined offset errors, but my values are ridiculous.

Correct values: Array ( [22] => 19 [32] => 11 )
Values with that line not commented out: Array ( [32] => 43 [22] => 41 )

I cannot even understand a mathematical way it gets to those values, can't find anything that could throw me the right direction, therefore I'm baffled.

Upvotes: -1

Views: 299

Answers (2)

Your Common Sense
Your Common Sense

Reputation: 157839

You need to learn SQL first: as the listing below illustrates, you can do all of this in one query, which is both simpler and more efficient than trying to drag down all of the data and then process it.

SELECT nume, prenume, email, telefon, utilizator, sum(cantitate) 
FROM comenzi, utilizatori WHERE id = utilizator GROUP BY utilizator

Upvotes: 0

OlivierH
OlivierH

Reputation: 3890

You just have to initialize your entry to 0 :

while($row = $result->fetch(PDO::FETCH_ASSOC)) {
    if(!isset($sumArr[ $row['utilizator'] ])) $sumArr[ $row['utilizator'] ] = 0;
    $sumArr[ $row['utilizator'] ] += $row['cantitate'];
}

The if line will create the $row['utilizator'] entry in the $sumArr array and will initialize its value to 0.

The problem you had is that you initialized it with a string I think : 

$sumArr[ $row['meniu_name'] ] = $row['meniu_name'];

IMO $row['meniu_name'] is a string. Then, when you do addition on a string, PHP tends to convert this string into integer, which gives you weird results you noticed.

Upvotes: 1

Related Questions