Performing addition on row values from DB

Question

fizanos 0 Light Poster

14 Years Ago

<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

php

3 Contributors
14 Replies
608 Views
9 Hours Discussion Span
Latest Post 14 Years Ago Latest Post by fizanos

urtrivedi 276 Nearly a Posting Virtuoso

14 Years Ago

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>

urtrivedi 276 Nearly a Posting Virtuoso

14 Years Ago

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");
.
.

rajarajan2017

14 Years Ago

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

Edited 14 Years Ago by rajarajan2017 because: n/a

urtrivedi 276 Nearly a Posting Virtuoso

14 Years Ago

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

Reply to this topic

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.

fizanos 0 Light Poster · Answer 1 · 2010-05-31T17:49:08+00:00

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.

fizanos 0 Light Poster · Answer 2 · 2010-05-31T17:49:30+00:00

see thishttp://www.cruiselink-ng.com/img.jpg

fizanos 0 Light Poster · Answer 3 · 2010-05-31T17:59:14+00:00

fizanos 0 Light Poster

14 Years Ago

Yes. I'm getting the Grand Total.

fizanos 0 Light Poster · Answer 4 · 2010-05-31T18:02:44+00:00

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>

fizanos 0 Light Poster · Answer 5 · 2010-05-31T18:03:34+00:00

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);
?>

fizanos 0 Light Poster · Answer 6 · 2010-05-31T18:04:55+00:00

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>

fizanos 0 Light Poster · Answer 7 · 2010-05-31T18:07:14+00:00

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

urtrivedi 276 Nearly a Posting Virtuoso · Answer 8 · 2010-05-31T18:24:17+00:00

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

fizanos 0 Light Poster · Answer 9 · 2010-05-31T18:25:52+00:00

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!

fizanos 0 Light Poster · Answer 10 · 2010-05-31T18:37:53+00:00

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