TO INSERT JSON DATA INTO DATABASE ORACLE

 

SUPPOSE YOU WANT TO INSERT BELOW DATA INTO DATABASE

http://localhost/sip/insert/formDataInsert?coach_type=GS&shell_no=9999&prod_yr=2022&form_number=1212&app_drg_no=0000&app_wi_no=1111&mod_carried_out=N&trial_items_fitted=N&remarks=TEST&tracelists=[{"traceValue":"a","traceCode":"2.1"},{"traceValue":"b","traceCode":"2.2"},{"traceValue":"c","traceCode":"2.3"}]&obserlists=[{"obserFlag":"O","obserCode":"3.1"},{"obserFlag":"O","obserCode":"3.2"},{"obserFlag":"O","obserCode":"3.3"},{"obserFlag":"A","obserCode":"3.4"},{"obserFlag":"O","obserCode":"3.5"},{"obserFlag":"O","obserCode":"3.6"},{"obserFlag":"A","obserCode":"3.7"}]

PHP SCRIPT BACKGROUND 

<?php

$db = oci_connect('user', 'password', 'string');

//SUPPOSE WE ARE COLLECTING DATA LIKE BELOW

 $response = array();

$coach_type=$_REQUEST['coach_type'];

$shell_no=$_REQUEST['shell_no'];

$prod_yr=$_REQUEST['prod_yr'];

$app_drg_no=$_REQUEST['app_drg_no'];

$form_number=$_REQUEST['form_number'];

$app_wi_no=$_REQUEST['app_wi_no'];

$mod_carried_out=$_REQUEST['mod_carried_out'];

$trial_items_fitted=$_REQUEST['trial_items_fitted'];

$remarks=$_REQUEST['remarks'];

$tracelists=$_REQUEST['tracelists'];   //JSON VALUE HERE 

$obserlists=$_REQUEST['obserlists']; //JSON VALUE HERE

$traceArray = json_decode($tracelists, true); //Convert JSON String into PHP Array

$obserArray = json_decode($obserlists, true);

$countHeader=0;

$countTrace=0;

$countObser=0;

$sqlHeader="INSERT INTO TABLE_NAME(COACH_TYPE,SHELL_NO,PROD_YR,QC_STAGE_CD,FORM_NO,APP_DRG_NO,APP_WI_NO,MOD_CARRIED_OUT,TRIAL_ITEMS_FITTED,REMARKS)

VALUES('$coach_type','$shell_no','$prod_yr','1','$form_number','$app_drg_no','$app_wi_no','$mod_carried_out','$trial_items_fitted','$remarks')";;

$compiled = oci_parse($db, $sqlHeader);

$rHeader = oci_execute($compiled, OCI_NO_AUTO_COMMIT);

$countHeader=oci_num_rows($compiled);

if ($countHeader=1)

{

          foreach($traceArray as $row) //Extract the Array Values by using Foreach Loop

          {

  // Make Multiple Insert Query 

$sql="INSERT INTO PS_SIP_TRAC_MAKE_APP (COACH_TYPE, SHELL_NO, PROD_YR, QC_STAGE_CD, FORM_NO, TRACE_NO, TRACE_DATA)

VALUES('$coach_type','$shell_no','$prod_yr','1','$form_number','".$row["traceCode"]."','".$row["traceValue"]."')";

        

$compiled = oci_parse($db, $sql);

$countTrace++;

$rTrace = oci_execute($compiled, OCI_NO_AUTO_COMMIT);

  }

  

//INSERT 2nd LOOP VALUE after    1st Loop

  foreach($obserArray as $row) //Extract the Array Values by using Foreach Loop

          {

  // Make Multiple Insert Query 

$sql="INSERT INTO TABLE_NAME_UR (COACH_TYPE, SHELL_NO, PROD_YR, QC_STAGE_CD, FORM_NO, OBSER_CD, OBSER_FLAG)

VALUES('$coach_type','$shell_no','$prod_yr','1','$form_number','".$row["obserCode"]."','".$row["obserFlag"]."')";

$compiled = oci_parse($db, $sql);

        $countObser++;

       $rObser = oci_execute($compiled, OCI_NO_AUTO_COMMIT);

  }

   

  

  if (!$rTrace OR !$rObser OR !$rHeader)

 {    

    $e = oci_error($compiled);

    oci_rollback($db);  // rollback changes to both tables

    trigger_error(htmlentities($e['message']), E_USER_ERROR);

      }

}   

    

$result = oci_commit($db);

if (!$result) {

    $err = oci_error($db);

    trigger_error(htmlentities($err['message']), E_USER_ERROR);

} 

/* echo $countHeader." rows inserted in PS_SIP_FORM_APP "; 

echo $countTrace." rows inserted in PS_SIP_TRAC_MAKE_APP ";

echo $countObser." rows inserted in PS_SIP_OBSER_APP"; */

  if  ($result)

{

$response["success"] = $countHeader;

$response["message"] = "Registered successfully.";

          echo json_encode($response);

} 

else 

{

        $response["success"] = $countHeader;

        $response["message"] = "There seems to be problem";

        echo json_encode($response);

}

?>