grouping data in php page

[Maideen]

hi
I have created the php report using below code. It works fine. But My problem is to group the data
example

# Date          Code   Name       Rate  Qty  Amount# 
 15.01.2011     0001   Milk       4.01  50    200.50
 15.01.2011     0125   Choklet   30.00  10    300.00
 15.01.2011     0241   Drink     12.50  12    150.00
 16.01.2011     5461   Meat      35.07  10    350.75
 16.01.2011     4587   Fish      17.80   5     89.00
                                    total    1090.25     

if I take the date range from 15.01.2011 to 16.01.2011, it shows in php page look like above. But if i select
the date range as 15.01.2011 to 16.01.2011, i need look like below

Pls help me

 # Date          Code   Name       Rate  Qty  Amount# 
 15.01.2011     0001   Milk       4.01  50    200.50
 15.01.2011     0125   Choklet   30.00  10    300.00
 15.01.2011     0241   Drink     12.50  12    150.00
                             subtotal         650.50

 16.01.2011     5461   Meat      35.07  10    350.75
 16.01.2011     4587   Fish      17.80   5     89.00
                             subtotal         439.75 

                             grand total     1090.25 

it is my code

    `<?php //include '../templete/header.php'; ?>
    <script language="javascript" type="text/javascript">
     function printFunction(){

     window.print();
     }
    </script>

    <script language="javascript" type="text/javascript">

    function PrintGridData() {
        var prtGrid = document.getElementById('<%=txtDocNo%>');
        prtGrid.border = 0;
                            var prtwin = window.open('','PrintGridViewData','left=100,top=100,width=1000,height=1000,tollbar=0,scrollbars=1,status=0,resizable=1');
        prtwin.document.write(prtGrid.outerHTML);
        prtwin.document.close();
        prtwin.focus();
        prtwin.print();
        prtwin.close();
     </script>

    <table width="100%" align="center" cellpadding="4" cellspacing="1" class=tbl_table">
      <tr>
        <td class="tbl_header">SO Date</td>
        <td class="tbl_header">MV CODE</td>
        <td class="tbl_header">MV NAME</td>
        <td class="tbl_header">RATE</td>
        <td class="tbl_header">SUPP.QTY</td>
        <td class="tbl_header">AMT</td>
    </tr>
    <?php 
      if(isset($stmt))
        { 
            while($row = $stmt->fetch())
        {?>
<tr>
<td class="tbl_content"><?php echo date("d-m-Y", strtotime($row['SODate']));?></td>
  <td class="tbl_content"><?php echo $row['MVCode'];?></td>
  <td class="tbl_content"><?php echo $row['MVName'];?></td>

  <td class="tbl_content_right"><?php echo number_format($row['Rate'],2) ;?></td>
  <td class="tbl_content_right"><?php echo number_format($row['Qty']) ;?></td>
  <td class="tbl_content_right"><?php echo number_format($row['BalAmt'],2) ;?></td>
  </tr>

<?php 
    $balamt+=$row['BalAmt'];
    $qty+=$row['Qty'];

}}?> 

    <tr><td colspan="9"><hr /></tr>

    <tr>  

  <td colspan="5"></td>
  <td class="tbl_content_total">  <?php echo number_format($qty);?></td>
  <td class="tbl_content_total">  <?php echo number_format($rtnqty);?></td>
  <td class="tbl_content_total">  <?php echo number_format($balqty);?></td>
  <td class="tbl_content_total">  <?php echo number_format($balamt,2);?></td>

    </tr>
    </table>


    <?php unset($dbh); unset($stmt); ?>

    <?php
    include '../templete/footer.php';
    ?>

'
Regards

Maideen

[mrvijayakumar]

Compare the stored Date field & the Date in loop from/to report requried using IF stmt. Add all the Amount as seperate variables for each date & show the result.

Note: Above point may increase CPU usage.

[paulkd]

Can you post all your PHP code, but NOT your mysqli credentials.

[Maideen]

Hi paulkd

Below i have given all form i am using. it is working fine. it will be look likes the below
example

    # Date          Code   Name       Rate  Qty  Amount# 
     15.01.2011     0001   Milk       4.01  50    200.50
     15.01.2011     0125   Choklet   30.00  10    300.00
     15.01.2011     0241   Drink     12.50  12    150.00
     16.01.2011     5461   Meat      35.07  10    350.75
     16.01.2011     4587   Fish      17.80   5     89.00
                                        total    1090.25     

But if I take the date range from 15.01.2011 to 16.01.2011, it shows in php page look like above. But if i select the date range as 15.01.2011 to 16.01.2011, i need look like below

Pls help me

     # Date          Code   Name       Rate  Qty  Amount# 
     15.01.2011     0001   Milk       4.01  50    200.50
     15.01.2011     0125   Choklet   30.00  10    300.00
     15.01.2011     0241   Drink     12.50  12    150.00
                                 subtotal         650.50
     16.01.2011     5461   Meat      35.07  10    350.75
     16.01.2011     4587   Fish      17.80   5     89.00
                                 subtotal         439.75 
                                 grand total     1090.25 

this is my index.php

        <?php
        include_once '../inc/connection.inc.php';
        ?>

    <?php
    /*
    try {
           $stmt = $dbh->prepare('SELECT * FROM MVendorMaster order by MVName');
           $stmt->execute();
        } 
    catch (PDOException $e) 
        {
           $output = 'Error fetching authors from database!';
           include '../errormsg.php';
           exit();
        }

    foreach ($stmt as $row) 
        {
           $mvcode[] = array('MVCode' => $row['MVCode'], 'MVName' => $row['MVName']);
        }
     * 
     */
    include 'searchform.html.php';
    ?>

    <?php
    if (isset($_POST['datefrom']) && $_POST['dateto']  != "" )
        {
            $datefrom=$_POST["datefrom"];
            $dateto=$_POST["dateto"];
            $stmt = $dbh->query("SELECT * FROM InvoiceHead WHERE SODate>='$datefrom' and SODate<='$dateto' order by SODate");  
    $stmt->setFetchMode(PDO::FETCH_ASSOC);  
 }
include 'view.html.php';
exit();

?>

it is my searchform.html.php

    <?php
    include '../templete/header.php';
    ?>
    <form action="" method="post">
            <table>

                  </tr>
                  <tr>
                    <td>Date[From]:</td>
                    <td><input type="text" id="datepicker1" name="datefrom" /></td>
                  </tr>
                  <tr>
                    <td>Date[To]:</td>
                    <td><input type="text" id="datepicker2" name="dateto" /></td>
                  </tr>    


            </table>

            <div>

                <input type="submit" value="Search">
            </div>
    </form>

it is my view result page view.html.php

     <table width="100%" align="center" cellpadding="4" cellspacing="1" class=tbl_table">
      <tr>
        <td class="tbl_header">MV CODE</td>
        <td class="tbl_header">MV NAME</td>
        <td class="tbl_header">SONO</td>
        <td class="tbl_header">SO Date</td>
        <td class="tbl_header">RATE</td>
        <td class="tbl_header">SUPP.QTY</td>
        <td class="tbl_header">RTN.QTY</td>
        <td class="tbl_header">BAL.Qty</td>
        <td class="tbl_header">SOLD AMT</td>



        <td class="tbl_header">Actions</td>


      </tr>
            <?php if(isset($stmt))
                { 
                    while($row = $stmt->fetch())
                {?>
        <tr>
          <td class="tbl_content"><?php echo $row['MVCode'];?></td>
          <td class="tbl_content"><?php echo $row['MVName'];?></td>
          <td class="tbl_content"><?php echo $row['SONo'];?></td>
          <td class="tbl_content"><?php echo date("d-m-Y", strtotime($row['SODate']));?></td>
          <td class="tbl_content_right"><?php echo number_format($row['Rate'],2) ;?></td>
          <td class="tbl_content_right"><?php echo number_format($row['Qty']) ;?></td>
          <td class="tbl_content_right"><?php echo number_format($row['RTNQty']) ;?></td>
          <td class="tbl_content_right"><?php echo number_format($row['BalQty']) ;?></td>
          <td class="tbl_content_right"><?php echo number_format($row['BalAmt'],2) ;?></td>

        </tr>
            <?php 
            $balamt+=$row['BalAmt'];
            $balqty+=$row['BalQty'];
            $rtnqty+=$row['RTNQty'];
            $qty+=$row['Qty'];

        }}?> 




    <tr><td colspan="9"><hr /></tr>

    <tr>  

          <td></td>
          <td></td>
          <td></td>
          <td></td>
          <td></td>
          <td class="tbl_content_total">  <?php echo number_format($qty);?></td>
          <td class="tbl_content_total">  <?php echo number_format($rtnqty);?></td>
          <td class="tbl_content_total">  <?php echo number_format($balqty);?></td>
          <td class="tbl_content_total">  <?php echo number_format($balamt,2);?></td>

    </tr>
    </table>
    <?php unset($dbh); unset($stmt); ?>

    <?php
    include '../templete/footer.php';
    ?>

it is my header.php

    <?php 
    include '../inc/connection.inc.php';
    include_once '../inc/html_function.php';

    ?>

    <!DOCTYPE html>
    <html lang="en">
    <!--<META HTTP-EQUIV="REFRESH" CONTENT="120">-->
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
        <title>HCK reporting Service-PHP</title>

        <link rel="shortcut icon" href="../image/102.ICO" />
        <link rel="stylesheet" type="text/css" href="../css/style.css">
        <!-- <link rel="stylesheet" type="text/css" href="../css/pmk.css">-->
        <link rel="stylesheet" type="text/css" href="../listv1/pmk.css">

        <link rel="stylesheet" href="http://code.jquery.com/ui/1.10.2/themes/smoothness/jquery-ui.css" />
        <link href="../css/styles_menu.css" rel="stylesheet" type="text/css">


        <script src="http://code.jquery.com/jquery-1.9.1.js"></script>
        <script src="http://code.jquery.com/ui/1.10.2/jquery-ui.js"></script>
        <link rel="stylesheet" href="/resources/demos/style.css" />
        <!--<a href="http://www.silsnetwork.com"><img id="logo" src="../image/Letter-Hade-Banner.png" alt="sils" /></a>-->





         <script>
              $(function() {
                $( "#datepicker1" ).datepicker({
                    showWeek: true, firstDay: 1 });
                $( "#datepicker2" ).datepicker({
                    showWeek: true, firstDay: 1});
              });
        </script>

    <?php include '../menu/menu.php';?>

    </head>

    <body>

it is my connection connection.inc.php

    <?php
      try {
            $hostname = "host";            //host
            $dbname = "dbname";            //db name
            $username = "login";            // username like 'sa'
            $pw = "password";                // password for the user
            $dbh = new PDO ("mssql:host=$hostname;dbname=$dbname","$username","$pw");
        } 

      catch (PDOException $e) 
        {
            echo "I'm sorry. I'm afraid I can't do that " . $e->getMessage() . "\n";
            file_put_contents('PDOErrors.txt', $e->getMessage(), FILE_APPEND);
            exit;
        }
    ?>

it is my fucntiong html_function.php

        <?php
            function html($text)
                {
                    return htmlspecialchars($text, ENT_QUOTES, 'UTF-8');
                }
            function htmlout($text)
                {
                echo html($text);
                }


            function markdown2html($text)
                {
                    $text = html($text);

                    // strong emphasis
                    $text = preg_replace('/__(.+?)__/s', '<strong>$1</strong>', $text);
                    $text = preg_replace('/\*\*(.+?)\*\*/s', '<strong>$1</strong>', $text);

                    // emphasis
                    $text = preg_replace('/_([^_]+)_/', '<em>$1</em>', $text);
                    $text = preg_replace('/\*([^\*]+)\*/', '<em>$1</em>', $text);

                    // Convert Windows (\r\n) to Unix (\n)
                    $text = str_replace("\r\n", "\n", $text);

                    // Convert Macintosh (\r) to Unix (\n)
                    $text = str_replace("\r", "\n", $text);

                    // Paragraphs
                    $text = '<p>' . str_replace("\n\n", '</p><p>', $text) . '</p>';

                    // Line breaks
                    $text = str_replace("\n", '<br>', $text);            

                    // [linked text](link URL)
                    $text = preg_replace('/\[([^\]]+)]\(([-a-z0-9._~:\/?#@!$&\'()*+,;=%]+)\)/i','<a href="$2">$1</a>', $text);

                    return $text;
                }

            function markdownout($text)
                {
                    echo markdown2html($text);
                }

        ?>

Regards

maideen

[Maideen]

mr.vijay kumar

I have tried, I could not result and not responing. Pls check the code i have posted just now...

maideen

[paulkd]

This code is based on your original code - to replace lines 22-67.

<?php  
$currentSODate = '';
$displaySubTotal = FALSE;
$subTotal = 0;
$grandTotal = 0;
?>
    <table width="100%" align="center" cellpadding="4" cellspacing="1" class=tbl_table">
        <tr>
            <td class="tbl_header">SO Date</td>
            <td class="tbl_header">MV CODE</td>
            <td class="tbl_header">MV NAME</td>
            <td class="tbl_header">RATE</td>
            <td class="tbl_header">SUPP.QTY</td>
            <td class="tbl_header">AMT</td>
        </tr>
        <?php while($row = $stmt->fetch()): ?>
            <?php if($currentSODate!=$row['SODate']): ?>
                <?php if($displaySubTotal): ?>
                    <tr>
                        <td colspan="4">
                        <td><b>subtotal</b></td>
                        <td><b><?php echo number_format($subTotal,2); ?></b></td>
                    </tr>
                    <?php $grandTotal += $subTotal; ?>
                    <?php $subTotal = 0; ?>
                <?php else: ?>
                    <?php $displaySubTotal = TRUE; ?>
                <?php endif; ?>
                <?php $currentSODate = $row['SODate']; ?>
            <?php endif; ?>
            <tr>
                <td class="tbl_content"><?php echo date("d-m-Y", strtotime($row['SODate']));?></td>
                <td class="tbl_content"><?php echo $row['MVCode'];?></td>
                <td class="tbl_content"><?php echo $row['MVName'];?></td>
                <td class="tbl_content_right"><?php echo number_format($row['Rate'],2) ;?></td>
                <td class="tbl_content_right"><?php echo number_format($row['Qty']) ;?></td>
                <td class="tbl_content_right"><?php echo number_format($row['BalAmt'],2) ;?></td>
            </tr>
            <?php $subTotal += $row['BalAmt']; ?>
        <?php endwhile; ?>
        <?php $grandTotal += $subTotal; ?>
        <tr>
            <td colspan="4">
            <td><b>subtotal</b></td>
            <td><b><?php echo number_format($subTotal,2); ?></b></td>
        </tr>
        <tr>
            <td colspan="4">
            <td><b>grand total</b></td>
            <td><b><?php echo number_format($grandTotal,2); ?></b></td>
        </tr>
    </table>
   <?php unset($dbh); unset($stmt); ?>

[Maideen]

Hi Paulkd

Fantastic. You have solved my problem. Thank you very much.

Once again, I thank you very much.

Regards

Maideen