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

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:

 
Jump to Post

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 learning and sharing knowledge.