travelkind

Yes his code did work! Thanks MrMarcus and Barand!

The column type in MYSQL is "DATE" format.

I was wondering if there was a way to have the MAX function NOT return a Date that is more than 2 days into the future (from the current day)? If there is a Date that is more than 2 days into the future I would like to return the one closest to the current day. Here is the code I have: <?php mysql_connect("local", "xxx", "xxx") or die(mysql_error()); mysql_select_db("pricelink") or die(mysql_error()); // Get a specific result from the "ft9_fuel_tax_price_lines" table $query ="SELECT ItemNumber,TableCode,Cost, MAX(`Date`) as `max_date`, MAX(`Time`) as 'max_time' FROM `ft9_fuel_tax_price_lines` GROUP BY `ItemNumber`,`TableCode`"; $result = mysql_query($query) or die(mysql_error()); echo "<table border='1'>"; echo "<tr> <th>ItemNumber</th> <th>TableCode</th> <th>Date</th> <th>Time</th> <th>Cost</th> </tr>"; // keeps getting the next row until there are no more to get while($row=mysql_fetch_array($result)) { // Print out the contents of each row into a table echo "<tr><td>"; echo $row['ItemNumber']; echo "</td><td>"; echo $row['TableCode']; echo "</td><td>"; echo $row['max_date']; echo "</td><td>"; echo $row['max_time']; echo "</td><td>"; echo $row['Cost']; echo "</td></tr>"; } echo "</table>"; ?> Any help would be appreciated. Thanks!

There is an issue with the ODBC connection so I created a local Mysql database and the code ran perfectly! I will have to look into ODBC issue but I appreciate all the help you guys gave me on this! Thanks again!

Hey guys thanks for the input but now I am getting the following Warning: Warning: odbc_exec() [function.odbc-exec]: SQL error: [ProvideX][ODBC Driver][FILEIO]Table is not accessible, SQL state S0000 in SQLExecDirect in D:\Pricing\EasyPHP-5.3.9\www\FT9 05-16-12 Night Version.php on line 15 [ProvideX][ODBC Driver][FILEIO]Table is not accessible Here is my revised code I must have missed something! <?php $conn=odbc_connect("XXX","xxx|xxx","xxx"); if (!$conn) { exit("Connection Failed: " . $conn); } // Get a specific result from the "FT9_FUEL_TAX_PRICE_LINES" table $query ="SELECT ItemNumber, MAX(`Date`) as `max_date` FROM `FT9_FUEL_TAX_PRICE_LINES` WHERE ItemNumber = 'E-10 NL GAS' AND TableCode = 'CITA' GROUP BY `ItemNumber`"; $result = odbc_exec($conn, $query) or die(odbc_errormsg()); // keeps getting the next row until there are no more to get while($row=odbc_fetch_array($result)) { // Print out result echo "The most recent date is: " .$row['max_date']; echo "<br />"; } ?>

I am getting the following error when I try to run my php script: Notice: Undefined index: MAX(Date) in D:\Pricing\EasyPHP-5.3.9\www\FT9 05-16-12.php on line 21 The most recent date is: The most recent or MAX Date should be returned after the echo statement "The most recent date is:" Here is my code: <?php $conn=odbc_connect("XXX","xxx|xxx","xxx"); if (!$conn) { exit("Connection Failed: " . $conn); } // Get a specific result from the "FT9_FUEL_TAX_PRICE_LINES" table $query =("SELECT ItemNumber,MAX(Date) FROM 'FT9_FUEL_TAX_PRICE_LINES' WHERE ItemNumber = 'E-10 NL GAS'AND TableCode = 'CITA' GROUP BY 'ItemNumber'"); $result = odbc_exec($conn, $query) or die(odbc_errormsg()); // keeps getting the next row until there are no more to get while($row=odbc_fetch_array($result)) { // Print out result echo "The most recent date is: " .$row['MAX(Date)']; echo "<br />"; } ?> Can anyone help me with this? Any help would be much appreciated.

Actually, I ended up using a create view to make it work. Here is the code hopefully it can help someone: STEP:1 ============================================================================== Create the View (v_rawdata) with the following code. 1. Here you can change Division or Invoice Date as per your needs ============================================================================== create or replace view v_rawdata as SELECT '1' as ID, Customer_Number as CUSTID, '' as Customer_Number, '' as Ship_To_Name, 'Total' as Invoice_Date, round(SUM(Ship_Quantity), 2) as Ship_Quantity, round(SUM(Sales_Extension), 2) as Sales_Extension, round(SUM(GM_Ext), 2) as GM_Ext FROM rawdata where Division='13' and Invoice_Date BETWEEN '2010-01-01' AND '2010-03-31' GROUP BY Customer_Number union all SELECT '2' as ID, Customer_Number as CUSTID, Customer_Number, Ship_To_Name, Invoice_Date, round(SUM(Ship_Quantity), 2) as Ship_Quantity, round(SUM(Sales_Extension), 2) as Sales_Extension, round(SUM(GM_Ext), 2) as GM_Ext FROM rawdata where Division='13' and Invoice_Date BETWEEN '2010-01-01' AND '2010-03-31' GROUP BY Customer_Number, Order_Month union all SELECT '3' as ID, Customer_Number as CUSTID, '' as Customer_Number, '' as Ship_To_Name, '' as Invoice_Date, '' as Ship_Quantity, '' as Sales_Extension, '' as GM_Ext FROM rawdata where Division='13' and Invoice_Date BETWEEN '2010-01-01' AND '2010-03-31' GROUP BY Customer_Number; STEP:2 ============================================================================== after created the above script use the below code to get output. ============================================================================== SELECT Customer_Number, Ship_To_Name, Invoice_Date, Ship_Quantity, Sales_Extension, GM_Ext FROM v_rawdata order by CUSTID;

It is returning a quantity total for all customers instead by each customer. I also need it to not post a month in the total SELECT summary. Here is what it is returning: 13AH01 ACE HARDWARE 1/31/2010 13116.1 27261.26 8660.74 13AH01 ACE HARDWARE 1/31/2010 2063.1 4168.78 1191.1 13AH01 ACE HARDWARE 2/28/2010 1809.5 3833.74 1195.35 13AH01 ACE HARDWARE 3/31/2010 2100.3 4115.5 1194.51 13BK01 BAGEL KING 1/31/2010 1911.1 4147.01 1486.3 13BK01 BAGEL KING 2/28/2010 1400.1 3168.6 1154.44 13BK01 BAGEL KING 3/31/2010 1868 4081.42 1494.99 13BOB01 BEEF O' BRADYS 1/31/2010 681.4 1274.39 297.02 13BOB01 BEEF O' BRADYS 2/28/2010 632.9 1243.53 322.9 13BOB01 BEEF O' BRADYS 3/31/2010 649.7 1228.29 324.13 Here is what I would like for it to return: Totals 5972.9 12118.02 3580.96 13AH01 ACE HARDWARE 1/31/2010 2063.1 4168.78 1191.1 13AH01 ACE HARDWARE 2/28/2010 1809.5 3833.74 1195.35 13AH01 ACE HARDWARE 3/31/2010 2100.3 4115.5 1194.51 Totals 5179.2 11397.03 4135.73 13BK01 BAGEL KING 1/31/2010 1911.1 4147.01 1486.3 13BK01 BAGEL KING 2/28/2010 1400.1 3168.6 1154.44 13BK01 BAGEL KING 3/31/2010 1868 4081.42 1494.99 Totals 1964 3746.21 944.05 13BOB01 BEEF O' BRADYS 1/31/2010 681.4 1274.39 297.02 13BOB01 BEEF O' BRADYS 2/28/2010 632.9 1243.53 322.9 13BOB01 BEEF O' BRADYS 3/31/2010 649.7 1228.29 324.13

Thanks for the response. I have changed my criteria and what I am looking for now is a total for each customer. I have done some research and it looks like I need to use a UNION ALL query and have one SELECT be the details and the other SELECT be the summary. I am still trying to figure out how to make this work correctly. Any ideas? Here is my code: SELECT * FROM ( SELECT rawdata.Customer_Number AS "Customer Number", rawdata.Ship_To_Name AS "Ship to Name", rawdata.Order_Month AS "Order Month Ended", SUM(rawdata.Ship_Quantity) AS "Quantity Ordered", SUM(rawdata.Sales_Extension) AS "Sales", SUM(rawdata.GM_Ext) AS "Gross Profit" FROM rawdata WHERE rawdata.Division = '13' AND rawdata.Invoice_Date BETWEEN '2010-01-01' AND '2010-03-31' UNION ALL SELECT rawdata.Customer_Number AS "Customer Number", rawdata.Ship_To_Name AS "Ship to Name", rawdata.Order_Month AS "Order Month Ended", SUM(rawdata.Ship_Quantity) AS "Quantity Ordered", SUM(rawdata.Sales_Extension) AS "Sales", SUM(rawdata.GM_Ext) AS "Gross Profit" FROM rawdata WHERE rawdata.Division = '13' AND rawdata.Invoice_Date BETWEEN '2010-01-01' AND '2010-03-31' GROUP BY rawdata.Customer_Number, Order_Month ) AS dt ORDER BY "Customer Number" ASC, "Quantity Ordered" ASC

Hello everyone. I need to know how to add a space after each customer. I have a field called Customer_Number and need to have a space after all results for a specific customer are returned. Here is my code. Thanks for your help! SELECT rawdata.Customer_Number, rawdata.Ship_To_Name, rawdata.Order_Month, SUM(rawdata.Ship_Quantity), SUM(rawdata.Sales_Extension) FROM rawdata WHERE rawdata.Division = '13' AND rawdata.Invoice_Date BETWEEN '2009-01-01' AND '2010-03-31' GROUP BY rawdata.Customer_Number, Order_Month ORDER BY rawdata.Customer_Number ASC, rawdata.Invoice_Date ASC

I am having an issue with the query I have written as it seems to make my site "loop" back through the code and it keeps refreshing my site. I have ran a syntax check and that seems to be fine. Could someone give me an idea of where to look? Here is my code. Thanks for your input. <?php session_start(); require("config.php"); require("header.php"); require("background.php"); //Connect To Database $hostname='xxx'; $username='xxx'; $password='xxx'; $dbname='xxx'; mysql_connect($hostname,$username, $password) OR DIE ('Unable to connect to database! Please try again later.'); mysql_select_db($dbname); echo " ". '<br>'; $query = "SELECT Account_and_Name, YEAR(Invoice_Date) AS IYear, MONTH(Invoice_Date) AS IMonth, SUM(Ship_Quantity) AS Total, MONTHNAME(Invoice_Date) AS MName FROM rawdata WHERE Invoice_Date BETWEEN '2010-01-01' AND '2010-12-31' GROUP BY Account_and_Name, YEAR(Invoice_Date), MONTH(Invoice_Date)"; $result = mysql_query($query) or die(mysql_error()); // Print out result while($row = mysql_fetch_array($result)){ echo "Customer: " . $row['Account_and_Name']; echo "<br />"; ?> <html> <head> <style type="text/css"> table, td, th { border:1px solid black; } td { padding-top: 5px; padding-right: 5px; padding-bottom: 5px; padding-left: 5px; } </style> </head> <body> <table> <tr> <th>Month</th> <th>Gallons Sold</th> <th>Sales Dollars</th> <th>Cost of Goods Sold</th> <th>Gross Profit</th> <th>Profit Per Gallon</th> </tr> </table> </body> </html> <?php echo " ". '<br>'; require("footer.php"); } ?>

Okay I will check into CSS. Thanks Ken.

This is probably a simple question, but how can I modify my code to insert additional spacing in between my column headings? Thanks for your input. echo " ". '<br>'; echo "Customer Number:" . '<br>'; echo "Customer Name:" . '<br>'; echo "Customer Address:" . '<br>'; echo " ". '<br>'; echo " ". '<br>'; echo "Month" . " " . "Gallons" . " " . "Sales" . " " . "COGS" . " " . "Gross Profit" . " " . "Profit Per Gallon" . '<br>'; echo " ". '<br>';

Okay I see that and should have noticed it myself! Okay the error is gone! I am going to work on echoing my data fields out now. Thanks a lot for your help!

It is actually Invoice_Date not inv_date. I got the following error when trying to run it: FUNCTION "MY Database Name".MONTH_NAME does not exist Here is my code: $query = "SELECT Account_and_Name, YEAR(Invoice_Date) AS IYear, MONTH(Invoice_Date) AS IMonth, SUM(Ship_Quantity) AS Total, MONTH_NAME(Invoice_Date) AS MName FROM rawdata WHERE Invoice_Date BETWEEN '2010-01-01' AND '2010-12-31' GROUP BY Account_and_Name, YEAR(Invoice_Date), MONTH(Invoice_Date)";