Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

jlrdw's avatar
jlrdw
OP
9 years ago
Level 75

Join instead of group by

@willvincent Or anyone I know you are good at this stuff, I have a group by and was wondering can the same be done with just joins and order by. I have fiddled but no luck yet. This is rough draft only using just pdo, in real production I'd change the POST to the laravel request way, I am just trying to work out a mysql pdo query right now, in laravel I can use the getPDO-> if needed. Here is a screen shot of output: https://drive.google.com/file/d/0B1_PFw--3o74LUdQR1lyWE1WaEk/view?usp=sharing This works great, and here is the code for group by:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
        <title>Untitled Document</title>
        <style>
            #customers {
                font-family: "Trebuchet MS", Arial, Helvetica, sans-serif;
                border-collapse: collapse;
                width: 100%;
            }

            #customers td, #customers th {
                border: 1px solid #ddd;
                text-align: left;
                padding: 8px;
            }

            #customers tr:nth-child(even){background-color: #f2f2f2}

            #customers tr:hover {background-color: #ddd;}

            #customers th {
                padding-top: 12px;
                padding-bottom: 12px;
                background-color: #4CAF50;
                color: white;
            }
        </style>
    </head>

    <body>

        <?php
        $host = "localhost";
        $db = "checkbook";
        $user = "root";
        $pass = "zzzzzzzzzzzz";
        $conn = new PDO("mysql:host=$host;dbname=$db", $user, $pass);
        //$bsdate = $_POST['begindate'];
        //$esdate = $_POST['enddate'];
        $bsdate = $_POST['begindate'];
        $esdate = $_POST['enddate'];

        $btime = strtotime($bsdate);

        $bdate = date('Y-m-d H:i:s', strtotime($bsdate));

        $etime = strtotime($esdate);

        $edate = date('Y-m-d H:i:s', strtotime($esdate));

        $sql = "select distinct `account_types`.`AccountType` AS `AccountType`,`accounts`.`AccountNumber` AS `AccountNumber`,`accounts`.`AccountName` AS `AccountName`,sum(`transactions`.`Expense`) AS `Sum_Expense`,sum(`transactions`.`Income`) AS `Sum_Income` from ((`account_types` join `accounts` on((`account_types`.`AccountTypeID` = `accounts`.`AccountTypeID`))) join `transactions` on((`accounts`.`AccountID` = `transactions`.`AccountID`))) where (`transactions`.`TransactionDate` Between '$bdate' and  '$edate') group by `account_types`.`AccountType`,`accounts`.`AccountNumber`,`accounts`.`AccountName`";
        $q = $conn->query($sql) or die("failed!");

        echo "<table id=\"customers\" style=\"width:94%;\">";


        echo "<tr>";

        echo "<th style=\"width:30px; text-align: left;\">type</th>";
        echo "<th style=\"width:80px; text-align: left;\">acct</th>";
        echo "<th style=\"width:80px; text-align: left;\">Expenses</th>";
        echo "<th style=\"width:80px; text-align: left;\">income</th>";

        echo "</tr>";
        $ke = 0;
        $ki = 0;
        while ($r = $q->fetch(PDO::FETCH_ASSOC)) {

            if ($r['AccountType'] == "expense") {
                $ke = $ke + 1;

                if ($ke < 2) {
                    //echo " ====" . $r['AccountType'] . "<br>";
                    echo "<tr>";
                    echo "<td style=\"width:30px;\">" . $r['AccountType'] . "</td>";
                    echo "</tr>";
                    echo "<tr>";
                    //echo "<td style=\"width:80px;\">" . $r['AccountType'] . "</td>";
                    echo "<td style=\"width:30px;\"></td>";
                    echo "<td style=\"width:80px;\">" . $r['AccountName'] . "</td>";
                    echo "<td style=\"width:80px;\">" . $r['Sum_Expense'] . "</td>";
                    echo "<td style=\"width:80px;\">" . $r['Sum_Income'] . "</td>";
                    echo "</tr>";
                } else {
                    echo "<tr>";
                     echo "<td style=\"width:30px;\"></td>";
                    echo "<td style=\"width:80px;\">" . $r['AccountName'] . "</td>";
                    echo "<td style=\"width:80px;\">" . $r['Sum_Expense'] . "</td>";
                    echo "<td style=\"width:80px;\">" . $r['Sum_Income'] . "</td>";
                    echo "</tr>";
                }
            } else {
                $ki = $ki + 1;
                if ($ki < 2) {
                    //echo " ====" . $r['AccountType'] . "<br>";
                    echo "<tr>";
                    echo "<td style=\"width:30px;\">" . $r['AccountType'] . "</td>";
                    echo "</tr>";
                    echo "<tr>";
                    //echo "<td style=\"width:80px;\">" . $r['AccountType'] . "</td>";
                    echo "<td style=\"width:30px;\"></td>";
                    echo "<td style=\"width:80px;\">" . $r['AccountName'] . "</td>";
                    echo "<td style=\"width:80px;\">" . $r['Sum_Expense'] . "</td>";
                    echo "<td style=\"width:80px;\">" . $r['Sum_Income'] . "</td>";
                    echo "</tr>";
                } else {
                    echo "<tr>";
                    echo "<td style=\"width:30px;\"></td>";
                    echo "<td style=\"width:80px;\">" . $r['AccountName'] . "</td>";
                    echo "<td style=\"width:80px;\">" . $r['Sum_Expense'] . "</td>";
                    echo "<td style=\"width:80px;\">" . $r['Sum_Income'] . "</td>";
                    echo "</tr>";
                }
            }
        }
        ?>

        </table>

    </body>
</html>

Again I just want to get it in a test single page at first and I can go from there. Oh, the raw return without looping through looks like this: https://drive.google.com/file/d/0B1_PFw--3o74M1hzcnNiUWkxc2s/view?usp=sharing The results are correct, I just worry about group by when there is a lot more records. The query is only getting one month at a time.

0 likes
8 replies
willvincent's avatar

Making query more readable (mostly for myself...)

SELECT DISTINCT account_types.AccountType AS AccountType, 
       accounts.AccountNumber AS AccountNumber,
       accounts.AccountName AS AccountName, 
       SUM(transactions.Expense) AS Sum_Expense,
       SUM(transations.Income) AS Sum_Income
FROM account_types
JOIN accounts ON account_types.AccountTypeID = accounts.AccountTypeID
JOIN transactions ON accounts.AccountID = transactions.AccountID
WHERE transactions.TransactionDate BETWEEN $bdate AND $edate
GROUP BY account_types.AccountType, 
         accounts.AccountNumber, 
         accounts.AccountName;
willvincent's avatar

So, you're trying to determine if you can do this without group by? Probably not..

Can you send me a DB dump to poke around with? Why are account name and account number the same?

jimmck's avatar

You are aggregating, you need a group by.

1 like
willvincent's avatar

I wouldn't worry about whether there are a lot more records or not though.. group by is plenty efficient.

The only real alternative would be to just fetch each record, and manually aggregate the results, which obviously would be much slower and chew up extra memory.

jlrdw's avatar
jlrdw
OP
9 years ago
Level 75

@willvincent and @jimmck thanks, records will probably only grow to maybe 3000 or less, because in January's old data will be closed out to a backup. I was just trying something else, with the what if it grew to 50000 or more thought. I try to learn as I program. Of course if it got that big, I would have a duplicate on local I just export the latest months records to, use a odbc connection and probably just run a report from ms access. That would be one way, since large data and group by via the web is questionable. I just look for good ways to handle stuff. And I admit, there are some pretty smart folks on this forum.

Edit: It hit hard how to do it.

  • Have an empty table named treport
  • Select the month you want, duplicate only them to that table
  • Query treport
  • can leave the data, just truncate before use again

This would probably work as long as you are not dealing with tens of thousands of records.

willvincent's avatar

what if it grew to 50000 or more

... pagination? ;)

jlrdw's avatar
jlrdw
OP
9 years ago
Level 75

@willvincent see previous, also the actual report is summaries and is always only one page, it's the group by that has to go over entire table to get results. This is one of those things I am just trying to learn as I program, and get the best query possible. I know you write these more complex queries all the time. But there are nifty little tricks, like duplicating a record in another table till that month's report is finished. I.E., having the full table, and a monthly table just for report purposes. Of course this can get complex due to edited records, but that's what flags are for. Being a database manager can require a lot of thought. You know this already.

Please or to participate in this conversation.