CODEWITHSUNDEEP
phpmysqldb2odbc
Geoff_S
Geoff_S

Reputation: 5107

DB2, passing between odbc and mysql

I'm trying to execute this following program but I'm getting some errors (I've commented the lines that have errors with /**/, while normal comments have //)

Essentially I'm:

  1. Selecting records from a mysql table where status is not 'S'
  2. Holding the ID and status code for those records in an array
  3. Pulling records from a table on an odbc connection that have the same order number (Mysql is order_id, odbc/db2 is INVNOZ as ORDERNUM)
  4. Comparing the status between the two tables for each record
  5. Looping to see if the status is still the same on the db2 connection, or if it has changed to 'S'
  6. For orders where it has changed to 'S', store those order numbers in another array

So it's working up to the point where I start dealing with DB2 and I think a lot of it just has to do with how I'm using the variables between databases.

Maybe it's a mix between syntax and improper variable use, but I'm not sure exactly where to go from here. I've cleared some other errors but I'm still stuck on these. I can answer any questions to clarify

    //Selecting Records with status other than 'S'
    $preShipmentOrders = "
            SELECT 
                order_id,
                order_status
            FROM order_status
            WHERE order_status <> 'S'
    ";

    $preShipResult = mysqli_query($mysqlConn, $preShipmentOrders);

    //Store all Order_id and order_status for records with status other than 'S'
    $PSOrderIds = array();

    while ($row = mysqli_fetch_array($preShipResult))
    {
    $PSOrderIds[] = $row['order_id'];
    $PSOrderIds[] = $row['order_status'];
    }

    print_r($PSOrderIds); //Up to this line works fine, prints correct Ids/Statuses 

    foreach($PSOrderIds as $PSOrderId){

    //Pull records from DB2 where INVNOZ = previous order_id and check to see if FSTATZ in DB2 is same status as previous query, or if it's now 'S' in DB2
    $orderCompareDB2 = " 

        SELECT 
            INVNOZ AS ORDERNUM,
            FSTATZ AS STATUS 
          FROM GPORTAFL
        WHERE  ORDERNUM = $PSOrderIds[order_id]    /*This gives - Notice: Undefined index: order_id in C:\check.php on line*/
    ";

    $compareResult = odbc_exec($DB2Conn,$orderCompareDB2); /*Error -  odbc_exec(): SQL error: [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token <END-OF-STATEMENT> was not valid. Valid tokens: ( + - ? : DAY INF NAN NOT RID ROW., SQL state 37000 in SQLExecDirect in C:\check.php on line*/

    //If any of those db2 records now have 'S' in fstatz, store those order IDs/INVNOZ
    $matchedOrderIds = array();

    while($compareRow = odbc_fetch_array($compareResult)){      /*Error - odbc_fetch_array() expects parameter 1 to be resource, boolean given in C:\check.php on line*/

        if($compareRow[STATUS] == 'S'){

            $updatePlacement = "UPDATE jfi_sales.order_status SET is_placement = 1, date_updated = DATE(NOW()) WHERE order_id IN (" . implode(',',$matchedOrderIds) .") ";

            if(mysqli_query($mysqlConn, $updatePlacement)){
                echo "Update Successful";
            }else{
                echo "ERROR: Couldn't update. " . mysqli_error($mysqlConn);
            }
        }else{
            echo "FAILED";
        }
    }
    }

Upvotes: 0

Views: 152

Answers (2)

Nigel Ren
Nigel Ren

Reputation: 57121

Your loop needs to store the current data and then each iteration of the foreach loop will point to that element, you can then pick out the orderID from the data there...

    while ($row = mysqli_fetch_array($preShipResult))
        {
        $PSOrderIds[] = $row;
    }
    foreach($PSOrderIds as $PSOrderId){
      $orderCompareDB2 = " SELECT 
            INVNOZ AS ORDERNUM,
            FSTATZ AS STATUS 
          FROM GPORTAFL
        WHERE  INVNOZ = '{$PSOrderId['order_id']}'
    ";

    $compareResult = odbc_exec($DB2Conn,$orderCompareDB2);
    if ( $compareResult === false ) {
         exit (odbc_errormsg($DB2Conn));
    }

    ...

The second error is due to the statement ending in ORDERNUM = as the substitution failed and the third error is due to the statement not compiling correctly.

Also added some error reporting in case the SQL fails to compile (although I hate using exit() this shouldn't normally get triggered.

Or to a shortcut for loading is 

$PSOrderIds= mysqli_fetch_all($preShipResult,MYSQLI_ASSOC);

Upvotes: 3

IsThisJavascript
IsThisJavascript

Reputation: 1716

That's because you never actually construct a key within $PSOrderIds called order_id. you can confirm this by just running a var_dump($PSOrderIds) The solution is to adapt your while() loop with $PSOrderIds[] = array('order_id'=>$row['order_id'], 'order_status'=>$row['order_status']);

Your second error; /*Error - odbc_fetch_array() expects parameter 1 to be resource, boolean given in C:\check.php on line*/ is because your first query failed. And the first query failed because it was never getting a correct value for ORDERNUM

Upvotes: 1

Related Questions