DaniWeb Forum Index > Web Development > PHP

Oganizing a Report for Excel

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Apr 2008

Posts: 45

Reputation: vanessia_1999 is an unknown quantity at this point

Solved Threads: 0

vanessia_1999 vanessia_1999 is offline

Offline

Light Poster

Oganizing a Report for Excel

Jul 2nd, 2009

I am having organization set up with my table. I am trying to code for a report to have the Purchase Date and Shipped Status information added/display onto my report but I continue getting errors such as the value is not showing up and my information look out of place.

I don't know where to put the purchase date ($date) and shipped status ($status) area without making the report table go wacky...all out of place. The report open up in Excel incase anyone was wondering.
I honestly tried and move it around through out the coding still nothing. I even added some <td> tags and nothing still a mess. Could someone please help me out

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
 
  echo "<table width=\"100%\" cellpadding=\"1\" border=\"1\">\n";
   echo "<tr><td colspan=\"6\"><b>Products sold between " . $startdate . " and " . $enddate . "</b></td></tr>\n";
   echo "<tr><td><b>Purchase Date</b></td><td><b>Shipped Status</b></td><td><b>Product</b></td><td><b>Quantity Sold</b></td><td><b>Unit price</b></td><td><b>Total</b></td></tr>\n";
   $count = 3;
   while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
   {
      $product = $row['description'];
      $quantity = $row['total'];
      $price = $row['price'];
      $total = $quantity * $price;
	  $date = $row['date'];
	  $status = $row['status'];
      echo "<tr><td>$product</td><td>$quantity</td>\n";
      printf("<td>%.2f</td><td>=B%s * C%s</tr>\n", $price, $count, $count);
      $count++;
   }
   $count--;
   echo "<tr><td><b>Total</b></td><td>=SUM(B3:B" . $count . ")</td><td>&nbsp;</td>\n";
   echo "<td>=SUM(D3:D" . $count . ")</td></tr>\n";
   echo "</table>\n";
  
  echo "<table width=\"100%\" cellpadding=\"1\" border=\"1\">\n";
   echo "<tr><td colspan=\"6\"><b>Products sold between " . $startdate . " and " . $enddate . "</b></td></tr>\n";
   echo "<tr><td><b>Purchase Date</b></td><td><b>Shipped Status</b></td><td><b>Product</b></td><td><b>Quantity Sold</b></td><td><b>Unit price</b></td><td><b>Total</b></td></tr>\n";
   $count = 3;
   while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
   {
      $product = $row['description'];
      $quantity = $row['total'];
      $price = $row['price'];
      $total = $quantity * $price;
	  $date = $row['date'];
	  $status = $row['status'];
      echo "<tr><td>$product</td><td>$quantity</td>\n";
      printf("<td>%.2f</td><td>=B%s * C%s</tr>\n", $price, $count, $count);
      $count++;
   }
   $count--;
   echo "<tr><td><b>Total</b></td><td>=SUM(B3:B" . $count . ")</td><td>&nbsp;</td>\n";
   echo "<td>=SUM(D3:D" . $count . ")</td></tr>\n";
   echo "</table>\n";

Last edited by vanessia_1999; Jul 2nd, 2009 at 5:54 am.

•

Join Date: Mar 2008

Posts: 152

Reputation: kireol is an unknown quantity at this point

Solved Threads: 19

kireol

Online

Junior Poster

Re: Oganizing a Report for Excel

Jul 2nd, 2009

The first problem I see is you are using 6 columns to start the table.

Then in your loop you are using 3.5 columns when there should be 6.

same with your totals line. should be 6 columns and you are using 4.

Why do Daniweb moderators edit CODE tags into CODE=style but don't check "solved".

•

Join Date: Apr 2008

Posts: 45

Reputation: vanessia_1999 is an unknown quantity at this point

Solved Threads: 0

vanessia_1999 vanessia_1999 is offline

Offline

Light Poster

Re: Oganizing a Report for Excel

Jul 3rd, 2009

I updated the column to make sure all the column are the same and still the result is wacky.
This is the code I know have

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
   echo "<table width=\"100%\" cellpadding=\"1\" border=\"1\">\n";
   echo "<tr><td colspan=\"6\"><b>Products sold between " . $startdate . " and " . $enddate . "</b></td></tr>\n";
   echo "<tr><td><b>Purchase Date</b></td><td><b>Shipped Status</b></td><<td><b>Product</b></td><td><b>Quantity Sold</b></td><td><b>Unit price</b></td><td><b>Total</b></td></tr>\n";
   $count = 3;
   while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
   {
      $product = $row['description'];
      $quantity = $row['total'];
      $price = $row['price'];
	  $date = $row['date'];
	  $status = $row['status'];
      $total = $quantity * $price;
      echo "<tr><td>$date</td><td>$status</td><td>$product</td><td>$quantity</td>\n";
      printf("<td>%.2f</td><td>=B%s * C%s</td></tr>\n", $price, $count, $count);
      $count++;
   }
   $count--;
   echo "<tr><td></td><td></td><td><b>Total</b></td><td>=SUM(B3:B" . $count . ")</td><td>&nbsp;</td>\n";
   echo "<td>=SUM(D3:D" . $count . ")</td></tr>\n";
   echo "</table>\n";
   echo "<table width=\"100%\" cellpadding=\"1\" border=\"1\">\n";
   echo "<tr><td colspan=\"6\"><b>Products sold between " . $startdate . " and " . $enddate . "</b></td></tr>\n";
   echo "<tr><td><b>Purchase Date</b></td><td><b>Shipped Status</b></td><<td><b>Product</b></td><td><b>Quantity Sold</b></td><td><b>Unit price</b></td><td><b>Total</b></td></tr>\n";
   $count = 3;
   while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
   {
      $product = $row['description'];
      $quantity = $row['total'];
      $price = $row['price'];
	  $date = $row['date'];
	  $status = $row['status'];
      $total = $quantity * $price;
      echo "<tr><td>$date</td><td>$status</td><td>$product</td><td>$quantity</td>\n";
      printf("<td>%.2f</td><td>=B%s * C%s</td></tr>\n", $price, $count, $count);
      $count++;
   }
   $count--;
   echo "<tr><td></td><td></td><td><b>Total</b></td><td>=SUM(B3:B" . $count . ")</td><td>&nbsp;</td>\n";
   echo "<td>=SUM(D3:D" . $count . ")</td></tr>\n";
   echo "</table>\n";

result...............
Products sold between 01/23/2007 and 08/23/2010
Purchase Date Shipped Status Product Quantity Sold Unit price Total
7/1/2009 17:48 shipped Highlight Color Clip in Extension 100 11.99 #VALUE!
7/1/2009 17:48 shipped starbright extension 1 3.33 #VALUE!
Total 0 100
The value is not showing it is saying value instead of the number.
And the total is wrong, because it is in the wrong place. I copy and paste this info from excel so I hope it shows like how I see it.

See there is something wrong that I am doing

•

Join Date: Mar 2008

Posts: 152

Reputation: kireol is an unknown quantity at this point

Solved Threads: 19

kireol

Online

Junior Poster

Re: Oganizing a Report for Excel

Jul 3rd, 2009

it's showing #Value! because excel is telling that what you filled in that cell is a formula that it doesnt understand.

From what I see you are trying to do, I think you want

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
printf("<td>%.2f</td><td>$total</td></tr>\n", $price );
printf("<td>%.2f</td><td>$total</td></tr>\n", $price );

follow that for your totals too. Not sure why you are using count there. Unless I'm misunderstanding what you are trying to put in that column, that should work.

Why do Daniweb moderators edit CODE tags into CODE=style but don't check "solved".