php mysql subtotal script required

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
iamroming
Forum Commoner
Posts: 27
Joined: Sun Jan 31, 2010 4:02 am
Location: Hyderabad, India
Contact:

php mysql subtotal script required

Post by iamroming »

Please help me getting the result of subtotal from mysql database
Note: Iam attaching the picture for example
Attachments
post-141540-1196958950_thumb.jpg
post-141540-1196958950_thumb.jpg (14.78 KiB) Viewed 4897 times
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: php mysql subtotal script required

Post by social_experiment »

You don't supply any code which will be more helpful than the image in this instance;
http://www.sql-tutorial.net/SQL-SUM.asp
Have a look at this url on how to use the SUM function
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
iamroming
Forum Commoner
Posts: 27
Joined: Sun Jan 31, 2010 4:02 am
Location: Hyderabad, India
Contact:

Re: php mysql subtotal script required

Post by iamroming »

Dear social experiment sorry i did not ask the quesiton with full details.
I have a table of vehicle ledger with colums vehicle no ,TR no.transaction date ,Dr,Cr,Cexp,amnt,balance , here my question is the ledger has debit and credit for different dates but i want the subtotal of dr,cr,cexp and amnt after every vehicle no . below iam writing the code , any help would be appreciated
NOTE: Iam attaching the picture of my output please take a look

Code: Select all

$sql="SELECT * FROM vehicleledger where date  BETWEEN '$date1' and '$date2' and vehicleno BETWEEN '$Division' and '$Division1' order by vehicleno asc,updatetime asc
Attachments
vl.jpg
iamroming
Forum Commoner
Posts: 27
Joined: Sun Jan 31, 2010 4:02 am
Location: Hyderabad, India
Contact:

Re: help needed in subtotal row in php mysql

Post by iamroming »

I have a query wich (SELECT * FROM vehicle ) slects the vehicle and amount and date
like this
vehicle no. amount date
1111 150 2012-03-24
2222 350 2012-03-24
1111 150 2012-03-25
2222 350 2012-03-25

but i want the subtotal for every vehicle no for different dates like
vehicle no. amount date
1111 150 2012-03-24
1111 150 2012-03-25

SUB 300
2222 350 2012-03-24
2222 350 2012-03-25
SUB 700

I have used mysql GROUP BY with modifier ROLLUP but no results ,somebody help in this please
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: php mysql subtotal script required

Post by social_experiment »

Code: Select all

SELECT SUM(`amount`)
FROM `vehicle`
WHERE `vechicle no` = 1111; 
You can modify the example from the url;
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
iamroming
Forum Commoner
Posts: 27
Joined: Sun Jan 31, 2010 4:02 am
Location: Hyderabad, India
Contact:

Re: php mysql subtotal script required

Post by iamroming »

Dear Social thanks for ur reply , ithink ur not clearly getting what my question is
.when i retrevie the result with SUM('amount') where vehicleno='1111' and try to echo it
this will be displayed


vehicle no. amount date
1111 150 2012-03-24
2222 350 2012-03-24
1111 150 2012-03-25
2222 350 2012-03-25

Sub 300

but my question is how to insert a new row to display subtotal or (SUM('amount') where Vehicleno='1111' as u said ) between the vehiclenos as i am
using while(loop) to get array of results in php ,or is there any subquery in mysql to get the desired result?
pls help me
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: php mysql subtotal script required

Post by social_experiment »

iamroming wrote:think ur not clearly getting what my question is
I concur :)
iamroming wrote:but my question is how to insert a new row to display subtotal or (SUM('amount') where Vehicleno='1111' as u said ) between the vehiclenos as i am
using while(loop) to get array of results in php ,or is there any subquery in mysql to get the desired result?
If you could clarify the above statement. I already gave an example of an sql query you can use for this;
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
iamroming
Forum Commoner
Posts: 27
Joined: Sun Jan 31, 2010 4:02 am
Location: Hyderabad, India
Contact:

Re: php mysql subtotal script required

Post by iamroming »

Dear Sir,
my query is SELECT vehicleno,amount,date,SUM(amount) from vehicles order by vehicleno asc,date desc
and it display this

Sub 1000

Sir i need the output like this
vehicle no. amount date
1111 150 2012-03-24
1111 150 2012-03-25
Sub 300 ------------------------- this line from either php or mysql code(iwant)
2222 350 2012-03-24
2222 350 2012-03-25
Sub 700 ------------------------- this line from either php or mysql code(iwant)
Grand = 1000
and my php code is
$sql="select vehicleno,amount, date,SUM('amount') from vehicles "
$res=mysql-query($res);
while($row=mysql_fetch_array($res))
{
//Result;
}
it echoes as
vehicle no. amount date
1111 150 2012-03-24
2222 350 2012-03-24
1111 150 2012-03-25
2222 350 2012-03-25

Grand 1000
help required
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: php mysql subtotal script required

Post by social_experiment »

Code: Select all

<?php
while($row=mysql_fetch_array($res))
{
//Result; 
 //select the sum in here;
 $sumQry = "SELECT SUM(amount) FROM vehicles WHERE vehicleno = '" . $row['vehicleno'] . "' GROUP BY vehicleno";

}
?>
Try the code above
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
iamroming
Forum Commoner
Posts: 27
Joined: Sun Jan 31, 2010 4:02 am
Location: Hyderabad, India
Contact:

Re: php mysql subtotal script required

Post by iamroming »

thanks a lot i will try it
iamroming
Forum Commoner
Posts: 27
Joined: Sun Jan 31, 2010 4:02 am
Location: Hyderabad, India
Contact:

Re: php mysql subtotal script required

Post by iamroming »

thanks social the script u supplied
<?php
while($row=mysql_fetch_array($res))
{
//Result;
//select the sum in here;
$sumQry = "SELECT SUM(amount) FROM vehicles WHERE vehicleno = '" . $row['vehicleno'] . "' GROUP BY vehicleno";

}
?>
Out puts the following
--------------------------------------------
vehicle no. amount date
1111 150 2012-03-24
Sub 300
1111 150 2012-03-25
Sub 300
2222 350 2012-03-24
Sub 300
2222 350 2012-03-25
Sub 300

sub after each row but iam confused how to write the code for the subtotal below the group by vehicleno
shaunbip
Forum Newbie
Posts: 1
Joined: Wed Mar 28, 2012 1:53 pm

Re: php mysql subtotal script required

Post by shaunbip »

:D :D :D :D :D :D :D :D :D :D :( :( :( :( :( :o :o :o :o 8O 8O 8O 8O 8O 8O 8O 8O 8O
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: php mysql subtotal script required

Post by social_experiment »

This is a quick solution which can probably be improved on. Replace the fields i used with the ones in your table

Code: Select all

<?php
 $sql = "SELECT `vehicleno`, `amount`, `dateCol` FROM `vehicle`";
 $qry = mysql_query($sql);

 //
 echo '<table>';
 // count the amount of rows for each vehicle number
 while ($row = mysql_fetch_array($qry)) {
     $cQry = "SELECT COUNT(`id`) FROM `vehicle` WHERE `vehicleno` = '" . $row['vehicleno'] . "' ";
     $cSql = mysql_query($cQry);
     $cAry = mysql_fetch_array($cSql);
     $rows = $cAry[0];

      echo '<tr>';
      echo '<td>' . $row['vehicleno'] . '</td>';
      echo '<td>' . $row['amount'] . '</td>';
      echo '<td>' . $row['dateCol'] . '</td>';
      // add the amount in $row['amount'] into an array
      // each time the iteration occurs
      $total[] = $row['amount'];

      // if the amount of rows of a specific vehicle number 
      // is equal to the amount of elements in the array it 
      // means the "last" record of a specific group is 
      // reached
      if (count($total) == $rows) {
          echo '<td>' . array_sum($total) . '</td>';
          // clear the array contained in $total or the following 
          // amounts wont be displayed
          $total = array();
      }
      echo '</tr>';
 }
 echo '</table>';     
?>
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
iamroming
Forum Commoner
Posts: 27
Joined: Sun Jan 31, 2010 4:02 am
Location: Hyderabad, India
Contact:

Re: php mysql subtotal script required

Post by iamroming »

Thanks a lot for giving full , thank u very much
Post Reply