John Davidson

arrays - Two Different Size Queries is not display record parallel Or Front .I want to display record Paralle in Php with Oracle

0 comments
Message:


Here is code given a varaiable of contract_n is coming from json or jquery


$contract_n = $_POST["contract_n"]; 

if(!empty($contract_n)){

 $date1 = '01-JAN-2000';

 $date2 = '31-DEC-2099';

}else{

$date1 = $_POST["date1"];

$date2 = $_POST["date2"];

}

$date1 = date("d-M-Y", strtotime($date1));

$date2 = date("d-M-Y", strtotime($date2));  





$date1 = strtoupper(substr($date1,0));

$date2 = strtoupper(substr($date2,0));



$main_q = "

SELECT   CONTRACT#,1 sr,CONTRACTDATE,SALEMANID,SALEMANNAME,CUSTOMERID,CUSTOMERNAME, SHIP_DATE,CONSTRUCTIONID,DESCRIPTION,LOOMABRV,WEAVENAME,

SUM(SHIP_MTR) SHIP_MTR,

MAINPRODUCTNAME,  SHIP_QTY ,WEB_Y_N

FROM



V_SALECONTRACT_MTR

WHERE  CONTRACTDATE BETWEEN '$date1' AND '$date2'

AND CONTRACT# = NVL('$contract_n',CONTRACT#)

GROUP BY CONTRACT#,CONTRACTDATE,SALEMANID,SALEMANNAME,CUSTOMERID,CUSTOMERNAME, SHIP_DATE,CONSTRUCTIONID,DESCRIPTION,LOOMABRV,WEAVENAME,

MAINPRODUCTNAME,ship_QTY,WEB_Y_N

";

Here is I take sub total


 $query = "



$main_q



UNION ALL

SELECT  CONTRACT#,2 SR, NULL CONTRACTDATE,NULL SALEMANID,NULL SALEMANNAME,NULL CUSTOMERID,NULL CUSTOMERNAME, SHIP_DATE SHIP_DATE,NULL CONSTRUCTIONID, NULL 

DESCRIPTION,NULL LOOMABRV,NULL WEAVENAME, SUM(SHIP_MTR) SHIP_MTR,

NULL MAINPRODUCTNAME,  SUM(SHIP_QTY) SHIP_QTY , NULL WEB_Y_N FROM ($main_q)

GROUP BY CONTRACT#, SHIP_DATE 

UNION ALL

SELECT   CONTRACT#,3 SR,NULL CONTRACTDATE,NULL SALEMANID,NULL SALEMANNAME,NULL CUSTOMERID,NULL CUSTOMERNAME,NULL SHIP_DATE,NULL CONSTRUCTIONID, NULL 

DESCRIPTION,NULL LOOMABRV,NULL WEAVENAME, SUM(SHIP_MTR) SHIP_MTR,

NULL MAINPRODUCTNAME, SUM(SHIP_QTY) SHIP_QTY ,  NULL WEB_Y_N

FROM ($main_q)

GROUP BY CONTRACT# 

ORDER BY CONTRACT# ,SHIP_DATE,SR

";

$stid = oci_parse($conn, $query);

oci_execute($stid);

2nd Query


 $q_2 = "

SELECT A.CONTRACT#,1 SR2 ,C.REQDATE,DESCRIPTION DESCRIPTION2,WEAVENAME WEAVENAME2,l.loomabrv loomabrv2,A.DEMAND#,C.QUANTITY





FROM 

DEMANDMST A,DEMANDDTL1 B,DEMANDDTL2 C,v_construction v,weave w,loom l , CONTRACTMST T

WHERE A.DEMAND#=B.DEMAND# AND

    B.DEMAND#=C.DEMAND# AND

    B.ROW_ID=C.ROW_ID AND 

    b.SCONSTRUCTIONID=v.CONSTRUCTIONID and 

    b.WEAVEID=w.WEAVEID and

    b.LOOMID=l.LOOMID

    and a.contract#= t.contract#

    and T.CONTRACTDATE BETWEEN '$date1' and '$date2'

   AND a.contract# = NVL('$contract_n',A.CONTRACT#)

--Group by Reqdate and contract#

UNION ALL 

SELECT A.CONTRACT# ,2 SR2 ,C.REQDATE,NULL DESCRIPTION,NULL WEAVENAME,NULL loomabrv,NULL DEMAND#,SUM(C.QUANTITY) QUANTITY





FROM 

DEMANDMST A,DEMANDDTL1 B,DEMANDDTL2 C,v_construction v,weave w,loom l , CONTRACTMST T

WHERE A.DEMAND#=B.DEMAND# AND

    B.DEMAND#=C.DEMAND# AND

    B.ROW_ID=C.ROW_ID AND 

    b.SCONSTRUCTIONID=v.CONSTRUCTIONID and 

    b.WEAVEID=w.WEAVEID and

    b.LOOMID=l.LOOMID

    AND a.contract# = NVL('$contract_n',A.CONTRACT#)

   and a.contract#= t.contract#

    and T.CONTRACTDATE BETWEEN '$date1' and '$date2'

    GROUP BY A.CONTRACT# , C.REQDATE 

    --Group By Contract Date

    UNION ALL 

    SELECT A.CONTRACT# ,3 SR2 ,NULL REQDATE,NULL DESCRIPTION,NULL WEAVENAME,NULL loomabrv,NULL DEMAND#,SUM(C.QUANTITY) QUANTITY





FROM 

DEMANDMST A,DEMANDDTL1 B,DEMANDDTL2 C,v_construction v,weave w,loom l , CONTRACTMST T

WHERE A.DEMAND#=B.DEMAND# AND

    B.DEMAND#=C.DEMAND# AND

    B.ROW_ID=C.ROW_ID AND 

    b.SCONSTRUCTIONID=v.CONSTRUCTIONID and 

    b.WEAVEID=w.WEAVEID and

    b.LOOMID=l.LOOMID

    AND a.contract# = NVL('$contract_n',A.CONTRACT#)

   and a.contract#= t.contract#

    and T.CONTRACTDATE BETWEEN '$date1' and '$date2'

    GROUP BY A.CONTRACT# 

    ORDER BY REQDATE 

 ";

 $stid2 = oci_parse($conn,  $q_2);

oci_execute($stid2);

?>

<span id="status"></span>

<br>

<table id="contract_req_mtr" style="padding-top: 20px;">

      <thead>

<tr style="text-align: center;">

       

       <th>Sales </br> Contarct #</th>

       <th>Dated</th>

       <th>Manager</th>

        <th>Customer</th>

         

          <th>Ship Date</th>

           <th>Quality</th>

           <th>Weave</th>

           <th>Loom</th>

         <!--  <th>Process</th> -->

           <th>Mtrs</th>

           <th>Product</th>

            <!--   <th>Consumption</th>-->

           <th style="border-right: 3px solid lightblue;">Qty</th>

           <th>Required Date</th>

           <th>Quality</th>

           <th>Weave</th>

           <th>Loom</th>

           <th>Demand No</th>

           <th>Qty</th>

      

    </tr>

      </thead>

    <tbody>



        <?php





        $prev_contract = NUll;

        $prev_shipdate = NUll;

        $prev_reqDate = NULL;

        $t_shipMtr = 0;

        $t_shipQty = 0;

        $t_Qty = 0;

        $row_1 = []; 

        while($row = oci_fetch_array($stid, OCI_ASSOC)) 

       {

        $row_1[] = $row;

       }

       $row_2 = [];

        while($row = oci_fetch_array($stid2, OCI_ASSOC)) 

       {

        $row_2[] = $row;

       }

Merge Two array Here


//

$results = array(); // define an array that will contain all the rows



    //iterate on the first array assuming its name is $array1

foreach($row_1 as $val) {

            //add the current line named $val to the resulting array

    $results[] = $val;

}

    //iterate on the first array assuming its name is $array1

foreach($row_2 as $val) {

            //add the current line named $val to the resulting array

    $results[] = $val;

}



//$results = [...$row_1 ,...$row_2];

Sorting of two combine array below


usort($results, function($a, $b) {

 

    return [$b['CONTRACT#'] ?? '', $b['SHIP_DATE'] ?? '' ,$a['SR'] ?? '', $b['REQDATE'] ?? '' , $b['DESCRIPTION2'] ?? '']

           <=>

           [$a['CONTRACT#'] ?? '', $a['SHIP_DATE'] ?? '' , $b['SR'] ?? '', $a['REQDATE'] ?? '' , $a['DESCRIPTION2'] ?? ''];

});

Display Result from array_merge varaiable $result


       foreach($results as $row){

 

            $g_contract = $row["CONTRACT#"] ?? '';

            $sr = $row["SR"] ?? '' ;

            $sr2 = $row["SR2"] ?? '';

            if($sr == 1){

                if(!empty($row["SHIP_MTR"])){

                $t_shipMtr += $row["SHIP_MTR"];

        }

             if(!empty($row["SHIP_QTY"])){

                $t_shipQty += $row["SHIP_QTY"]; 

            }

            }

           

                if($sr2 == 1){

                $t_Qty += $row["QUANTITY"]; 

            }

            

       ?>

               <tr  style="

<?php if($sr == 3){ ?>

    border-bottom: 3px solid lightblue;

        <?php } ?>       ">

                <?php if($g_contract != $prev_contract) { 

                     

                    ?>

                 <td style="font-weight: bold; vertical-align: middle;"><?php echo $row["CONTRACT#"] ?? '' ; ?>

                   <input type="checkbox" name="web_y_n"  style="text-align: right;vertical-align: middle;margin-top: -2px;" 

                     class="web_y_n" id="web_y_n" contract_no="<?php echo $row["CONTRACT#"] ;



                      ?>"

                      <?php 

                          $web_y_n = $row["WEB_Y_N"] ?? ''; 

                       if($web_y_n == "YES"){ ?>

                            checked

                       <?php }else{



                       } ?> 

                     >

                 </td>

                 <td style="font-weight: bold;"><?php echo $row["CONTRACTDATE"] ?? '' ; ?></td>

                  <td style="font-weight: bold;"><?php echo $row["SALEMANNAME"] ?? ''; ?></td>

                   <td style="font-weight: bold;max-width: 200px;"><?php echo $row["CUSTOMERNAME"] ?? '' ; ?></td>

               <?php 

              $prev_contract = $g_contract;

             



                }else{

                    echo "<td colspan='4'></td>";

    



                } 

                

                    $ship_date = $row["SHIP_DATE"] ?? '';

              

             $g_shipdate = $g_contract.$ship_date;

         

                if($g_shipdate != $prev_shipdate) { ?>

        <td style="font-weight: bold;"><?php echo $row["SHIP_DATE"] ?? '' ; ?></td>



  <?php 

  $prev_shipdate = $g_shipdate;

   }else{

    echo "<td></td>";

  } 



  ?>

        <td><?php echo $row["DESCRIPTION"] ?? ''; ?></td>

         <td><?php echo $row["WEAVENAME"] ?? ''; ?></td>

         <?php   ?>

          <td><?php 

          

          if($sr  == 2){

            echo "<span style='font-weight:bold;'>SubTotal</span>";

          }elseif ($sr == 3) {

            echo "<span style='font-weight:bold;'>Contract Total</span>";

          }else{

          echo $row["LOOMABRV"] ?? ''; 

      }?></td>

          

         <td style= "text-align: right;

               <?php  if($sr  == 2 || $sr==3 ){ ?>

             font-weight: bold;

               <?php } ?>

         "><?php echo isset($row["SHIP_MTR"]) ? number_format($row["SHIP_MTR"]) : '' ; ?></td>

          <td><?php echo $row["MAINPRODUCTNAME"] ?? '';?></td>

          <td style="text-align: right;border-right: 3px solid lightblue;

            <?php  if($sr  == 2 || $sr==3 ){ ?>

             font-weight: bold;

               <?php } ?>

          "><?php echo isset($row["SHIP_QTY"]) ? number_format($row["SHIP_QTY"]) : ''; ?></td>

            



 

      <?php  

     



 $q_reqDate = $row["REQDATE"] ?? '';



      if($q_reqDate != $prev_reqDate) { 



        ?>

        

    <td style="font-weight:bold;text-align: center;"><?php echo $row["REQDATE"] ?? ''; ?></td>



<?php

 

$prev_reqDate = $q_reqDate;



}else{

    echo "<td></td>";

    

} 



?>

    <td><?php echo $row["DESCRIPTION2"] ?? '';  ?></td>

    <td><?php echo $row["WEAVENAME2"] ?? '';  ?></td>

    <td><?php echo $row["LOOMABRV2"] ?? '';  ?></td>

    <td><?php 

    if(empty($row["DEMAND#"]) && (!empty($row["QUANTITY"]))){

        if($sr2 == 2){

        echo "<span style='font-weight:bold;'>SubTotal</span>";

    }

     if($sr2 == 3){

        echo "<span style='font-weight:bold;'>Contract Total</span>";

     }

    }else{

    echo $row["DEMAND#"] ?? '';

    }  ?></td>

     <td style="text-align:right;

<?php if(empty($row["DEMAND#"]) && (!empty($row["QUANTITY"]))){ ?>

    font-weight: bold;

    <?php } ?>

     "><?php echo 

isset($row["QUANTITY"]) ? number_format($row["QUANTITY"]) : ''; ?></td>

</tr>

    <?php  } ?>

     <tr style="font-weight:bold;text-align: right;">

        <td colspan="8" style="padding-right: 10px;">Grand Total</td>

         <td ><?php echo number_format($t_shipMtr); ?></td>

         <td></td>

         <td><?php echo number_format($t_shipQty); ?></td>

         <td colspan="5"></td>

         <td><?php echo number_format($t_Qty); ?></td>

     </tr>

   </tbody>

</table>

</div>

[Here Is Screen shoot given .From 2nd Date or bold border in midle start 2nd Query it is not parallel to left reult.Please guide me how i display record right side record parallel or front to left ][1]
[1]: https://i.stack.imgur.com/0YWES.jpg


My Answer:
'contract_n'];

// Query to fetch records from two different tables
$query1 = "SELECT * FROM table1 WHERE contract_n = :contract_n";
$query2 = "SELECT * FROM table2 WHERE contract_n = :contract_n";

// Prepare statements
$stmt1 = $conn->prepare($query1);
$stmt2 = $conn->prepare($query2);

// Bind parameters
$stmt1->bindParam(':contract_n', $contract_n);
$stmt2->bindParam(':contract_n', $contract_n);

// Execute queries
$stmt1->execute();
$stmt2->execute();

// Fetch records from both tables
$result1 = $stmt1->fetchAll(PDO::FETCH_ASSOC);
$result2 = $stmt2->fetchAll(PDO::FETCH_ASSOC);

// Display records in parallel
foreach ($result1 as $row1) {
// Display record from table1
echo "Table 1 - " . $row1['column1'] . " " . $row1['column2'] . "
";
}

foreach ($result2 as $row2) {
// Display record from table2
echo "Table 2 - " . $row2['column1'] . " " . $row2['column2'] . "
";
}

Rate this post

5 of 5 based on 7462 votes

Comments



© 2024 Hayatsk.info - Personal Blogs Platform. All Rights Reserved.
Create blog  |  Privacy Policy  |  Terms & Conditions  |  Contact Us