I am trying to output data for selected dates and think I have managed to do the sql query but it's currently not outputting the total amount, below is what it is currently outputting

I am looking for the following

From To Expense Category Expenses Total
01/01/2018 31/12/2018 Accounting Costs £0.00
01/01/2018 31/12/2018 Marketing £0.00

Below is the current code I have

<?php
            if (isset($_POST['submit'])){
                include('dbconnection.php');
                $from=date('Y-m-d',strtotime($_POST['from']));
                $to=date('Y-m-d',strtotime($_POST['to']));

                $oquery=$mysqli->query("SELECT ec.expense_category_id, ec.expense_category_name,sum(e.expenseamount), date_format('$from','%d/%m/%Y') as `from`, date_format('$to','%d/%m/%Y') as `to` FROM expenses e join expense_category ec ON e.expense_category_id = ec.expense_category_id WHERE e.expddate between '$from' and '$to' group by ec.expense_category_name");

                if ($oquery->num_rows > 0) {
                while($orow = $oquery->fetch_array()){

                ?>

                <tr>
                        <td><?php echo $orow['from'] ?></td>
                        <td><?php echo $orow['to'] ?></td>
                        <td><?php echo $orow['expense_category_name'] ?></td>
                        <td><?php echo "" . '&#163;' . number_format($orow['expenseamount'], 2) . "";?></td>
                    </tr>
                    <?php 
                    }
                } else {
                    echo "0 Results";
                }
            }
        ?>

Can someone help please as I'm not 100% on PHP, I'm not sure if is to do with my sql query or is something else?

Recommended Answers

All 3 Replies

Sorry, can you please clarify what it is currently outputting and what you want it to be outputting instead?

From       To         Expense Category     Expenses Total
01/01/2018 31/12/2018 Accounting Costs     £0.00
01/01/2018 31/12/2018 Marketing            £0.00

In your SQL query, you are fetching the following 5 columns:

expense_category_id
expense_category_name
sum of expenses
from date
to date

Is it that it has expenses total marked as 0 that is not what you're wanting?

It's ok I managed to get it working in the end

If I may, it's not a good practice to use $_POST directly.

You should use something like

$from = filter_input(INPUT_POST, "from");
$to = filter_input(INPUT_POST, "to");

Read more about filter_input : https://www.php.net/manual/fr/function.filter-input.php

There are filters you can apply to make sure the values you receive is what you intend and not invasive code.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.