Octagon_AU Posted September 1, 2015 Share Posted September 1, 2015 Hi, i get difficulties to solve the following below and i hope someone can help with this. Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted September 1, 2015 Share Posted September 1, 2015 the answer is 42 Quote Link to comment Share on other sites More sharing options...
Octagon_AU Posted September 1, 2015 Author Share Posted September 1, 2015 the answer is 42 What do you mean? Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted September 1, 2015 Share Posted September 1, 2015 your post contains absolutely no information upon which to help you find what's wrong with what you are doing. you got an answer that was equally useful (42 is the Answer to the Ultimate Question of Life, the Universe, and Everything from the The Hitchhiker's Guide to the Galaxy.) the only thing anyone can determine from your post is that the problem is somewhere in what you are doing. care to show us your query and code that reproduces the problem so that someone could tell you if the problem is in your database query, in the php code retrieving the data from the database query, or in the html that's being produced? Quote Link to comment Share on other sites More sharing options...
Barand Posted September 1, 2015 Share Posted September 1, 2015 If the image on the left is your db table then it needs redesigning. Sales should be DECIMAL type, without the "$" Date should be DATE type, format YYYY-MM-DD Functionally your current values are worthless. All you can do with them is display them. You can't sort or compare the dates and you can't get a total of the sales. Quote Link to comment Share on other sites More sharing options...
Barand Posted September 1, 2015 Share Posted September 1, 2015 (edited) here's one solution $db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE); /************************************************** * Get the dates for headings * and for keys to the arrays * for each name ***************************************************/ $sql = "SELECT DISTINCT date FROM sales ORDER BY date"; $res = $db->query($sql); $heads = []; $newarray = []; while ($row = $res->fetch_row()) { $heads[] = date('n/j/Y', strtotime($row[0])); $newarray[$row[0]] = ''; } /************************************************** * Get the data from the table * and store in arrays * for each name ***************************************************/ $sql = "SELECT name , date , sales FROM sales"; $res = $db->query($sql); $data = []; while (list($name,$date,$sales) = $res->fetch_row()) { if (!isset($data[$name])) { $data[$name] = $newarray; // blank array for name } $data[$name][$date] = $sales; } /************************************************** * Now output the array * ***************************************************/ echo "<table border='1'>\n"; echo "<tr><th>Name</th><th>" . join('</th><th>', $heads) . "</th></tr>\n"; foreach ($data as $name => $salesdata) { echo "<tr><td>$name</td><td>" . join('</td><td>', $salesdata) . "</td></tr>\n"; } echo "</table>\n"; [edit] NOTE: assumes correct data formats +--------+--------+------------+ | name | sales | date | +--------+--------+------------+ | Liya | 200.00 | 2015-01-01 | | Faye | 100.00 | 2015-01-01 | | Faye | 300.00 | 2015-01-02 | | Jerome | 120.00 | 2015-01-01 | | Liya | 130.00 | 2015-01-03 | | Liya | 400.00 | 2015-01-04 | | Jerome | 210.00 | 2015-01-02 | | Ronna | 230.00 | 2015-01-02 | +--------+--------+------------+ Edited September 1, 2015 by Barand 1 Quote Link to comment Share on other sites More sharing options...
Octagon_AU Posted September 9, 2015 Author Share Posted September 9, 2015 Hi Barand, i try to follow your code but i did not get it. and difficult to convert using MS Access DB or using ODBC. take a look my code and it work but wrong column base on header. Hope someone can take a time to analyze code below $sql1 = "SELECT DISTINCT BRANCH FROM sales ORDER BY BRANCH asc"; $rs = odbc_exec($connect, $sql1); if(!$rs) {exit("Error in SQL");} echo"<table class='table table-bordered'>"; $result_branch = array(); while (odbc_fetch_row($rs)) { $result_branch[] = odbc_result($rs,'branch'); } echo "<tr style = 'background-color:#ccc;'>"; echo "<td style = 'font-weight:bold;font-size:12px;'>"; echo "BRANCH / DATE"; echo "</td>"; $sqldate = "SELECT DISTINCT Date FROM sales where Date BETWEEN #8/1/2015# AND #8/30/2015# ORDER BY Date asc"; $rs1 = odbc_exec($connect, $sqldate); $result_date = array(); while (odbc_fetch_row($rs1)) { $result_date[] = odbc_result($rs1,'Date'); } foreach($result_date as $rs_date) { echo "<td style = 'font-weight:bold;font-size:12px;'>"; echo substr("$rs_date",0, 10); echo "</td>"; } echo "<td style = 'font-weight:bold;'>" . TOTAL . "</td>"; echo "</tr>"; foreach($result_branch as $rs_branch) { echo "<tr>"; echo "<td style = 'font-weight:bold; width:70px;background-color:#ccc;font-size:12px;'>". $rs_branch . "</td>"; $sql2 = ("SELECT Date,SumOftotal_inc,BRANCH FROM sales WHERE BRANCH = '$rs_branch' ORDER BY Date asc"); $rs2 = odbc_exec($connect, $sql2); $result_total = array(); $result_date2 = array(); while (odbc_fetch_row($rs2)) { $result_total[] = odbc_result($rs2,'SumOftotal_inc'); $result_date2[] = odbc_result($rs2,'Date'); } foreach($result_total as $rs_total) { echo "<td style = 'color:#fe0000;font-size:12px;'>₱". number_format($rs_total, 2). "<input type = 'hidden' name = 'hdden' value = '$result_date2'></td>"; } echo "<td style = 'color:#000;font-weight:bold;'> ₱"; $mval = array_sum($result_total); echo number_format($mval, 2); echo "<input type = 'hidden' name = 'hdden' value = '$result_date2'></td>"; echo "</tr>"; } echo "<tr>"; echo "<td style = 'font-weight:bold;'>"; echo "Total"; echo "</td>"; echo "</tr>"; echo "</table>"; Quote Link to comment Share on other sites More sharing options...
Octagon_AU Posted September 9, 2015 Author Share Posted September 9, 2015 (edited) Output which is not correct Edited September 9, 2015 by Octagon_AU Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted September 9, 2015 Share Posted September 9, 2015 (edited) this seems to be a common task. you have far too many queries and too much code. you need to separate the concerns by first retrieving the data and storing it in php array variable(s), then loop over that data to produce the output. you can then troubleshoot each concern separately. separating the different concerns also groups the database specific statements together so that if you need to change the type of database server, you can do it all in one place without needing to touch the code that's responsible for producing the output. see the following example - // simulated data from one query for whatever range of branch names and range of dates you have chosen in the implied query // use ORDER BY BRANCH in the query to get the branch names in the order that you want in the final result $rows[] = array('branch'=>'b1','date'=>'2015-08-02','sales'=>'1000110.00'); $rows[] = array('branch'=>'b1','date'=>'2015-08-03','sales'=>'1189015.00'); $rows[] = array('branch'=>'b1','date'=>'2015-08-04','sales'=>'1902110.00'); $rows[] = array('branch'=>'b1','date'=>'2015-08-05','sales'=>'1000122.00'); $rows[] = array('branch'=>'b2','date'=>'2015-08-02','sales'=>'2002110.00'); $rows[] = array('branch'=>'b2','date'=>'2015-08-03','sales'=>'1701110.00'); $rows[] = array('branch'=>'b2','date'=>'2015-08-04','sales'=>'2980110.00'); $rows[] = array('branch'=>'b3','date'=>'2015-08-02','sales'=>'1597110.00'); $rows[] = array('branch'=>'b3','date'=>'2015-08-03','sales'=>'2201110.00'); /* i would loop over the result from your database query and produce two arrays. the first array gets all the dates. the second multi-dimensional array holds the data, using the branch as the index for the first dimension, the date as the index for the second dimension, and the sales as the stored data value. */ $dates = array(); // all the dates. this, with the array_unique() statement, accomplishes the same as the DISTINCT date query, without the query $data = array(); // the data, with branch and date as the index foreach($rows as $row){ $dates[] = $row['date']; // you could test if a date isn't already in the array, but using array_unique, once, after the loop will be faster $data[$row['branch']][$row['date']] = $row['sales']; } /* use array_unique() on the first array, then sort that resulting array. this will produce an array of unique dates in ascending order for producing the heading and for accessing the data under those headings. */ $dates = array_unique($dates); sort($dates); /* to produce the result, loop over the second array's first dimension (branch), outputting the branch name as the label for the row. then, loop over the first array, and use each date to access the data, if any, for the current branch for that date. if there isn't a value, output whatever indication you want (0, ----, n/a, blank). if there is a value, output the value. repeat for all branches being looped over. */ $no_data = 'n/a'; // what to display when there is no data for a column $date_format = 'n/j/Y'; // format for displaying dates $currency = '$'; // symbol for currency $content = "<table><tr><th>BRANCH/DATE</th>"; // produce heading foreach($dates as $date){ $dt = new DateTime($date); $df = $dt->format($date_format); $content .= "<th>$df</th>"; } $content .= "<th>TOTAL</th></tr>"; // produce data section foreach($data as $branch=>$arr){ $content .= "<tr><th>$branch</th>"; foreach($dates as $date){ $value = isset($arr[$date]) ? $currency.number_format((double)$arr[$date],2) : $no_data; // handle what to show in the cells with no data $content .= "<td>$value</td>"; } $total = array_sum($arr); $content .= "<td>$currency".number_format($total,2)."</td></tr>"; } $content .= "</table>"; echo $content; your task to make this work with your database would just be to form and run the one query that retrieves the data you want and store it in an array named $rows. you would also need to style the output the way you want it. Edited September 9, 2015 by mac_gyver Quote Link to comment Share on other sites More sharing options...
Octagon_AU Posted September 9, 2015 Author Share Posted September 9, 2015 (edited) Hi mac_gyver, Thank you so much for your time. and in ODBC its need to have this lines to get the result but how do i convert this in array? $sql1 = "SELECT * FROM sales"; $rs = odbc_exec($connect, $sql1); if(!$rs) {exit("Error in SQL");} while (odbc_fetch_row($rs)) { $rs_total = odbc_result($rs,'SumOftotal_inc'); $rs_branch = odbc_result($rs,'BRANCH'); $rs_date = odbc_result($rs,'Date'); } i try to have this but, not work. $rows[] = array($rs_branch,$rs_date,$rs_total); Edited September 9, 2015 by Octagon_AU Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted September 9, 2015 Share Posted September 9, 2015 If you are using mac_gyver code to define the $rows array then you need to assign the data from your query to the branch, date and sales keys, like so while (odbc_fetch_row($rs)) { $rows[] = array( 'branch' => odbc_result($rs,'BRANCH'), 'date' => odbc_result($rs,'Date'), 'sales' => odbc_result($rs,'SumOftotal_inc') ); } Quote Link to comment Share on other sites More sharing options...
Octagon_AU Posted September 9, 2015 Author Share Posted September 9, 2015 Hi. mac_gyver and ch0cu3r, a little bit, and very close to solve my problem. i get this output and what is wrong with it. code $sql1 = "SELECT * FROM sales"; $rs = odbc_exec($connect, $sql1); if(!$rs) {exit("Error in SQL");} $dates = array(); $data = array(); while (odbc_fetch_array($rs)) { $rows[] = array( 'branch' => odbc_result($rs,'BRANCH'), 'date' => odbc_result($rs,'Date'), 'sales' => odbc_result($rs,'SumOftotal_inc') ); } foreach($rows as $row) { $dates[] = $row['date']; $data[$row['branch']][$row['sate']] = $row['sales']; } $dates = array_unique($dates); sort($dates); $no_data = 'n/a'; $date_format = 'n/j/Y'; $currency = '$'; $content = "<table class='table table-bordered'><tr><th>BRANCH / DATE</th>"; foreach($dates as $date) { $dt = new DateTime($date); $df = $dt->format($date_format); $content .= "<th> $df </th>"; } $content .= "<th> TOTAL </th>"; foreach($data as $branch=>$arr) { $content .= "<tr><th> $branch </th></tr>"; foreach($dates as $date) { $value = isset($arr[$date]) ? $currency.number_format((double)$arr[$date],2) : $no_data; $content .= "<td>$value</td>"; } $total = array_sum($arr); $content .= "<td>$currency" .number_format($total,2). "</td></tr>"; } $content .= "</table>"; echo $content; Quote Link to comment Share on other sites More sharing options...
Octagon_AU Posted September 9, 2015 Author Share Posted September 9, 2015 Hi, Almost done, but i get extra rows and it don't need. Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted September 9, 2015 Share Posted September 9, 2015 Remove the </tr> from this line $content .= "<tr><th> $branch </th></tr>"; Quote Link to comment Share on other sites More sharing options...
Octagon_AU Posted September 10, 2015 Author Share Posted September 10, 2015 Thank you so much guys. to all your help thank you... Quote Link to comment Share on other sites More sharing options...
Octagon_AU Posted September 10, 2015 Author Share Posted September 10, 2015 (edited) Hi, i just want to add new row to compute over_all total and total per day or per date? how Edited September 10, 2015 by Octagon_AU Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted September 10, 2015 Share Posted September 10, 2015 what have you tried? because the fun part of programming is in actually seeing code that you wrote produce the result that you want. Quote Link to comment Share on other sites More sharing options...
Octagon_AU Posted September 10, 2015 Author Share Posted September 10, 2015 i want to add new row to to the bottom which is to get the result of the total. code : $sql_query = "SELECT * FROM sales where Date BETWEEN #8/1/2015# AND #8/30/2015# ORDER BY BRANCH asc"; $rs = odbc_exec($connect, $sql_query); if(!$rs) {exit("Error in SQL");} $dates = array(); $data = array(); while (odbc_fetch_array($rs)) { $rows[] = array( 'branch' => odbc_result($rs,'BRANCH'), 'date' => odbc_result($rs,'Date'), 'sales' => odbc_result($rs,'SumOftotal_inc') ); } foreach($rows as $row) { $dates[] = $row['date']; $data[$row['branch']][$row['date']] = $row['sales']; } $dates = array_unique($dates); sort($dates); $no_data = "<p style = 'color:#fe0000'>0.00</p>"; $date_format = 'n/j/Y'; $currency = '₱'; $content = "<table class='table table-bordered table-hover'><tr style = 'background-color:#878787;font-weight:bold;font-size:12px;color:#ccc;'><th style = 'padding-top:20px;width:800px;'>BRANCH / DATE</th><th style = 'color:#ccc;padding-top:20px;'> TOTAL </th>"; foreach($dates as $date) { $dt = new DateTime($date); $df = $dt->format($date_format); $content .= "<th style = 'padding-top:20px;'> $df </th>"; } $content .= "</tr>"; foreach($data as $branch=>$arr) { $content .= "<tr class='active'><td class='col-md-6' style = 'background-color:#878787;font-weight:bold;font-size:12px;color:#ccc;padding-top:20px;'> $branch </td>"; $total = array_sum($arr); $content .= "<td style = 'color:#FE0000;font-size:12px;padding-top:20px;font-weight:bold;'>$currency" .number_format($total,2). "</td>"; foreach($dates as $date) { $value = isset($arr[$date]) ? $currency.number_format((double)$arr[$date],2) : $no_data; $content .= "<td class='text-center' style = 'color:#874b03;font-size:12px;padding-top:20px;'>$value</td>"; } $content .= "</tr>"; } foreach($total as $tot_count) { $count_total = array_sum($tot_count); } $content .= "<tr><td>TOTAL</td>"; $content .= "<td>$currency" .number_format($count_total,2). "</td>"; // display over all total $content .= "<td>TOTAL</td></tr>"; // display per date total $content .= "</table>"; echo $content; Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted September 10, 2015 Share Posted September 10, 2015 (edited) First add $totals = array(); $totals['total'] = 0; before foreach($data as $branch=>$arr){ Add $totals['total'] += $total; after $total = array_sum($arr); Then after $content .= "<td class='text-center' style = 'color:#874b03;font-size:12px;padding-top:20px;'>$value</td>"; Add if(!isset($totals[$date])) $totals[$date] = 0; $totals[$date] += isset($arr[$date]) ? $arr[$date] : 0; Next replace foreach($total as $tot_count) { $count_total = array_sum($tot_count); } $content .= "<tr><td>TOTAL</td>"; $content .= "<td>$currency" .number_format($count_total,2). "</td>"; // display over all total $content .= "<td>TOTAL</td></tr>"; // display per date total $content .= "</table>"; with $content .= "<tr><th>TOTAL</th>"; foreach($totals as $column => $total) { $content .= "<td>" . $currency.number_format($total, 2) . "</td>"; } $content .= "</tr></table>"; Edited September 10, 2015 by Ch0cu3r Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.