<table width="95%" border="1" align="center" CELLPADDING="2" CELLSPACING="0" bordercolor="<?PHP print "$BorderColour"; ?>">
          <tr valign="middle" class="tableheadsx">
            <td width="91" height="29" align="left" class="welcomexx"><strong>Date</strong></td>
            <td width="136" height="29" align="left" class="welcomexx"><strong>Distributor's ID </strong></td>
			<td width="136" height="29" align="left" class="welcomexx"><strong>Customer's Name </strong></td>
            <td width="140" height="29" align="left" class="welcomexx"><strong>Total Paid(Naira) </strong></td>
			<td width="157" height="29" align="left" class="welcomexx"><strong>Total Paid(Dollar)</strong></td>
          </tr>
		  <?php $rowShine = 1;?>
          <?php if ($totalRows_getPages > 0) { // Show if recordset not empty ?>
            <?php do { ?>
              <tr id="row-<?php echo $row_getPages['code']; ?>" <?php if($rowShine%2){
						echo "class=\"alternate\" ";
					}
			?>><?php $me=$row_getPages['totalDollar']; ?>
			<?php $mee=$row_getPages['totalNaira']; ?>
                <td height="25" align="left" valign="top" class="style1"><?php echo $row_getPages['xdate']; ?></td>
				 <td height="25" align="left" valign="top" class="style1"><?php echo $row_getPages['dis_id']; ?></td>
                <td height="25" align="left" valign="top" class="style1"><strong><?php echo $row_getPages['dis_name']; ?></strong></td>
                <td height="25" align="left" valign="top" class="style1"><?php print number_format($mee,2); ?></td>
				<td height="25" align="left" valign="top" class="style1"><?php print number_format($me); ?></td>
              </tr>
              <tr id="row-<?php echo $row_getPages['code']; ?>" <?php if($rowShine%2){
						echo "class=\"alternate\" ";
					}
			?>>
                <td height="25" align="left" valign="top" class="style1"><strong>Grand Total </strong></td>
                <td height="25" align="left" valign="top" class="style1">&nbsp;</td>
                <td height="25" align="left" valign="top" class="style1">&nbsp;</td>
                <td height="25" align="left" valign="top" class="style1"><img src="images/naira.jpg" width="9" height="11" />&nbsp;</td>
                <td height="25" align="left" valign="top" class="style1"><strong>$</strong></td>
              </tr>
              <?php $rowShine++;?>
              <?php } while ($row_getPages = mysql_fetch_assoc($getPages)); ?>
            <?php } // Show if recordset not empty ?>
        </table>

Hello

I'm new and i need your help.

I need to create a report using start date and end date.

I created a form that gets the values Between those that and display accordingly unfortunately i need to perform addition on some values.

Date Distributor's ID Customer's Name Total Paid(Naira) Total Paid(Dollar)
2010-05-31 001 Sanyaolu 598,728.00 3,939
Grand Total =N=X $X

I am not sure what is your problem, even though I guessed your problem and made certain changes.
1) used while{ } loop instead of do.. while loop
2) used two variable grandnaira & granddollar , intialize them and added current value in every iteration.
3) You were printing grand total in the while loop, i m not sure why. so I have taken out that grand total row out of while loop and printed two varaibles grandnaira & granddollar at appropriate place. I have also remove html tr ID for the grand total row.

</table><table width="95%" border="1" align="center" CELLPADDING="2" CELLSPACING="0" bordercolor="<?PHP print "$BorderColour"; ?>">
          <tr valign="middle" class="tableheadsx">
            <td width="91" height="29" align="left" class="welcomexx"><strong>Date</strong></td>
            <td width="136" height="29" align="left" class="welcomexx"><strong>Distributor's ID </strong></td>
			<td width="136" height="29" align="left" class="welcomexx"><strong>Customer's Name </strong></td>
            <td width="140" height="29" align="left" class="welcomexx"><strong>Total Paid(Naira) </strong></td>
			<td width="157" height="29" align="left" class="welcomexx"><strong>Total Paid(Dollar)</strong></td>
          </tr>
		  <?php 
		  $rowShine = 1;
		 //next 2 lines added by urtrivedi,			 		  
		  $grandnaira=0;
		  $granddollar=0;
		  ?>
          <?php if ($totalRows_getPages > 0) { // Show if recordset not empty ?>
            <?php  while ($row_getPages = mysql_fetch_assoc($getPages)) { ?>
              <tr id="row-<?php echo $row_getPages['code']; ?>" <?php if($rowShine%2){
						echo "class=\"alternate\" ";
					}
			?>>
			<?php 
			
			 $me=$row_getPages['totalDollar']; 
			 $mee=$row_getPages['totalNaira']; 
			 //next 2 lines added by urtrivedi,			 
			 $grandnaira +=$mee;
			 $granddollar+=$me;
			 
			 ?>
                <td height="25" align="left" valign="top" class="style1"><?php echo $row_getPages['xdate']; ?></td>
				 <td height="25" align="left" valign="top" class="style1"><?php echo $row_getPages['dis_id']; ?></td>
                <td height="25" align="left" valign="top" class="style1"><strong><?php echo $row_getPages['dis_name']; ?></strong></td>
                <td height="25" align="left" valign="top" class="style1"><?php print number_format($mee,2); ?></td>
				<td height="25" align="left" valign="top" class="style1"><?php print number_format($me); ?></td>
              </tr>
              
              <?php $rowShine++;?>
              <?php }//end while   ?>
			
			
			
			
			
			
			
			<tr <?php if($rowShine%2){
						echo "class=\"alternate\" ";
					}
			?>>
                <td height="25" align="left" valign="top" class="style1"><strong>Grand Total </strong></td>
                <td height="25" align="left" valign="top" class="style1">&nbsp;</td>
                <td height="25" align="left" valign="top" class="style1">&nbsp;</td>
                <td height="25" align="left" valign="top" class="style1"><img src="images/naira.jpg" width="9" height="11" /> <?php echo $grandnaira; ?></td>
                <td height="25" align="left" valign="top" class="style1"><strong>$</strong> <?php echo $granddollar; ?></td>
              </tr>              
            <?php } // Show if recordset not empty ?>
        </table>

Thanks bro. I really appreciate that you understand me now. Yet, the query only brings out one record and calculate the total. What do i do to get others. Its like creating a report and wanting to know the overall total at the end of say a month.

First of all let me know Have you applied the suggested changes and are you getting the grand total.

Secondly If you not getting more recrods then, You should post the portion of your code where you are executing database query.
supppost your code is like below, You just echo your query text, copy it and run it in phpmyadmin or other database tool, and see whether it is working there fine.

$query="select * from mytable where mycol=mycondtion";
echo $query;
.
.
.
$result=mysql_query($query");
.
.

Yes. I'm getting the Grand Total.

The form is:

<table width="90%" border="0" align="center" cellpadding="2" cellspacing="2">
  <tr valign="middle"> 
            <td align="left">&nbsp;</td>
          </tr>
  <tr valign="middle">
    <td align="left"><span class="welcomexx">Start Date:</span>
      <input type="text" name="firstinput" size="10" class="inputX" />
      </label>
      &nbsp;<a href="javascript:showCal('Calendar1')"><img src="images/calendar.png" width="20" height="19" border="0" /></a> &nbsp;
      <label for="endDate"><span class="welcomexx">End Date:</span>&nbsp;
      <input type="text" name="secondinput" size="10" class="inputX" />
      </label>
      &nbsp;<a href="javascript:showCal('Calendar2')"><img src="images/calendar.png" width="20" height="19" border="0" /></a>&nbsp;
      <input name="submit" type="submit" class="goZ" value="&raquo;&raquo; Generate Report" /></td>
  </tr>
  <tr valign="middle">
    <td align="left">&nbsp;</td>
  </tr>
</table>

then;

<?php
$firstinput=$_POST['firstinput'];
$secondinput=$_POST['secondinput'];
?>
<?php
mysql_select_db($database_tSaturnConn, $tSaturnConn);
$query_getPages = "SELECT * FROM proorder WHERE xdate BETWEEN '$firstinput' AND '$secondinput' ORDER BY xdate ASC";
$getPages = mysql_query($query_getPages, $tSaturnConn) or die(mysql_error());
$row_getPages = mysql_fetch_assoc($getPages);
$totalRows_getPages = mysql_num_rows($getPages);
?>

Lastly,

the one you re-wrote

<table width="95%" border="1" align="center" CELLPADDING="2" CELLSPACING="0" bordercolor="<?PHP print "$BorderColour"; ?>">
          <tr valign="middle" class="tableheadsx">
            <td width="91" height="29" align="left" class="welcomexx"><strong>Date</strong></td>
            <td width="136" height="29" align="left" class="welcomexx"><strong>Distributor's ID </strong></td>
			<td width="136" height="29" align="left" class="welcomexx"><strong>Customer's Name </strong></td>
            <td width="140" height="29" align="left" class="welcomexx"><strong>Total Paid(Naira) </strong></td>
			<td width="157" height="29" align="left" class="welcomexx"><strong>Total Paid(Dollar)</strong></td>
          </tr>
		  <?php 
		  $rowShine = 1;
		 //next 2 lines added by urtrivedi,			 		  
		  $grandnaira=0;
		  $granddollar=0;
		  ?>
          <?php if ($totalRows_getPages > 0) { // Show if recordset not empty ?>
            <?php  while ($row_getPages = mysql_fetch_assoc($getPages)) { ?>
              <tr id="row-<?php echo $row_getPages['code']; ?>" <?php if($rowShine%2){
						echo "class=\"alternate\" ";
					}
			?>>
			<?php 
			
			 $me=$row_getPages['totalDollar']; 
			 $mee=$row_getPages['totalNaira']; 
			 //next 2 lines added by urtrivedi,			 
			 $grandnaira +=$mee;
			 $granddollar+=$me;
			 
			 ?>
                <td height="25" align="left" valign="top" class="style1"><?php echo $row_getPages['xdate']; ?></td>
				 <td height="25" align="left" valign="top" class="style1"><?php echo $row_getPages['dis_id']; ?></td>
                <td height="25" align="left" valign="top" class="style1"><strong><?php echo $row_getPages['dis_name']; ?></strong></td>
                <td height="25" align="left" valign="top" class="style1"><?php print number_format($mee,2); ?></td>
				<td height="25" align="left" valign="top" class="style1"><?php print number_format($me); ?></td>
              </tr>
              
              <?php $rowShine++;?>
              <?php }//end while   ?>
			
			
			
			
			
			
			
			<tr <?php if($rowShine%2){
						echo "class=\"alternate\" ";
					}
			?>>
                <td height="25" align="left" valign="top" class="style1"><strong>Grand Total </strong></td>
                <td height="25" align="left" valign="top" class="style1">&nbsp;</td>
                <td height="25" align="left" valign="top" class="style1">&nbsp;</td>
                <td height="25" align="left" valign="top" class="style1"><img src="images/naira.jpg" width="9" height="11" /> <?php echo $grandnaira; ?></td>
                <td height="25" align="left" valign="top" class="style1"><strong>$</strong> <?php echo $granddollar; ?></td>
              </tr>              
            <?php } // Show if recordset not empty ?>
        </table>

Now, I have records with 2010-05-31 and 2010-06-01 but ONLY getting the record with 2010-06-01. Why?

Member Avatar for rajarajan2017

Open your phpadmin and goto your database -> Select the table -> goto sql tab to execute the query below. instead of your inputs directly give the dates and check whether you get the records from there, I think it will return one record. Then you will get confirmed the error is in your query. If not then in your php code. Check it first.

$query_getPages = "SELECT * FROM proorder WHERE xdate BETWEEN '$firstinput' AND '$secondinput' ORDER BY xdate ASC";

at line 8 add
echo $query_getPages;
copy the output query from browser and run in phpmyadmin. check the result

you again use do ....while () loop
that will solve your problem. because your code is desinged that way.

I did and I got SELECT * FROM proorder WHERE xdate BETWEEN '2010/05/30' AND '2010/06/30' ORDER BY xdate ASC

which brings out the two record within the dates, which is correct!

urtrivedi,rajarajan07, other guys. I thank you and really apprecite ur help. My code is good now.
@urtrivedi, the do....while worked with your code thanks

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.