Jump to content

Data display in wrong column


Octagon_AU

Recommended Posts

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 |
+--------+--------+------------+

post-3105-0-99513600-1441097380_thumb.png

Edited by Barand
  • Like 1
Link to comment
Share on other sites

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>";
Link to comment
Share on other sites

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 by mac_gyver
Link to comment
Share on other sites

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 by Octagon_AU
Link to comment
Share on other sites

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')
    );
}
Link to comment
Share on other sites

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.

 

display1.jpg

 

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;
Link to comment
Share on other sites

i want to add new row to to the bottom which is to get the result of the total.

 

2myn503.jpg

 

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;
Link to comment
Share on other sites

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 by Ch0cu3r
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.