I have the following code to search the stores by name and and order by transaction date by Asc which works perfect.
My questions is how to performe a subtraction by row (checkamount-redeposited-replacement-paidcash = balance) and totalize by columns (total checkamount, total redeposited, total replacement, total paidcash and total balance) by store name and by all stores using a drop down combo box.
See an exemplo:

Bounced checks report!
From Main Office 2012-01-01 To 2012-09-20
Check ID Transaction Date Store Name Check Date Date Cashed Date Return Check Number Maker Name Payee Name Bank Name Check Amount Return Reason Date Solved Redeposited Replacement Paid Cash Balance Disposition
16 9/6/2012 Main Office 9/6/2012 9/6/2012 9/6/2012 123 Maker Payee Bank 350.55 NSF 9/6/2012 100.00 10.00 10.00 230.55 none
63 9/6/2012 Main Office 9/2/2012 9/6/2012 9/6/2012 1531 House Renovations Co. Luis Tapioca Bank 1,600.00 Stop PAyment - - - 1,600.00
38 9/15/2012 Main Office 9/2/2012 9/2/2012 9/6/2012 1531 LEE Van Renovations LLC Luis Tapioca Bank 2,000.00 Stop Payment 9/15/2012 - - 800.00 1,200.00 partial payment
39 9/15/2012 Main Office 9/2/2012 9/2/2012 9/6/2012 1531 All Renovations LLC Luis Tapioca Bank 1,500.00 Stop Payment 9/15/2012 - - 800.00 700.00 partial payment
40 9/15/2012 Main Office 9/2/2012 9/2/2012 9/6/2012 1531 Great Renovations LLC Luis Tapioca Bank 1,300.00 Stop Payment 9/15/2012 - - 800.00 500.00 partial payment
64 9/15/2012 Main Office 9/2/2012 9/2/2012 9/6/2012 1531 Last Renovations Co. Luis Tapioca Bank 2,300.00 Stop PAyment 9/15/2012 - - 800.00 1,500.00
21 9/20/2012 Main Office 9/20/2012 9/20/2012 9/20/2012 22222 Maker Name Payee Name Bank 123.00 Reason 1/12/2012 123.00 123.00 123.00 (246.00) 123
22 9/20/2012 Main Office 9/20/2012 9/20/2012 9/20/2012 22222 Maker Name Payee Name Bank 123.00 Reason 1/12/2012 123.00 123.00 123.00 (246.00) test
23 9/20/2012 Main Office 9/20/2012 9/20/2012 9/20/2012 22222 Maker Name Payee Name Bank 123.00 Reason 1/12/2012 123.00 123.00 123.00 (246.00) test
Totals 9,419.55 469.00 379.00 3,579.00 4,992.55

...

<?php 
include("db.php"); //this connection opens database for the stores names drop down

if(!isset($_POST['find'])) 
{ 
?>

<body bgcolor="#E6E6FA"> 
<form method = "post" action = "<?php echo $_SERVER['PHP_SELF'];?>"> 
<table width = "600" align = "center"> 
<tr> 
<td><b><i>Please enter in the fields below Store Name and Date - smaller date first - (yyyy-mm-dd)</i></b></td> 
</tr> 
<tr> 
<td>

<select name="storename"> 
 <?php $result= mysql_query('SELECT * FROM stores_names'); ?> 
<?php while($row= mysql_fetch_assoc($result)) { ?> 
<option value="<?php echo htmlspecialchars($row['stores']);?>"> 
<?php echo htmlspecialchars($row['stores']); ?> 
</option> 
<?php } ?> 
</select>



    From <input type = "text" name = "small">
    To <input type = "text" name = "large"></td>
</tr>
<tr>
    <td align = "center">
        <input type = "submit" name = "find" value = "SEARCH">
        <input type = "reset" value = "CLEAR FORM">
    </td>    
</tr>
</table> 
</form> 
<?php 
} 
else 
{ 
$storename = trim($_POST['storename']); 
$small = trim($_POST['small']); 
$large = trim($_POST['large']);

//$connection = mysql_pconnect("localhost", "root", "") or die("Connection failed. ".myslq_error());
//mysql_select_db("books") or die("Unable to select db. ".mysql_error());
//Add 1 to the upper range, $large, else it won't make it inclusive
$query = "SELECT * FROM books WHERE transactiondate BETWEEN DATE_FORMAT(transactiondate,'%".$small."') AND DATE_FORMAT(transactiondate, '%".($large)."') AND storename LIKE '$storename' ORDER BY transactiondate ASC";

//order by creationdate, creationtime desc;
$result = mysql_query($query) or die(mysql_error());
//echo "<span>Not logged in</span>" ; 
echo "Bounced checks report!</br>";
echo "From", " ", $storename," ", $small, " To ", $large; 

echo "<table width = '1000' align = 'center' border ='1'>";
echo "<tr><b>";
    echo "<td>Check ID</td>";
    echo "<td>Transaction Date</td>";
    echo "<td>Store Name</td>";
    echo "<td>Check Date</td>";
    echo "<td>Date Cashed</td>";
    echo "<td>Date Return</td>";
    echo "<td>Check Number</td>";
    echo "<td>Maker Name</td>";
    echo "<td>Payee Name</td>";
    echo "<td>Bank Name</td>";
    echo "<td>Check Amount</td>";
    echo "<td>Return Reason</td>";
    echo "<td>Date Solved</td>";
    echo "<td>Redeposited</td>";
    echo "<td>Replacement</td>";
    echo "<td>Paid Cash</td>";
    echo "<td>Balance</td>";
    echo "<td>Disposition</td>";

echo "</b></tr>";
while($record = mysql_fetch_object($result))
{
    echo "<tr>";
        echo "<td>".$record->BookID."</td>";
        $year_part_of_date = explode('-', $record->transactiondate);
        //echo "<td>".$year_part_of_date[0]."</td>";
        echo "<td>".$record->transactiondate."</td>";
        //if you want the full date replace the $year_part_of_date[0] with $record->date
        echo "<td>".$record->storename."</td>";
        echo "<td>".$record->checkdate."</td>";
        echo "<td>".$record->datecashed."</td>";
        echo "<td>".$record->datereturn."</td>";
        echo "<td>".$record->checknumber."</td>";
        echo "<td>".$record->makername."</td>";
        echo "<td>".$record->payeename."</td>";
        echo "<td>".$record->bankname."</td>";
        echo "<td>".$record->checkamount."</td>";
        echo "<td>".$record->returnreason."</td>";
        echo "<td>".$record->datesolved."</td>";
        echo "<td>".$record->redeposited."</td>";
        echo "<td>".$record->replacement."</td>";
        echo "<td>".$record->paidcash."</td>";
        echo "<td>".$record->balance."</td>";
        echo "<td>".$record->disposition."</td>";

    echo "</tr>";
}
echo "</table>";
} 
?>

Recommended Answers

All 14 Replies

Member Avatar for LastMitch

@tonyfonseca

My questions is how to performe a subtraction by row (checkamount-redeposited-replacement-paidcash = balance) and totalize by columns (total checkamount, total redeposited, total replacement, total paidcash and total balance) by store name and by all stores using a drop down combo box.

This is surreal. The question is you asking is a lot of work! What e-commerce platform are you using? I think it's gonna be a bit hard for anymore to help you with this because there's a lot writing new code involved. You have to know that Daniweb is for educational purpose to learn and it's not work related like this.

Please, I just need to know how to subtract the rows horizontal and how to add the columns vertically after searching an item.The searching function is already working i only need to subtract the rows and sum the columns by store name and between start and end date.
Your help is very appreciated. Thanks

Member Avatar for LastMitch

@tonyfonseca

Please, I just need to know how to subtract the rows horizontal and how to add the columns vertically after searching an item.The searching function is already working i only need to subtract the rows and sum the columns by store name and between start and end date.Your help is very appreciated. Thanks

You didn't provide the corrected code? I don't see any query at related to your problem?

You need a JOIN to do this.

Read more about JOIN here:

http://www.keithjbrown.co.uk/vworks/mysql/mysql_p5.php

This is how you add the column from each table:

select SUM(column) AS Total FROM table_name where ##=''

This is how you subtract the rows from each table

select id, 1column, 
coalesce(1column - (select 2column from books where id = id + 1), 1column) as diff
from books 

Then you need a JOIN

SELECT
  COALESCE() AS , - COALESCE() AS Subtracted Quantity
FROM (
    SELECT id, SUM(Quantity) AS Total InvQuantity
    FROM inventory
    GROUP BY id,
  ) inv
  LEFT JOIN (
    SELECT id, SUM(Quantity) AS TotalShipQuantity
    FROM inventory
    GROUP BY id, 
  ) ship ON 
      AND 

This is as much I can help you with this and I don't think I can help you anymore.

Member Avatar for diafol

Could you please state which columns you need to sum. Your description is a bit vague. Also when you say subtract rows, do you mean filter the dataset to only return certain rows as in using the WHERE (or HAVING) clause or like your first entry - the calculated field - balance? If the latter, I can't see why a calculated field is actually, physically in the table. There's a problem with this approach as a change to one of the dependent fields needs to update the balance field. You could build a TRIGGER for this, but it seems IMO a bit convoluted. The way to SUM fields will be part of the GROUP BY syntax.

//EDIT - ignore this. I just re-read the post - I think I get it. WIll come back.

//EDIT 2

I tried to create a csv from the data, but it's all over the place. It will take too much formatting. Sorry.

$query = "SELECT * FROM books Sum(checkamount - redeposited - replacement - paidcash) AS total, sum(checkamount), sum(redeposited), sum(replacement), sum(paidcash), sum(balance) WHERE transactiondate BETWEEN DATE_FORMAT(transactiondate,'%".$small."') AND DATE_FORMAT(transactiondate, '%".($large)."') AND storename LIKE '$storename' ORDER BY transactiondate ASC";

I think here is where i have to fix the code. First i want to calculate the balance for each row like (balance = checkamount-redeposited-replacement-paidcash) then i want to sum all those columns at the end of table.
let me see if i can attach a sample of report i want to get.

Thanks a lot for your help

ReportForm

Member Avatar for LastMitch

@tonyfonseca

I think here is where i have to fix the code. First i want to calculate the balance for each row like (balance = checkamount-redeposited-replacement-paidcash) then i want to sum all those columns at the end of table.

I don't understand your query at all. But the image does show the table.

The image looks more like a Excel sheet than a database table.

This is very complicated you might need a JOIN in order to do this.

This is how you add:

SUM(checkamount + redeposited + replacement + paidcash)

This is how you subtract:

Update your table set balance = checkamount - redeposited - replacement - paidcash

To me this is very complicated. Plus it's getting late.

Member Avatar for diafol

OK, I see. SIngle calculated field and just a row of totals.
Possibly you could use 'WITH ROLLUP' syntax. I'll have to think about that.

Please i appreciate if you could help me with that. I did a lot of researches and i couldn’t find anything like that.
Thanks for your help
Member Avatar for LastMitch

@tonyfonseca

Please i appreciate if you could help me with that. I did a lot of researches and i couldn’t find anything like that.

I'm not sure what are you asking? I gave you some examples on how to approached it. Diafol also gave you some instructions on how to approach this differently.

Here is a couple of links of what Diafol is talking about:

Read this

http://databases.about.com/od/sql/l/aacuberollup.htm

and this:

http://www.bidn.com/blogs/marcoadf/bidn-blog/383/group-by-with-rollup

and this:

http://phpmysqltipoftheday.blogspot.com/2009/04/mysql-with-rollup.html

This is all that anyone can help you with this.

You have to understand if this was a simple e-commerrece question then maybe someone can help you.

No integrated shopping cart or anything do with payment method because no one will help you with that because you are getting pay to do!

This is very complicated issue plus it's a lot of work. The only thing I can tell you that if you need this thing to be fixed then you have to pay someone to do this.

Member Avatar for diafol

If you could post an sql dump file of your table, that would enable us to fiddle with it. Trying to build one in order to test is a pain.

Thanks for your help. 
I am not geting paid for this work. This is a program that i am doing to facilitate my job, 
because i use excel for that.
If i use a program like that my job will be easier.
Attached is a snapshot of the tables.
Thanks again for your help.

![![Click Here](/attachments/large/2/stores_table.jpg "stores_table")](/attachments/large/2/books_table.jpg "books_table")

Member Avatar for LastMitch

@tonyfonseca

When diafol mention this:

If you could post an sql dump file of your table, that would enable us to fiddle with it. Trying to build one in order to test is a pain.

He meant for you to do this:

http://www.java2s.com/Tutorial/MySQL/0520__MySQL-Utilities/SelectintoaDUMPFILE.htm

or try this:

http://www.tutorialspoint.com/mysql/mysql-database-export.htm

or try this:

https://www.servage.net/wiki/Create_MySQL_dumps_with_PHP

Just used any of the link. No more images! Just post the code.

Sorry i didn't understand you at the begining how to dump the files.
Here i have a copy of the files.
Thank you very much for your help.



-- phpMyAdmin SQL Dump
-- version 3.3.9
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 25, 2012 at 12:19 AM
-- Server version: 5.5.8
-- PHP Version: 5.3.5

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `books`
--

-- --------------------------------------------------------

--
-- Table structure for table `books`
--

CREATE TABLE IF NOT EXISTS `books` (
  `BookID` int(11) NOT NULL AUTO_INCREMENT,
  `transactiondate` date NOT NULL,
  `storename` varchar(150) NOT NULL,
  `checkdate` date NOT NULL,
  `datecashed` date NOT NULL,
  `datereturn` date NOT NULL,
  `checknumber` varchar(150) NOT NULL,
  `makername` varchar(150) NOT NULL,
  `payeename` varchar(150) NOT NULL,
  `bankname` varchar(150) NOT NULL,
  `checkamount` decimal(18,2) DEFAULT NULL,
  `returnreason` varchar(150) NOT NULL,
  `datesolved` date NOT NULL,
  `redeposited` decimal(18,2) DEFAULT NULL,
  `replacement` decimal(18,2) DEFAULT NULL,
  `paidcash` decimal(18,2) DEFAULT NULL,
  `balance` decimal(18,2) DEFAULT NULL,
  `disposition` varchar(200) NOT NULL,
  PRIMARY KEY (`BookID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=27269 ;

--
-- Dumping data for table `books`
--

INSERT INTO `books` (`BookID`, `transactiondate`, `storename`, `checkdate`, `datecashed`, `datereturn`, `checknumber`, `makername`, `payeename`, `bankname`, `checkamount`, `returnreason`, `datesolved`, `redeposited`, `replacement`, `paidcash`, `balance`, `disposition`) VALUES
(16, '2012-09-06', 'Main Office', '2012-09-06', '2012-09-06', '2012-09-06', '123', 'Maker', 'Payee', 'Bank', '101.00', 'NSF', '2012-09-06', '100.00', '10.00', '10.00', '11.00', 'none'),
(17, '2012-09-14', 'Brunswick', '2012-09-14', '2012-09-14', '2012-09-14', '1234567', 'Machine Builder', 'Frank Jones', 'Chase Bank', '123.00', 'Stop Payment', '2012-09-14', '12.00', '10.00', '10.00', '0.00', 'none'),
(18, '2012-01-12', 'Harrison', '2012-01-12', '2012-01-12', '2012-01-12', '22222', 'Taxi Company', 'Mark Moreira', 'TD Bank', '1000.00', 'NSF', '2012-01-12', '200.00', '12.00', '12.00', '144.00', 'none'),
(19, '2012-01-12', 'Harrison', '2012-01-12', '2012-01-12', '2012-01-12', '22222', 'Taxi Company', 'Mark Moreira', 'TD Bank', '1000.00', 'NSF', '2012-01-12', '200.00', '12.00', '12.00', '144.00', 'none'),
(20, '2012-01-12', 'Somerville', '2012-01-12', '2012-01-12', '2012-01-12', '2222222222', 'Computer Software ', 'Tony Fonfo', 'Wachovia Bank', '123.00', 'NSF', '2012-01-12', '10.00', '10.00', '20.00', '33.00', 'none'),
(21, '2012-09-20', 'Main Office', '2012-09-20', '2012-09-20', '2012-09-20', '22222', 'Maker Name', 'Payee Name', 'TD Bank', '123.00', 'Reason', '2012-01-12', '123.00', '123.00', '123.00', '23.00', '123'),
(22, '2012-09-20', 'Main Office', '2012-09-20', '2012-09-20', '2012-09-20', '22222', 'Maker Name', 'Payee Name', 'TD Bank', '123.00', 'Reason', '2012-01-12', '123.00', '123.00', '123.00', '23.00', 'test'),
(23, '2012-09-20', 'Main Office', '2012-09-20', '2012-09-20', '2012-09-20', '22222', 'Maker Name', 'Payee Name', 'TD Bank', '123.00', 'Reason', '2012-01-12', '123.00', '123.00', '123.00', '23.00', 'test', '');
-- --------------------------------------------------------

--
-- Table structure for table `stores_names`
--

CREATE TABLE IF NOT EXISTS `stores_names` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stores` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=24 ;

--
-- Dumping data for table `stores_names`
--

INSERT INTO `stores_names` (`id`, `stores`) VALUES
(1, 'Main Office'),
(2, 'Madison'),
(3, 'New Brunswick'),
(4, 'Harrison'),
(5, 'East Front'),
(6, 'Dover'),
(7, 'Somerville'),
(8, 'West Front'),
(9, 'Somerset'),
(10, 'Broadway'),
(11, 'Elizabeth'),
(12, 'Livingston'),
(13, 'Veronica'),
(14, 'Lakewood'),
(15, 'Bayville'),
(16, 'Forked River'),
(17, 'Lucky'),
(18, 'Terefern'),
(19, 'Manville'),
(20, 'Terrill'),
(21, 'Union City'),
(22, 'Continental'),
(23, 'All Stores');
Hi everybody thanks for your precious help but i have got the code working already.
Somebody helped me to fix it.
As soon as i have a chance i will post it in case somebody needs it
Thanks
Be a part of the DaniWeb community

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