CODEWITHSUNDEEP
phpsqloracle-databasevariablessql-insert
mat1986
mat1986

Reputation: 135

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);
   }

Upvotes: 0

Views: 123

Answers (3)

Amish
Amish

Reputation: 95

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);
   } 

?>

Upvotes: 1

Funk Forty Niner
Funk Forty Niner

Reputation: 74217

Besides the answer given by GMB, variables cannot start with an integer.

You can however use something like $_1 etc., just not $1 etc.

Consult the following on variables on PHP.net:

I quote from the manual:

Variables in PHP are represented by a dollar sign followed by the name of the variable. The variable name is case-sensitive.

Variable names follow the same rules as other labels in PHP. A valid variable name starts with a letter or underscore, followed by any number of letters, numbers, or underscores. As a regular expression, it would be expressed thus: ^[a-zA-Z_\x80-\xff][a-zA-Z0-9_\x80-\xff]*$

Use error reporting also:

Upvotes: 1

GMB
GMB

Reputation: 222432

Oracle has the MERGE syntax to perform such update/insert operations at once.

Here is an example based on 3 columns of your table:

merge into wip.table1 t              -- target table
using (                              -- source table: pass your parameters here
    select 
        :job_number job_number, 
        :1_cut_short_amount 1_cut_short_amount, 
        :1_scrap 1_scrap 
    from dual
) s
on (s.job_number = t.job_number)      -- define the "match" condition
when matched then                     -- a record already exists: update
    update set 
        t.1_cut_short_amount = s.1_cut_short_amount,
        t.1_scrap = s.1_scrap
when not matched then                 -- no record yet: insert
    insert(job_number, 1_cut_short_amount, 1_scrap)
    values(s.job_number, s.1_cut_short_amount, s.1_scrap)

Upvotes: 3

Related Questions