tommahh

Thanks!  :)

/bump I've been reading up on JOIN trying to make sense of this and can't seem to grasp it. If somebody understands what is going on here, could they explain in terms that I might understand? It's probably easier than I am making it out to be, it's just not clicking at the moment. Thanks for reading :)

I tried them and didn't get too far. Either error msgs or no output at all was the result. As I am fairly new to this, I think it was more my not understanding then his solution not being accurate.

Here is a dump from phpMyAdmin (it's what I use to create and edit) [color=red] -- -- Table structure for table `my_inv` -- CREATE TABLE `my_inv` (   `fb_id` int(11) NOT NULL auto_increment,   `fb_type` varchar(100) collate latin1_bin NOT NULL default '',   `fb_color` varchar(100) collate latin1_bin NOT NULL default '',   `date_in` date NOT NULL default '0000-00-00',   `date_out` date NOT NULL default '0000-00-00',   `negotiable` varchar(100) collate latin1_bin NOT NULL default '',   `carton_num` varchar(100) collate latin1_bin NOT NULL default '',   `yards` decimal(9,1) NOT NULL default '0.0',   `release_num` varchar(100) collate latin1_bin NOT NULL default '',   `po_num` varchar(100) collate latin1_bin NOT NULL default '',   `invoice_num` varchar(100) collate latin1_bin NOT NULL default '',   `customer` varchar(100) collate latin1_bin NOT NULL default '',   `warehouse` varchar(100) collate latin1_bin NOT NULL default '',   `in_transit` tinyint(1) NOT NULL default '0',   `comments` varchar(100) collate latin1_bin NOT NULL default '',   PRIMARY KEY  (`fb_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin AUTO_INCREMENT=82005 ; [/color] The two items I would like subtracted are taken from these two queries: This one is "as stock01" [color=red] $query01 = "SELECT fb_color, fb_type, SUM(yards) as stock01             FROM  my_inv         WHERE (fb_type = \"{$fb_type}\"         AND fb_color = \"{$fb_color}\"         AND date_in <= \"{$end_date01}\"         AND date_out > \"{$end_date01}\"         AND in_transit < 1)         OR         (fb_type = \"{$fb_type}\"         AND fb_color = \"{$fb_color}\"         AND date_in <= \"{$end_date01}\"         AND date_out = \"0000-00-00\"         AND in_transit < 1)         OR         (fb_type = \"{$fb_type}\"         AND fb_color = \"{$fb_color}\"         AND date_in <= \"{$end_date01}\"         AND date_out = \"{$end_date01}\"         AND in_transit < 1)         GROUP BY fb_type, fb_color"; [/color] This one is "as stock02" [color=red] $query02 = "SELECT fb_color, fb_type, SUM(yards) as stock02             FROM  my_inv         WHERE (fb_type = \"{$fb_type}\"         AND fb_color = \"{$fb_color}\"         AND date_in <= \"{$end_date02}\"         AND date_out > \"{$end_date02}\"         AND in_transit < 1)         OR         (fb_type = \"{$fb_type}\"         AND fb_color = \"{$fb_color}\"         AND date_in <= \"{$end_date02}\"         AND date_out = \"0000-00-00\"         AND in_transit < 1)         OR         (fb_type = \"{$fb_type}\"         AND fb_color = \"{$fb_color}\"         AND date_in <= \"{$end_date02}\"         AND date_out = \"{$end_date02}\"         AND in_transit < 1)         GROUP BY fb_type, fb_color         ORDER BY fb_type, fb_color ASC"; [/color] They are basically the same query, just a different $end_date varible given by the user (it's the two dates they want to compare). I am able to display the stock levels on both $end_date01 (which is shown by stock01) and $end_date02 (which is shown by stock02). What I would like to do next is show the difference between them (which would be subtracting stock02 from stock01). The code for the entire page (except for the db.inc which has the db login information in it) is in my pervious post so you can see how it is all strung together. Thanks for looking into this for me.

Sure thing. I am pulling two different sets of information (stock levels on one date and stock levels on another date). That part I have accomplished. The next part (where I am stuck) is subtracting one from the other (stock01 - stock02) and printing it to the screen to find the difference.

Still trying to figure this one out, I'm stumped at the moment.

Thank you for your reply. I took a few days off from this problem as it was frustrating me greatly. I am a bit new to this, so I am kind of unsure what you meant by your possible solution. Below is the code for the entire page (with all my comments that help me understand just what is what), maybe it may help you to help me understand how I am supposed to use the information you supplied (or anybody else who would like to weigh in as well). Sorry for the inconvience, I'm a bit new to this : / [color=red] <? require "../db.inc"; ?> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>test report</title> <link rel="STYLESHEET" type="text/css" href="../_s/output.css"> </head> <body> <?   // ... then print out a header   print "<div class=\"header\"><strong>Report:</strong> Difference in Total yards of $fb_color $fb_type between $end_date01 and $end_date02</div> \n <div class=\"main\">"; // show all information in a <table>   function display_list01($connection, $query01, $end_date01)   {     // run the query     if (!($result = @ mysql_query ($query01, $connection)))         showerror();     // find out how many rows are available     $rowsFound = @ mysql_num_rows($result);     // if the query has results...     if ($rowsFound > 0)     { // and start a <table>         print "\n<table>\n<tr>" .   "\n\t<th>Fabric Type</th>" .               "\n\t<th>Fabric Color</th>" .               "\n\t<th>Starting Yards</th>" .               "\n\t<th>Ending Yards</th>" .   "\n\t<th>Difference</th>\n</tr>"; // fetch each of the query rows         while ($row = @ mysql_fetch_array($result))         { // print one row of results (this is a while loop)             print "\n<tr>\n\t\n\t<td>{$row["fb_type"]}&nbsp;</td>" .               "\n\t<td>{$row["fb_color"]}&nbsp;</td>" .               "\n\t<td>{$row["stock01"]}&nbsp;</td>\n";         } // end while loop     } // end if $rowsFound body   } // end of function // ########### this is what outputs the data ########### // // connect to the db server   if (!($connection = @ mysql_connect($hostname, $username, $password)))     die("Could not connect"); // secure the user parameter $end_date   $end_date01 = mysqlclean($_GET, "end_date01", 30, $connection);   $fb_type = mysqlclean($_GET, "fb_type", 30, $connection);   $fb_color = mysqlclean($_GET, "fb_color", 30, $connection);   if (!mysql_select_db($databaseName, $connection))     showerror(); // start a query ...   $query01 = "SELECT fb_color, fb_type, SUM(yards) as stock01             FROM  my_inv WHERE (fb_type = \"{$fb_type}\" AND fb_color = \"{$fb_color}\" AND date_in <= \"{$end_date01}\" AND date_out > \"{$end_date01}\" AND in_transit < 1) OR (fb_type = \"{$fb_type}\" AND fb_color = \"{$fb_color}\" AND date_in <= \"{$end_date01}\" AND date_out = \"0000-00-00\" AND in_transit < 1) OR (fb_type = \"{$fb_type}\" AND fb_color = \"{$fb_color}\" AND date_in <= \"{$end_date01}\" AND date_out = \"{$end_date01}\" AND in_transit < 1) GROUP BY fb_type, fb_color"; // run the query and show the results   display_list01($connection, $query01, $fb_color, $fb_type, $end_date01); ?> <? // query02 continuing in the new <table>   function display_list02($connection, $query02, $end_date02)   {     // run the query     if (!($result = @ mysql_query ($query02, $connection)))         showerror();     // find out how many rows are available     $rowsFound = @ mysql_num_rows($result);     // if the query has results...     if ($rowsFound > 0)     { // fetch each of the query rows         while ($row = @ mysql_fetch_array($result))         { // print one row of results (this is a while loop)             print "\n\t<td>{$row["stock02"]}&nbsp;</td>\n" .   "\n\t<td>bleh&nbsp;</td>\n</tr>";         } // end while loop         // finish the <table>         print "\n</table>";     } // end if $rowsFound body     // bottom navigation stuff     print "<br /><strong>Additional Navigation</strong>:<br /> <img src=\"../_m/icon_info.gif\" width=\"11\" height=\"11\" border=\"0\" />&nbsp;<a href=\"../index.php\">Back to Company Reports</a><br /><br /> <img src=\"../_m/icon_info.gif\" width=\"11\" height=\"11\" border=\"0\" />&nbsp;<a href=\"../index2.php\">Back to Customer Reports</a><br />";   } // end of function // ########### this is what outputs the data ########### // // connect to the db server   if (!($connection = @ mysql_connect($hostname, $username, $password)))     die("Could not connect"); // secure the user parameter $end_date   $end_date02 = mysqlclean($_GET, "end_date02", 30, $connection);   $fb_type = mysqlclean($_GET, "fb_type", 30, $connection);   $fb_color = mysqlclean($_GET, "fb_color", 30, $connection);   if (!mysql_select_db($databaseName, $connection))     showerror();   $query02 = "SELECT fb_color, fb_type, SUM(yards) as stock02             FROM  my_inv WHERE (fb_type = \"{$fb_type}\" AND fb_color = \"{$fb_color}\" AND date_in <= \"{$end_date02}\" AND date_out > \"{$end_date02}\" AND in_transit < 1) OR (fb_type = \"{$fb_type}\" AND fb_color = \"{$fb_color}\" AND date_in <= \"{$end_date02}\" AND date_out = \"0000-00-00\" AND in_transit < 1) OR (fb_type = \"{$fb_type}\" AND fb_color = \"{$fb_color}\" AND date_in <= \"{$end_date02}\" AND date_out = \"{$end_date02}\" AND in_transit < 1) GROUP BY fb_type, fb_color ORDER BY fb_type, fb_color ASC"; // run the query and show the results   display_list02($connection, $query02, $fb_color, $fb_type, $end_date02); ?> </div> </body> </html> [/color] As shown, this prints the following: fb_type | fb_color | stock01 | stock02 | bleh Thanks for your help with this. I am trying to understand!

Hello, I have been trying to figure out a way to subtract two numbers from each other (numbers created by using SUM), but can't seem to get it. I've tried looking in the mysql docs and each different search I do seems to land me at this page: http://dev.mysql.com/doc/refman/4.1/en/arithmetic-functions.html The user is inputting two dates ($end_date01 and $end_date02) to check inventory levels of a certain item on that date. They are also selecting $fb_type and $fb_color from a select form. I am able to pull both numbers in different queries, but where I am stuck is subtracting the first from the second. This is the code I am using to pull both of the starting numbers: [color=red]   $query01 = "SELECT fb_color, fb_type, SUM(yards) as stock01             FROM  my_inv WHERE (fb_type = \"{$fb_type}\" AND fb_color = \"{$fb_color}\" AND date_in <= \"{$end_date01}\" AND date_out > \"{$end_date01}\" AND in_transit < 1) OR (fb_type = \"{$fb_type}\" AND fb_color = \"{$fb_color}\" AND date_in <= \"{$end_date01}\" AND date_out = \"0000-00-00\" AND in_transit < 1) OR (fb_type = \"{$fb_type}\" AND fb_color = \"{$fb_color}\" AND date_in <= \"{$end_date01}\" AND date_out = \"{$end_date01}\" AND in_transit < 1) GROUP BY fb_type, fb_color";   $query02 = "SELECT fb_color, fb_type, SUM(yards) as stock02             FROM  my_inv WHERE (fb_type = \"{$fb_type}\" AND fb_color = \"{$fb_color}\" AND date_in <= \"{$end_date02}\" AND date_out > \"{$end_date02}\" AND in_transit < 1) OR (fb_type = \"{$fb_type}\" AND fb_color = \"{$fb_color}\" AND date_in <= \"{$end_date02}\" AND date_out = \"0000-00-00\" AND in_transit < 1) OR (fb_type = \"{$fb_type}\" AND fb_color = \"{$fb_color}\" AND date_in <= \"{$end_date02}\" AND date_out = \"{$end_date02}\" AND in_transit < 1) GROUP BY fb_type, fb_color"; [/color] When I output this, I get the name followed by a column showing stock1 and a column showing stock2. Ideally, I would like to then do "stock2 - stock1" and output that in the next column....which is where I am stuck. Any suggestions?