CODEWITHSUNDEEP
phpsqldb2
Geoff_S
Geoff_S

Reputation: 5107

DB2 Select and insert based on bound parameters in php script

I've been trying to slowly migrate a script that selects data, and inserts based on bound parameters into another table from mysql to db2.

I have most of it migrated, but this main portion is still failing to insert. My select is working, returning exactly what I expect. However, something is going wrong in the bit where I create the array or parameter values. I"m simply trying to iterate through the selected value rows and insert the values into a matching table.

I'm I using odbc_fetch_array incorrectly, or does it look like something's wrong with my bound parameters?

//Main query to select data
$data = "
  SELECT  
      u.extension
      , sum(duration) as total_talk_time_seconds
      , round(sum(duration) / 60,2) as total_talk_time_minutes
      , sum(case when legtype1 = 1 then 1 else 0 end) as total_outbound

  from SESSION a
    join call_summary b
      on a.notablecallid = b.notablecallid
    inner join system_USERS u
      on u.EXTENSION = CALLINGPARTYNO or u.EXTENSION = FINALLYCALLEDPARTYNO
  group by extension,u.user_id" or die(db2_conn_error($DB2Conn));


$stmt = "
  INSERT into daily_call_totals
    (extension,
    total_talk_time_seconds,
    total_talk_time_minutes,
    total_outbound)
  VALUES (?, ?, ?, ?)" or die(db2_conn_error($DB2Conn));

//create array for binding
$content = [];

$mainResult = odbc_exec($DB2Conn, $data);
while ($d = odbc_fetch_array($mainResult)) {

  $prepInsert = odbc_prepare($DB2Conn, $stmt);

  //for each row, bind param. This is to ensure we get the correct number of records whether they're being inserted or updated for duplicates
  $values = [
      $d['extension'],
      $d['total_talk_time_seconds'],
      $d['total_talk_time_minutes'],
      $d['total_outbound']];

  // Store the current row
  $content[] = $d;

  if($prepInsert){
      $result = odbc_execute($prepInsert,$values);
      if($result){
        print "successfully added record";
      }
    }
}

Upvotes: 0

Views: 119

Answers (1)

Parfait
Parfait

Reputation: 107652

As mentioned consider an INSERT...SELECT and avoid the looped INSERT...VALUES. In fact, even consider the ANSI version (i.e., compliant in all SQL-supported DBMS's) of avoiding duplicates using the NOT EXISTS clause. But this will only run one action and not conditionally two actions.

Below can be run anytime and only unique pairings of date_of_report and extension will be appended, ignoring matches. Be sure to replace date_of_report and my_report_date_column for actual columns as you never explicitly do so in aggregate query.

INSERT into daily_call_totals
        (date_of_report,                                  -- UPDATE COLUMN
         extension,
         total_talk_time_seconds,
         total_talk_time_minutes,
         total_outbound)
SELECT
      my_report_date_column,                              -- UPDATE COLUMN
      u.extension
      , sum(duration) as total_talk_time_seconds
      , round(sum(duration) / 60,2) as total_talk_time_minutes
      , sum(case when legtype1 = 1 then 1 else 0 end) as total_outbound

FROM SESSION a
JOIN call_summary b
  ON a.notablecallid = b.notablecallid
JOIN system_USERS u
  ON u.EXTENSION = CALLINGPARTYNO or u.EXTENSION = FINALLYCALLEDPARTYNO
WHERE NOT EXISTS
   (SELECT sub.date_of_report, sub.extension
    FROM daily_call_totals sub
    WHERE sub.date_of_report = my_report_date_column     -- UPDATE COLUMN
      AND sub.extension = u.extension)
GROUP BY my_report_date_column, extension, u.user_id     -- UPDATE COLUMN

Now if you want to run two actions conditionally: 1) update existing values OR 2) insert new values, then use DB2's MERGE (available in some but not all RDBMS's): 

MERGE INTO daily_call_totals AS d
USING
   (SELECT
          my_report_date_column,                              -- UPDATE COLUMN
          u.extension
          , sum(duration) as total_talk_time_seconds
          , round(sum(duration) / 60,2) as total_talk_time_minutes
          , sum(case when legtype1 = 1 then 1 else 0 end) as total_outbound

    FROM SESSION a
    JOIN call_summary b
      ON a.notablecallid = b.notablecallid
    JOIN system_USERS u
      ON u.EXTENSION = CALLINGPARTYNO or u.EXTENSION = FINALLYCALLEDPARTYNO
    GROUP BY my_report_date_column, extension, u.user_id     -- UPDATE COLUMN
   ) AS q
ON (d.date_of_report = q.my_report_date_column               -- UPDATE COLUMN
    AND d.extension = q.extension)
WHEN MATCHED THEN
     UPDATE SET d.total_talk_time_seconds = q.total_talk_time_seconds,
                d.total_talk_time_minutes = q.total_talk_time_minutes,
                d.total_outbound = q.total_outbound            
WHEN NOT MATCHED THEN
     INSERT (date_of_report,                                 -- UPDATE COLUMN
             extension,
             total_talk_time_seconds,
             total_talk_time_minutes,
             total_outbound)
     VALUES (q.my_report_date_column,                        -- UPDATE COLUMN
             q.extension
             q.total_talk_time_seconds
             q.total_talk_time_minutes
             q.total_outbound);

Upvotes: 1

Related Questions