Data display in wrong column

Octagon_AU · September 1, 2015

Hi,

i get difficulties to solve the following below and i hope someone can help with this.

mac_gyver · September 1, 2015

the answer is 42 :psychic:

Octagon_AU · September 1, 2015

the answer is 42

What do you mean?

mac_gyver · 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?

Barand · 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.

Barand · September 1, 2015

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

Octagon_AU · 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>";

Octagon_AU · September 9, 2015

Output which is not correct

Edited September 9, 2015 by Octagon_AU

mac_gyver · September 9, 2015

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

Octagon_AU · September 9, 2015

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

Ch0cu3r · 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')
    );
}

Octagon_AU · 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;

Octagon_AU · September 9, 2015

Hi,

Almost done, but i get extra rows and it don't need.

Ch0cu3r · September 9, 2015

Remove the </tr> from this line

$content .= "<tr><th> $branch </th></tr>";

Octagon_AU · September 10, 2015

Thank you so much guys. to all your help thank you...

Octagon_AU · September 10, 2015

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

mac_gyver · 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.

Octagon_AU · 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;

Ch0cu3r · September 10, 2015

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

Sign In

Data display in wrong column

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information