Update row if exists or insert

I’m working on a web based PHP form that’s currently connect to an Oracle database. I’m trying to get the functionality working so that after a user submits the form it’ll check if a row exists based on the Job Number, if it does then update, otherwise insert the values.

It’s bring back an internal server error at the moment and I can’t quite see why.

Below is the code snippet…

<?php

//connect to database
$DBC = oci_connect("username", "password", "server");

set_time_limit ( 120 );


$jobNumber = $_POST['jobNumber'];

$C1utShortAmount = $_POST['1CutShortAmount'];
$1Scrap = $_POST['1Scrap'];
$1Repair = $_POST['1Repair'];
$1TotalQty = $_POST['1TotalQty'];

$2Scrap = $_POST['2Scrap'];
$2RaisedParts = $_POST['2RaisedParts'];
$2Repair = $_POST['2Repair'];
$2TotalQty = $_POST['2TotalQty'];

$3Repair = $_POST['3Repair'];
$3Scrap = $_POST['3Scrap'];
$3Repair = $_POST['3SmtRepair'];
$3RaisedParts = $_POST['3RaisedParts'];
$3TotalQty = $_POST['3TotalQty'];

$createdBy = $_POST['usersname'];

$sqlSelect = "SELECT * FROM wip.table1 WHERE JOB_NUMBER = '$jobNumber'";

$SQL = "INSERT INTO wip.table1
   ( job_number,
     1_Cut_Short_Amount,
     1_Scrap,
     1_Repair,
     1_Total_Qty,
     2_Scrap,
     2_Raised_Parts,
     2_Repair,
     2_Total_Qty,
     3_Repair,
     3_Scrap,
     3_Repair,
     3_Raised_Parts,
     3_Total_Qty
  )
    VALUES (
      '$jobNumber',
      '$1CutShortAmount',
      '$1Scrap',
      '$1Repair',
      '$1TotalQty',
      '$2Scrap',
      '$2RaisedParts',
      '$2Repair',
      '$2TotalQty',
      '$3Repair',
      '$3Scrap',
      '$3Repair',
      '$3RaisedParts',
      '$3TotalQty'
   )";

   $sql2 = "UPDATE wip.table1
            SET 1CUT_SHORT_AMOUNT = '$1CutShortAmount',
                1SCRAP = '$1Scrap',
                1REPAIR = '$1Repair',
                1TOTAL_QTY = '$1TotalQty',
                2SCRAP = '$2Scrap',
                2RAISED_PARTS = '$2RaisedParts',
                2SMT_REPAIR = '$2Repair',
                2TOTAL_QTY = '$2TotalQty',
                3REPAIR = '$3Repair',
                3SCRAP = '$3eScrap',
                3SMT_REPAIR = '$3SmtRepair',
                3RAISED_PARTS = '$3RaisedParts',
                3TOTAL_QTY = '$3TotalQty'
            WHERE JOB_NUMBER LIKE '$jobNumber'";

   if(oci_num_rows($sqlSelect) > 0){
     $stmt = oci_parse($DBC,$SQL2);

     $rc = oci_execute($stmt);
      if (!$rc)
      {
      $error = oci_error($stmt);
      var_dump($error);
      }

      oci_free_statement($stmt);
   }
   else
   {
     $stmt1 = oci_parse($DBC,$SQL);

     $rc = oci_execute($stmt1);
      if (!$rc)
      {
      $error = oci_error($stmt1);
      var_dump($error);
      }

      oci_free_statement($stmt1);
   }

Answer

In Oracle, you need to first parse, execute and then fetch before num_rows.

<?php

$stmt = oci_parse($DBC,$sqlSelect);

$rc = oci_execute($stmt);
$row=oci_fetch_array($stmt);
$jobExists=oci_num_rows($stmt)>0; 

   if($jobExists){
     $stmt = oci_parse($DBC,$SQL2);

     $rc = oci_execute($stmt);
      if (!$rc)
      {
      $error = oci_error($stmt);
      var_dump($error);
      }

      oci_free_statement($stmt);
   }
   else {
     $stmt1 = oci_parse($DBC,$SQL);
     $rc = oci_execute($stmt1);

      if (!$rc) {

        $error = oci_error($stmt1);
        var_dump($error);

      }

      oci_free_statement($stmt1);
   } 

?>

Advertisement

Answer