Fearpig

Hello, Can anyone help me with my query? Here's my current query: SELECT TOP (100) PERCENT Area, Pacode, SUM(CurrentMonth) AS Sum_CurrentMonth FROM dbo.qry_Travis_by_Area_Summary GROUP BY Pacode, Area HAVING (Area = N'0010') OR (Area = N'0011') OR (Area = N'0019') ...and the output comes out as: Area Pacode Sum_CurrentMonth 0010 A 7 0011 A 18 0019 A 9 0010 B 6 0011 B 3 0019 B 7 and this is the output I'm aiming for: Pacode Sum_CurrentMonth A 34 B 16 Unfortunately I can't just use: SELECT TOP (100) PERCENT Area, Pacode, SUM(CurrentMonth) AS Sum_CurrentMonth FROM dbo.qry_Travis_by_Area_Summary GROUP BY Pacode Because there are Areas that I need to filter out. Can anyone explain how to use a field to filter with when it is not included in either the "group by" or the "aggregate"? Cheers.

Hi Guys, Can someone have a look at the code below for me? It all works as it is but just takes too long to run up. Can anyone see a way to simplify the process? The page displays a table of people and their jobs for the next 7 days, a bit like this... Paul: Today: Job001 Job002 Wednesday: Job003 Thursday: Job004 Job005 Job006 Stephen: Today: Job007 Job008 Wednesday: Thursday: Job009 Job010 Job011 Job012 James: Today: Job0013 Wednesday: Job0014 Thursday: Job015 Job016 Unfortunately at the moment the page runs a SELECT query for every cell of the table (the example above would use 9 queries). This isn't too bad untill I now have 26 people and I'm showing up to 7 days meaning that the one page for this table runs 182 SELECT queries!! Can anyone see a simpler method? My table is set out as: Table: Job Number Person Date Status ...and here is the code I am currently using: <?php $conn=odbc_connect('OU_PROD','ou_dba','dba'); if (!$conn) {exit("Connection Failed: " . $conn);} $sql="SELECT * FROM person WHERE person_group = 'ENGINEER' AND employee = 'Y'"; $rs=odbc_exec($conn,$sql); if (!$rs) {exit("Error in SQL");} echo "<Table cellspacing='0' cellpadding='0' border='1'><tr valign='top'>"; while (odbc_fetch_row($rs)) { $User_ID=odbc_result($rs,"user_id"); echo "<td class='Body3'>$User_ID"; //The above query sets up a table with one row and one person per cell. //The query below puts in a table of that persons jobs into the cell created above $day=0; //sets the day value back to 0 - days run 0,1,2,3,4,5,6, $startdate=date("Y/m/d"); //sets the initial date to the current date echo "<Table border=1 width=110>"; while ($day < 7) { $selectdate = date( "Ymd", mktime(0, 0, 0, date("m"), date("d")+$day, date("y")) ); //increase $startdate by the current value of $day $formatdate = date("D - d/m/Y",strtotime("$selectdate")); $sql="SELECT * FROM FER_Engineers_Jobs_ALL WHERE Date = '$selectdate' and person_id = '$User_ID'"; $result=odbc_exec($conn,$sql); if (!$result) {exit("Error in SQL");} echo "<tr bgcolor=#CCCCCC align=center class='Body3'><th><b>$formatdate</b></th></tr><tr><td>"; //echo "$day<br>"; while (odbc_fetch_row($result)) { $Request=odbc_result($result,"request_id"); $PostCode=odbc_result($result,"Postcode"); $Status=odbc_result($result,"Status"); if($Status == 'OPEN '){$Font = 'Body3Red';} else{$Font = 'Body3';} echo "<a href=Result_ByRequest.php?Request=$Request class='$Font'>$Request - $PostCode</a><br>"; } $day++; //Add one to the $day variable before repeating loop } echo "</td></tr></Table>"; echo "<td>"; } echo "</tr></table>"; odbc_close($conn); ?>

Right then..... Sorted it but incase anyone was following this here's what I did: I was getting in a real mess with this so I transferred the query to the MS SQL server (which runs it quicker anyway!) and now I can just handle all of the values as variables. Thanks to Barand (yet again!) for all his help, it would have taken years without him and a couple of days with his help! Here's my final code: <?php $sql_by_PAC = "SELECT * FROM qry_Sales_by_PAC_Code_qry2 WHERE Year = '$Year' AND Area = '$id' ORDER BY Pac1"; $result_by_PAC=odbc_exec($conn,$sql_by_PAC); if (!$result_by_PAC) {exit("Error in SQL");} echo "<Table border='0' cellpadding='4'><tr class='TitleText_White' bgcolor=#FF6600><th>Area: $id</th><th>Jan</th><th>Feb</th><th>Mar</th><th>Apr</th><th>May</th><th>June</th><th>July</th><th>Aug</th><th>Sept</th><th>Oct</th><th>Nov</th><th>Dec</th><th>Y.T.D.</th></tr>"; while (odbc_fetch_row($result_by_PAC)) { $PAC=odbc_result($result_by_PAC,"Pac1"); $jan_quantity=round(odbc_result($result_by_PAC,"jan_quantity")); $jan_price=number_format(odbc_result($result_by_PAC,"jan_price"), 2, '.', ','); $feb_quantity=round(odbc_result($result_by_PAC,"feb_quantity")); $feb_price=number_format(odbc_result($result_by_PAC,"feb_price"), 2, '.', ','); $mar_quantity=round(odbc_result($result_by_PAC,"mar_quantity")); $mar_price=number_format(odbc_result($result_by_PAC,"mar_price"), 2, '.', ','); $apr_quantity=round(odbc_result($result_by_PAC,"apr_quantity")); $apr_price=number_format(odbc_result($result_by_PAC,"apr_price"), 2, '.', ','); $may_quantity=round(odbc_result($result_by_PAC,"may_quantity")); $may_price=number_format(odbc_result($result_by_PAC,"may_price"), 2, '.', ','); $jun_quantity=round(odbc_result($result_by_PAC,"jun_quantity")); $jun_price=number_format(odbc_result($result_by_PAC,"jun_price"), 2, '.', ','); $jul_quantity=round(odbc_result($result_by_PAC,"jul_quantity")); $jul_price=number_format(odbc_result($result_by_PAC,"jul_price"), 2, '.', ','); $aug_quantity=round(odbc_result($result_by_PAC,"aug_quantity")); $aug_price=number_format(odbc_result($result_by_PAC,"aug_price"), 2, '.', ','); $sep_quantity=round(odbc_result($result_by_PAC,"sep_quantity")); $sep_price=number_format(odbc_result($result_by_PAC,"sep_price"), 2, '.', ','); $oct_quantity=round(odbc_result($result_by_PAC,"oct_quantity")); $oct_price=number_format(odbc_result($result_by_PAC,"oct_price"), 2, '.', ','); $nov_quantity=round(odbc_result($result_by_PAC,"jan_quantity")); $nov_price=number_format(odbc_result($result_by_PAC,"jan_price"), 2, '.', ','); $dec_quantity=round(odbc_result($result_by_PAC,"dec_quantity")); $dec_price=number_format(odbc_result($result_by_PAC,"dec_price"), 2, '.', ','); $YTD_quantity=round(odbc_result($result_by_PAC,"ytdqty")); $YTD_price=number_format(odbc_result($result_by_PAC,"ytdprice"), 2, '.', ','); if($PAC == 'A'){$PAC_Desc = 'Domestic';} elseif($PAC == 'B'){$PAC_Desc = 'Ancillaries';} elseif($PAC == 'N'){$PAC_Desc = 'Spares';} else{$PAC_Desc = 'Unknown';} echo "<tr bgcolor='#FFE0CC' class='Body2' align='right'> <form action='PAC_Level_2.php' method='post'><td align='center'> <input name='Year' type='hidden' value='$Year'> <input name='id' type='hidden' value='$id'> <input name='PAC' type='hidden' value='$PAC'> <input name='Submit' type='submit' value='$PAC_Desc'> </td></form> <td>$jan_quantity<br>£$jan_price</td> <td>$feb_quantity<br>£$feb_price</td> <td>$mar_quantity<br>£$mar_price</td> <td>$apr_quantity<br>£$apr_price</td> <td>$may_quantity<br>£$may_price</td> <td>$jun_quantity<br>£$jun_price</td> <td>$jul_quantity<br>£$jul_price</td> <td>$aug_quantity<br>£$aug_price</td> <td>$sep_quantity<br>£$sep_price</td> <td>$oct_quantity<br>£$oct_price</td> <td>$nov_quantity<br>£$nov_price</td> <td>$dec_quantity<br>£$dec_price</td> <td>$YTD_quantity<br>£$YTD_price</td>"; } echo "</table>"; ?>

Sorry Barand... Did you mean just replace the 'A' with 1? If so I now get the following error: Warning: vprintf() [function.vprintf]: Too few arguments in D:\Intranet v3\Sales\Individual_Area_Summary_by_PAC_Code.php on line 64 As it stood with the 'A' value the names printed out correctly but I haven't been able to reorganise the output so that the array value appears as the button name. Is there any way to declare all of the values as variables as oppose to using %s and %0.2f ?

Hi Barand, I've followed your code and got the array to print a differnet value for each row of the table but I've come up against yet another issue!! The code below all works without errors but I want the value from the array to appear as the name of the button, at the moment the value is in its own cell at the end of the row. How do I move the code around so that the array shows up as the name on the submit button? <?php $ButtonName = array ( 'A' => "Domestic", 'B' => "Ancillaries", 'N' => "Spares" ); $sql_month="SELECT Pac1, Pac1_v2, SUM(CASE month WHEN '1' THEN SumQuantity ELSE 0 END) AS jan_quantity, SUM(CASE month WHEN '1' THEN SumPrice ELSE 0 END) AS jan_price, SUM(CASE month WHEN '2' THEN SumQuantity ELSE 0 END) AS feb_quantity, SUM(CASE month WHEN '2' THEN SumPrice ELSE 0 END) AS feb_price, SUM(CASE month WHEN '3' THEN SumQuantity ELSE 0 END) AS mar_quantity, SUM(CASE month WHEN '3' THEN SumPrice ELSE 0 END) AS mar_price, SUM(CASE month WHEN '4' THEN SumQuantity ELSE 0 END) AS apr_quantity, SUM(CASE month WHEN '4' THEN SumPrice ELSE 0 END) AS apr_price, SUM(CASE month WHEN '5' THEN SumQuantity ELSE 0 END) AS may_quantity, SUM(CASE month WHEN '5' THEN SumPrice ELSE 0 END) AS may_price, SUM(CASE month WHEN '6' THEN SumQuantity ELSE 0 END) AS jun_quantity, SUM(CASE month WHEN '6' THEN SumPrice ELSE 0 END) AS jun_price, SUM(CASE month WHEN '7' THEN SumQuantity ELSE 0 END) AS jul_quantity, SUM(CASE month WHEN '7' THEN SumPrice ELSE 0 END) AS jul_price, SUM(CASE month WHEN '8' THEN SumQuantity ELSE 0 END) AS aug_quantity, SUM(CASE month WHEN '8' THEN SumPrice ELSE 0 END) AS aug_price, SUM(CASE month WHEN '9' THEN SumQuantity ELSE 0 END) AS sep_quantity, SUM(CASE month WHEN '9' THEN SumPrice ELSE 0 END) AS sep_price, SUM(CASE month WHEN '10' THEN SumQuantity ELSE 0 END) AS oct_quantity, SUM(CASE month WHEN '10' THEN SumPrice ELSE 0 END) AS oct_price, SUM(CASE month WHEN '11' THEN SumQuantity ELSE 0 END) AS nov_quantity, SUM(CASE month WHEN '11' THEN SumPrice ELSE 0 END) AS nov_price, SUM(CASE month WHEN '12' THEN SumQuantity ELSE 0 END) AS dec_quantity, SUM(CASE month WHEN '12' THEN SumPrice ELSE 0 END) AS dec_price, SUM(SumQuantity) as ytdqty, SUM(SumPrice) as ytdprice FROM qry_Sales_by_PAC_Code WHERE Year = '$Year' AND Area = '$id' GROUP BY Pac1, Pac1_v2 ORDER BY Pac1"; $result = odbc_exec($conn, $sql_month); echo "<Table border='0' cellpadding='4'><tr class='TitleText_White' bgcolor=#FF6600><th>Area: $id</th><th>Jan</th><th>Feb</th><th>Mar</th><th>Apr</th><th>May</th><th>June</th><th>July</th><th>Aug</th><th>Sept</th><th>Oct</th><th>Nov</th><th>Dec</th><th>Y.T.D.</th><th></th></tr>"; while (odbc_fetch_into($result, $data)) { $data['A'] = $ButtonName[$data[0]]; vprintf ("<tr bgcolor='#FFE0CC' class='Body2' align='right'> <form action='PAC_Level_2.php' method='post'><td align='center'> <input name='Year' type='hidden' value='$Year'> <input name='id' type='hidden' value='$id'> <input name='PAC' type='hidden' value='%s'> <input name='Submit' type='submit' value='%s'> </td></form> <td>%0.2f<br>£%0.2f</td><td>%0.2f<br>£%0.2f</td><td>%0.2f<br>£%0.2f</td><td>%0.2f<br>£%0.2f</td> <td>%0.2f<br>£%0.2f</td><td>%0.2f<br>£%0.2f</td><td>%0.2f<br>£%0.2f</td><td>%0.2f<br>£%0.2f</td> <td>%0.2f<br>£%0.2f</td><td>%0.2f<br>£%0.2f</td><td>%0.2f<br>£%0.2f</td><td>%0.2f<br>£%0.2f</td> <td>%0.2f<br>£%0.2f</td><td>%s</td></tr>", $data); } echo '</table>'; ?>

OK... you can ignore some of the last post! I've figured out what this does: $data[] = $ButtonName[$data[2]]; But all of the lines are coming back as "Domestic" rather that whatever the array should be to match the PAC Code. Here's my code so far: <?php $sql_month="SELECT Pac1, Pac1_v2, SUM(CASE month WHEN '1' THEN SumQuantity ELSE 0 END) AS jan_quantity, SUM(CASE month WHEN '1' THEN SumPrice ELSE 0 END) AS jan_price, SUM(CASE month WHEN '2' THEN SumQuantity ELSE 0 END) AS feb_quantity, SUM(CASE month WHEN '2' THEN SumPrice ELSE 0 END) AS feb_price, SUM(CASE month WHEN '3' THEN SumQuantity ELSE 0 END) AS mar_quantity, SUM(CASE month WHEN '3' THEN SumPrice ELSE 0 END) AS mar_price, SUM(CASE month WHEN '4' THEN SumQuantity ELSE 0 END) AS apr_quantity, SUM(CASE month WHEN '4' THEN SumPrice ELSE 0 END) AS apr_price, SUM(CASE month WHEN '5' THEN SumQuantity ELSE 0 END) AS may_quantity, SUM(CASE month WHEN '5' THEN SumPrice ELSE 0 END) AS may_price, SUM(CASE month WHEN '6' THEN SumQuantity ELSE 0 END) AS jun_quantity, SUM(CASE month WHEN '6' THEN SumPrice ELSE 0 END) AS jun_price, SUM(CASE month WHEN '7' THEN SumQuantity ELSE 0 END) AS jul_quantity, SUM(CASE month WHEN '7' THEN SumPrice ELSE 0 END) AS jul_price, SUM(CASE month WHEN '8' THEN SumQuantity ELSE 0 END) AS aug_quantity, SUM(CASE month WHEN '8' THEN SumPrice ELSE 0 END) AS aug_price, SUM(CASE month WHEN '9' THEN SumQuantity ELSE 0 END) AS sep_quantity, SUM(CASE month WHEN '9' THEN SumPrice ELSE 0 END) AS sep_price, SUM(CASE month WHEN '10' THEN SumQuantity ELSE 0 END) AS oct_quantity, SUM(CASE month WHEN '10' THEN SumPrice ELSE 0 END) AS oct_price, SUM(CASE month WHEN '11' THEN SumQuantity ELSE 0 END) AS nov_quantity, SUM(CASE month WHEN '11' THEN SumPrice ELSE 0 END) AS nov_price, SUM(CASE month WHEN '12' THEN SumQuantity ELSE 0 END) AS dec_quantity, SUM(CASE month WHEN '12' THEN SumPrice ELSE 0 END) AS dec_price, SUM(SumQuantity) as ytdqty, SUM(SumPrice) as ytdprice FROM qry_Sales_by_PAC_Code WHERE Year = '$Year' AND Area = '$id' GROUP BY Pac1, Pac1_v2 ORDER BY Pac1"; $result = odbc_exec($conn, $sql_month); echo "<Table border='0' cellpadding='4'><tr class='TitleText_White' bgcolor=#FF6600><th>Area: $id</th><th>Jan</th><th>Feb</th><th>Mar</th><th>Apr</th><th>May</th><th>June</th><th>July</th><th>Aug</th><th>Sept</th><th>Oct</th><th>Nov</th><th>Dec</th><th>Y.T.D.</th><th></th></tr>"; while (odbc_fetch_into($result, $data)) { $ButtonName = array ( 'A' => "Domestic", 'B' => "Ancillaries", 'N' => "Spares" ); $data[] = $ButtonName[$data[1]]; vprintf ("<tr bgcolor='#FFE0CC' class='Body2' align='right'> <form action='PAC_Level_2.php' method='post'><td align='center'> <input name='Year' type='hidden' value='$Year'> <input name='id' type='hidden' value='$id'> <input name='PAC' type='hidden' value='%s'> <input name='Submit' type='submit' value='%s'> </td></form> <td>%0.2f<br>£%0.2f</td><td>%0.2f<br>£%0.2f</td><td>%0.2f<br>£%0.2f</td><td>%0.2f<br>£%0.2f</td> <td>%0.2f<br>£%0.2f</td><td>%0.2f<br>£%0.2f</td><td>%0.2f<br>£%0.2f</td><td>%0.2f<br>£%0.2f</td> <td>%0.2f<br>£%0.2f</td><td>%0.2f<br>£%0.2f</td><td>%0.2f<br>£%0.2f</td><td>%0.2f<br>£%0.2f</td> <td>%0.2f<br>£%0.2f</td><td>%s</td></tr>", $data); } echo '</table>'; ?> Can anyone see why it isn't renewing with each line? Cheers.

Hi Barand... Its you helping me AGAIN!! I'm actually using this in the code you helped me with yesterday. It's shown below in full but I'm getting the following error: Error: Notice: Undefined index: 42.0000 in D:\Intranet v3\Sales\Individual_Area_Summary_by_PAC_Code.php on line 47 I'm guessing that it is trying to resolve the array value but with the wrong variable as 42 is the jan_quantity value? <?php $sql_month="SELECT Pac1, Pac1_v2, SUM(CASE month WHEN '1' THEN SumQuantity ELSE 0 END) AS jan_quantity, SUM(CASE month WHEN '1' THEN SumPrice ELSE 0 END) AS jan_price, SUM(CASE month WHEN '2' THEN SumQuantity ELSE 0 END) AS feb_quantity, SUM(CASE month WHEN '2' THEN SumPrice ELSE 0 END) AS feb_price, SUM(CASE month WHEN '3' THEN SumQuantity ELSE 0 END) AS mar_quantity, SUM(CASE month WHEN '3' THEN SumPrice ELSE 0 END) AS mar_price, SUM(CASE month WHEN '4' THEN SumQuantity ELSE 0 END) AS apr_quantity, SUM(CASE month WHEN '4' THEN SumPrice ELSE 0 END) AS apr_price, SUM(CASE month WHEN '5' THEN SumQuantity ELSE 0 END) AS may_quantity, SUM(CASE month WHEN '5' THEN SumPrice ELSE 0 END) AS may_price, SUM(CASE month WHEN '6' THEN SumQuantity ELSE 0 END) AS jun_quantity, SUM(CASE month WHEN '6' THEN SumPrice ELSE 0 END) AS jun_price, SUM(CASE month WHEN '7' THEN SumQuantity ELSE 0 END) AS jul_quantity, SUM(CASE month WHEN '7' THEN SumPrice ELSE 0 END) AS jul_price, SUM(CASE month WHEN '8' THEN SumQuantity ELSE 0 END) AS aug_quantity, SUM(CASE month WHEN '8' THEN SumPrice ELSE 0 END) AS aug_price, SUM(CASE month WHEN '9' THEN SumQuantity ELSE 0 END) AS sep_quantity, SUM(CASE month WHEN '9' THEN SumPrice ELSE 0 END) AS sep_price, SUM(CASE month WHEN '10' THEN SumQuantity ELSE 0 END) AS oct_quantity, SUM(CASE month WHEN '10' THEN SumPrice ELSE 0 END) AS oct_price, SUM(CASE month WHEN '11' THEN SumQuantity ELSE 0 END) AS nov_quantity, SUM(CASE month WHEN '11' THEN SumPrice ELSE 0 END) AS nov_price, SUM(CASE month WHEN '12' THEN SumQuantity ELSE 0 END) AS dec_quantity, SUM(CASE month WHEN '12' THEN SumPrice ELSE 0 END) AS dec_price, SUM(SumQuantity) as ytdqty, SUM(SumPrice) as ytdprice FROM qry_Sales_by_PAC_Code WHERE Year = '$Year' AND Area = '$id' GROUP BY Pac1, Pac1_v2 ORDER BY Pac1"; $ButtonName = array ( 'A' => "Domestic", 'B' => "Ancillaries", 'N' => "Spares" ); $result = odbc_exec($conn, $sql_month); echo "<Table border='0' cellpadding='4'><tr class='TitleText_White' bgcolor=#FF6600><th>Area: $id</th><th>Jan</th><th>Feb</th><th>Mar</th><th>Apr</th><th>May</th><th>June</th><th>July</th><th>Aug</th><th>Sept</th><th>Oct</th><th>Nov</th><th>Dec</th><th>Y.T.D.</th></tr>"; while (odbc_fetch_into($result, $data)) { $data[] = $ButtonName[$data[2]]; vprintf ("<tr bgcolor='#FFE0CC' class='Body2' align='right'> <form action='PAC_Level_2.php' method='post'><td align='center'> <input name='Year' type='hidden' value='$Year'> <input name='id' type='hidden' value='$id'> <input name='PAC' type='hidden' value='%s'> <input name='Submit' type='submit' value='%s'> </td></form> <td>%0.2f<br>£%0.2f</td><td>%0.2f<br>£%0.2f</td><td>%0.2f<br>£%0.2f</td><td>%0.2f<br>£%0.2f</td> <td>%0.2f<br>£%0.2f</td><td>%0.2f<br>£%0.2f</td><td>%0.2f<br>£%0.2f</td><td>%0.2f<br>£%0.2f</td> <td>%0.2f<br>£%0.2f</td><td>%0.2f<br>£%0.2f</td><td>%0.2f<br>£%0.2f</td><td>%0.2f<br>£%0.2f</td> <td>%0.2f<br>£%0.2f</td></tr>", $data); } echo '</table>'; ?> Could you also explain what this line does : $data[] = $ButtonName[$data[2]];

Hello, Would someone have a quick look at my code and see if you can suggest a solution! The code below pulls all the data from the database correctly but I want to change what is displayed. Usually I would just use an if-else or a switch but the field i want to change is inside a vprintf(). Here's the code I'm using at the moment.... <?php while (odbc_fetch_into($result, $data)) { vprintf (" <form action='PAC_Level_2.php' method='post'><td align='center'> <input name='Year' type='hidden' value='$Year'> <input name='id' type='hidden' value='$id'> <input name='PAC' type='hidden' value='%s'> <input name='Submit' type='submit' value='%s' </td></form>$data); } ?> ...and here's an example of the switch... switch (%s){ case "A": echo "Domestic"; case "B": echo "Ancillaries"; case "N": echo "Spares"; }; Can anyone suggest a way to combine them? Thank you.

Thanks Barand its working now! Had to do a bit of jiggery pokery once I realised I couldn't use IF statements on an MS SQL server but here's where I got to (and it works woo hoo!). Again many thanks for your help on this one. <?php $sql_month="SELECT Account, SUM(CASE month WHEN '1' THEN SumPrice ELSE 0 END) AS jan, SUM(CASE month WHEN '2' THEN SumPrice ELSE 0 END) AS feb, SUM(CASE month WHEN '3' THEN SumPrice ELSE 0 END) AS mar, SUM(CASE month WHEN '4' THEN SumPrice ELSE 0 END) AS apr, SUM(CASE month WHEN '5' THEN SumPrice ELSE 0 END) AS may, SUM(CASE month WHEN '6' THEN SumPrice ELSE 0 END) AS jun, SUM(CASE month WHEN '7' THEN SumPrice ELSE 0 END) AS jul, SUM(CASE month WHEN '8' THEN SumPrice ELSE 0 END) AS aug, SUM(CASE month WHEN '9' THEN SumPrice ELSE 0 END) AS sep, SUM(CASE month WHEN '10' THEN SumPrice ELSE 0 END) AS oct, SUM(CASE month WHEN '11' THEN SumPrice ELSE 0 END) AS nov, SUM(CASE month WHEN '12' THEN SumPrice ELSE 0 END) AS dec, SUM(SumQuantity) as ytdqty, SUM(SumPrice) as ytdprice FROM qry_Sales_by_Customer WHERE Year = '2007' AND Area = '0032' GROUP BY Account"; $result = odbc_exec($conn, $sql_month); echo "<Table border='0' cellpadding='4'><tr class='TitleText_White' bgcolor=#FF6600><th>Area: $id</th><th>Jan</th><th>Feb</th><th>Mar</th><th>Apr</th><th>May</th><th>June</th><th>July</th><th>Aug</th><th>Sept</th><th>Oct</th><th>Nov</th><th>Dec</th><th>Y.T.D.</th></tr>"; while (odbc_fetch_into($result, $data)) { vprintf ('<tr><td>%s</td> <td>%0.2f</td><td>%0.2f</td><td>%0.2f</td><td>%0.2f</td> <td>%0.2f</td><td>%0.2f</td><td>%0.2f</td><td>%0.2f</td> <td>%0.2f</td><td>%0.2f</td><td>%0.2f</td><td>%0.2f</td> <td>%0.2f<br>%0.2f</td></tr>', $data); } echo '</table>'; // and this ?>

Hi Barand... Sorry you seem to be holding my hand through this one! Here's the code I have so far, its also got the quantities for each month and I've hard coded the variables until I get it working: <?php $sql_month="SELECT Account, SUM(IF(month=1, price, 0)) as price_jan, SUM(IF(month=2, price, 0)) as price_feb, SUM(IF(month=3, price, 0)) as price_mar, SUM(IF(month=4, price, 0)) as price_apr, SUM(IF(month=5, price, 0)) as price_may, SUM(IF(month=6, price, 0)) as price_jun, SUM(IF(month=7, price, 0)) as price_jul, SUM(IF(month=8, price, 0)) as price_aug, SUM(IF(month=9, price, 0)) as price_sep, SUM(IF(month=10, price, 0)) as price_oct, SUM(IF(month=11, price, 0)) as price_nov, SUM(IF(month=12, price, 0)) as price_dec, SUM(IF(month=1, quantity, 0)) as quantity_jan, SUM(IF(month=2, quantity, 0)) as quantity_feb, SUM(IF(month=3, quantity, 0)) as quantity_mar, SUM(IF(month=4, quantity, 0)) as quantity_apr, SUM(IF(month=5, quantity, 0)) as quantity_may, SUM(IF(month=6, quantity, 0)) as quantity_jun, SUM(IF(month=7, quantity, 0)) as quantity_jul, SUM(IF(month=8, quantity, 0)) as quantity_aug, SUM(IF(month=9, quantity, 0)) as quantity_sep, SUM(IF(month=10, quantity, 0)) as quantity_oct, SUM(IF(month=11, quantity, 0)) as quantity_nov, SUM(IF(month=12, quantity, 0)) as quantity_dec, SUM(quantity) as ytdqty, SUM(price) as ytdprice FROM qry_Sales_by_Customer WHERE Year = '2007' AND Area = '0032' GROUP BY Account"; $result=odbc_exec($conn,$sql); if (!$result) {exit("Error in SQL");} while (odbc_fetch_row($result)) { echo $row['price_jan']; } ?>[code] Unfortunately I'm just getting "Undefined variable: row" as I haven't declared what $row is! Where do I set up the array or do I just need to change the way I’m trying to output the results? [/code]

Cheers for that Barand, But I'm still in the dark as to how to print the array now the values are in there. Can you recommend a tutorial on arrays as I think I need to go back to basics with them!

Yep...

Month is just 1,2,3...

Table: qry_Sales_by_Customer Fields: Account Price Quantity Month Year Area But if a customer hasn't bought anything in a month then there is no row for that month, there are no rows with 0 quantity.

Hi Barand, Have you got an example or tutorial like this that works as I'm having trouble adapting this one. Cheers. Tom

Hi Barand, That looks spot on to me! Hopefully if I do it like that it'll only be 1 query per customer. 1 question though... Once I've got that query into my page how do I print the values? Is it just a case of echo "jan"; ?

Hi Guys, I've written the page below which creates a table of sales figures from an sql database, unfortunately the way I have written it if someone has 40 customers the database hase to run about 520 select queries. Can anyone see a more streamlined way of doing this? What I am basically trying to get is a table with these columns: Customer ID, Jan, Feb, Mar, April, May ..... Dec, Year-To-Date Under the YTD and month columns there is just a quantity and total value. Anway here's the code if anyone can help I'd appreciate it as every time someone has a look at their sales page the whole webserver locks up! <?php $sql="SELECT * FROM qry_Sales_Accounts_by_Customer WHERE Year = '$Year' AND Area = '$id'"; $result=odbc_exec($conn,$sql); if (!$result) {exit("Error in SQL");} while (odbc_fetch_row($result)) { $Account=odbc_result($result,"Account"); echo "<tr align='right' bgcolor='#FFE0CC' class='Body2'><td align='center'>$Account</td>"; //----------Start of 12 month loop--------- $Month_by_Cust = 0; while($Month_by_Cust < 12){ echo "<td>"; $Month_by_Cust = $Month_by_Cust + 1; //echo "$Month_by_Cust<br>$Year<br>$id<br>$Account"; $sql_month="SELECT * FROM qry_Sales_by_Customer WHERE Year = '$Year' AND Month = '$Month_by_Cust' AND Area = '$id' AND Account = '$Account'"; $result_month=odbc_exec($conn,$sql_month); if (!$result_month) {exit("Error in SQL");} while (odbc_fetch_row($result_month)) { $Price=odbc_result($result_month,"SumPrice"); $Price = number_format($Price); $Quantity=odbc_result($result_month,"SumQuantity"); $Quantity = number_format($Quantity); echo "$Quantity<br>£$Price"; } echo "</td>"; } //-----------------Year To Date Totals---------------------------- echo "<td>"; $sql_year="SELECT * FROM qry_Sales_by_Customer_by_Year WHERE Year = '$Year' AND Area = '$id' AND Account = '$Account'"; $result_year=odbc_exec($conn,$sql_year); if (!$result_year) {exit("Error in SQL");} while (odbc_fetch_row($result_year)) { $Price=odbc_result($result_year,"SumPrice"); $Price = number_format($Price); $Quantity=odbc_result($result_year,"SumQuantity"); $Quantity = number_format($Quantity); echo "$Quantity<br>"; echo "£$Price"; } echo "</td>"; echo "</tr>"; } ?>

Cheers farkewie, but it still isn't displaying anything if there are no records.

Sorry... edited the code as it was just indented by miles! Should now be readable.

Hi Guys, Can someone have a quick look at my code for me.... I've written a page with some sales analysis on it and the figures are broken down in a table by month and customer. My problem is that when there are no sales in a particular month for that customer the cell of the table is blank as oppose to showing quantity = 0, value = £0 Code: $sql_month="SELECT * FROM qry_Sales_by_Customer WHERE Year = '$Year' AND Month = '$Month_by_Cust' AND Area = '$id' AND Account = '$Account'"; $result_month=odbc_exec($conn,$sql_month); if (!$result_month) {exit("Error in SQL");} while (odbc_fetch_row($result_month)) { $Price=odbc_result($result_month,"SumPrice"); $Price = number_format($Price); $Quantity=odbc_result($result_month,"SumQuantity"); $Quantity = number_format($Quantity); //Here is my attempt at entering 0, £0 instead of a null cell if (!isset($Quantity)){echo "0<br>£0";} elseif (is_null($Quantity)){echo "0<br>£0";} elseif (empty($Quantity)){echo "0<br>£0";} else {echo "$Quantity<br>£$Price";} }

Hello, I usually sort out any header information, declare any variables, include the start of my design, do something here!, include the end of my design: e.g. - session bits - get/post variables - validate any recieved data - include the start of my layout - display results or error messages / warnings - include end of my layout There are loads of different ways to do it. Does anyone have a better method? Hope that helps.

Cheers MadTechie, I obviously need to catch up on some sleep! Here's my revised and working While Loop in case anyone else was looking! while(!empty($id)){ echo "- $id<br>"; if ($id == $Area_01){$id = $Area_02;} elseif ($id == $Area_02){$id = $Area_03;} elseif ($id == $Area_03){$id = $Area_04;} elseif ($id == $Area_04){$id = $Area_05;} elseif ($id == $Area_05){$id = 'NULL';} }

Hi Guys, Could someone take a look at my code for me?! I've made another stupid error somewhere. while (odbc_fetch_row($result_Area)) { $Area_01=odbc_result($result_Area,"Area_01"); $Area_02=odbc_result($result_Area,"Area_02"); $Area_03=odbc_result($result_Area,"Area_03"); $Area_04=odbc_result($result_Area,"Area_04"); $Area_05=odbc_result($result_Area,"Area_05"); } $id = $Area_01; while(!empty($id)){ echo "moose<br>"; //some sample code to check the loop works! if ($id == $Area_01){$id == $Area_02} elseif ($id == $Area_02){$id == $Area_03} elseif ($id == $Area_03){$id == $Area_04} elseif ($id == $Area_04){$id == $Area_05} else {$id == $Area_05} } So for example if fields Area_01, Area_02, Area_03 are populated and Area_04 is empty, I would expect to see: moose moose moose Instead I get the following error message. Error: Parse error: parse error, unexpected '}' in D:\Intranet v3\Sales\home.php Can anyone see where I'm going wrong?

For the log-in/out side of it take a look at sessions for a secure system, if its going to be internet based. You don't want anyone logging on and changing data for you! Its not too complicated and again there are plenty of people on here to help with specific queries. Hope that helps!

Thanks ReDucTor, I had been staring at that for far too long and just couldn't see it. I need a break I think! Tom