Page 1 of 1

Re: SUM profit issue

Posted: Thu Feb 04, 2016 10:51 am
by Celauran
For openers, delete this entire section. This is a copy/paste from the top of the file and entirely unnecessary. (And if it were necessary, it should still be at the top of the file, but that's a separate issue).

Code: Select all

<?php

include('connect.php');
$data = [];
if (isset($_GET['d1']) && isset($_GET['d2'])) {
    $d1 = strtotime($_GET['d1']);
    $d2 = strtotime($_GET['d2']);
    if ($d1 && $d2) {
        $start_date = date('Y-m-d', $d1);
        $end_date = date('Y-m-d', $d2);
        $result = $db->prepare("SELECT SUM(profit) FROM purchased_software WHERE sales_month BETWEEN :a AND :b");
        $result->bindParam(':a', $start_date);
        $result->bindParam(':b', $end_date);
        $exec = $result->execute();
        if ($exec) {
             $data = $result->fetchAll();
                }
        }
}
echo "Total Profit From Software Sales: " . '&#163;' . $row['SUM(profit)'] . "";
?>
Now let's stop and think about what you're trying to do. You're already getting all the relevant rows, you're already iterating over them, all that's missing is the sum. No need to perform a separate lookup just for that. Instead, let PHP handle the addition as you traverse the rows you've already pulled.

Re: SUM profit issue

Posted: Thu Feb 04, 2016 5:15 pm
by Celauran
You could in theory add it to the query. However, since you're retrieving the profit column anyway, you could also just have PHP calculate the sum.

Re: SUM profit issue

Posted: Fri Feb 05, 2016 6:13 am
by Celauran
It's not a row being returned from the DB. You won't find it that way. $row['profit'], however, exists for every entry. Why not just add them up?

Re: SUM profit issue

Posted: Fri Feb 05, 2016 6:21 am
by Celauran

Code: Select all

<?php

ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);

session_start();
if ($_SESSION['user']=='') {
    header("Location:../index.php");
} else {
    include("../config.php");
    $sql = $dbh->prepare("SELECT * FROM users WHERE id=?");
    $exec = $sql->execute(array($_SESSION['user']));
    $user = $exec ? $sql->fetch() : null;
}

include('connect.php');
$data = []; // Data starts as an empty array
$profit = 0; // Profit starts at zero
if (isset($_GET['d1']) && isset($_GET['d2'])) {
    $d1 = strtotime($_GET['d1']);
    $d2 = strtotime($_GET['d2']);
    if ($d1 && $d2) {
        $start_date = date('Y-m-d', $d1);
        $end_date = date('Y-m-d', $d2);
        $result = $db->prepare("SELECT * FROM purchased_software WHERE sales_month BETWEEN :a AND :b");
        $result->bindParam(':a', $start_date);
        $result->bindParam(':b', $end_date);
        $exec = $result->execute();
        if ($exec) {
             $data = $result->fetchAll();
        }
    }
}

?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
    <head>
        <title>Search Data Between Two Dates</title>
        <link rel="stylesheet" type="text/css" media="screen" href="css/styles.css" />
        <link rel="stylesheet" type="text/css" href="css/tcal.css" />
        <script type="text/javascript" src="js/tcal.js"></script>
    </head>  

    <body>
        <div id="logo">
            <img src="images/logo/it-done-right.jpg" alt="" title="">
        </div>

        <script>
            var t;
            window.onload=resetTimer;
            document.onkeypress=resetTimer;

            function logout()
            {
                alert("You are now logged out.");
                location.href='../logout.php';
            }
            function resetTimer()
            {
                clearTimeout(t);
                t=setTimeout(logout,1800000) //logs out in 30 minutes
            }
        </script>

        <?php if ($user): ?>
            <div class='home-content'>
                <center>
                    <h2>Hello, <?= $user['username']; ?></h2>
                    <a href='../logout.php'>Log Out</a>
                    <br><br>
                    <a href='../index.php'>Home</a>
                </center>
            </div>
            <br>
        <?php endif; ?>

        <?php include("nav-menu.php"); ?>

        <br>

        <form action="search-data.php" method="get">
            From : <input type="text" name="d1" class="tcal" value="" />
            To: <input type="text" name="d2" class="tcal" value="" />
            <input type="submit" value="Search">
        </form>

        <?php if (!empty($data)): ?>
        <table class="view-repairs">
            <thead>
                <tr>
                    <th>Software ID</th>
                    <th>Customer PayPal Email</th>
                    <th>Sales Date</th>
                    <th>Software Title</th>
                    <th>Quantity</th>
                    <th>Total Sale</th>
                    <th>Ebay Fees</th>
                    <th>PayPal Fees</th>
                    <th>Cost Price</th>
                    <th>Profit</th>
                    <th>Notes</th>
                    <th>Actions</th>
                </tr>
            </thead>
            <tbody>
            <?php foreach ($data as $row): ?>
                <?php $profit += $row['profit']; // Take the profit from this row, add it to the total ?>
                <tr>
                   <td><a href="view-specific-software-sale.php?id=<?= $row['id']; ?>"><?php echo $row['id']; ?></a></td>
                   <td><?php echo $row['customer_pp_email']; ?></td>
                   <td><?php echo date("d/m/Y", strtotime($row['sales_month'])); ?></td>
                   <td><?php echo $row['software_title']; ?></td>
                   <td><?php echo $row['quantity']; ?></td>
                   <td><?php echo $row['total_sale']; ?></td>
                   <td><?php echo $row['ebay_fees']; ?></td>
                   <td><?php echo $row['paypal_fees']; ?></td>
                   <td><?php echo '&#163;' . $row['software_cost']; ?></td>
                   <td><?php echo '&#163;' . $row['profit']; ?></td>
                   <td><?php echo substr($row['notes'], 0, 25); ?></td>
                   <td><a href="add-update-software-sales.php?id=<?= $row['id']; ?>">Edit</a></td>
                </tr>
            <?php endforeach; ?>
            </tbody>
        </table>
       
        <div class="box-right">
            <div class="box-middle-content">
                £<?= $profit; ?>
            </div>
        </div>
        <?php endif; ?>
       
        <a href="view-software-sales.php">BACK TO VIEW ALL SOFTWARE SALES</a>
       
    </body>
</html>

Re: SUM profit issue

Posted: Fri Feb 05, 2016 6:24 am
by Celauran
ianhaney wrote:is there a way to get the selected dates displayed in the box so for example it says Total Profit for dates 1/2/2016 to 29/2/2016 £amount
You're already got $start_date and $end_date as YYYY-MM-DD dates, and $d1 and $d2 as timestamps. You can simply format either of those.