MySQL Query error - no data supplied for parameters in prepared statement

I'm relatively new to MySQL, and I'm trying to combine my insert and update statements like I saw elsewhere. For the following code,

$query = "this is not the problem";
$ora_stmt = oci_parse($conn, $query);
$result = oci_execute($ora_stmt);
$stmt1 = $mysqli1->prepare("INSERT INTO na_dslam_card (n_alias, shelf_pt_num, card_pt_num, card_pt_description)
                            VALUES (?, ?, ?, ?)
                            ON DUPLICATE KEY UPDATE n_alias=?, shelf_pt_num=?, card_pt_num=?, card_pt_description=?");

while($row = oci_fetch_array($ora_stmt, OCI_ASSOC+OCI_RETURN_NULLS)) {
    print  $row['DSLAM'] . "," . $row['MODEL']  . "," . $row['VENDOR'] . "," . $row['BROADBAND_CIRCUIT_ID'] . "," . $row['SVC_N'] . "," . $row['PORT_ADDR_STATUS']. "," . $row['BROADBAND_CIRCUIT_ID'] . "," . $row['RACK'] . "," . $row['SHELF'] . "," .  $row['SLOT'] . "," . $row['PORT'] . "," . $row['SHELF_PT_NUMBER'] . "," .  $row['CARD_PT_NUMBER'] . "," . $row['CARD_PT_DESCRIPTION'] . "\n";
    $stmt1->bind_param("ssss", $row['DSLAM'],$row['SHELF_PT_NUMBER'],$row['CARD_PT_NUMBER'],$row['CARD_PT_DESCRIPTION']); 
    if(!$stmt1->execute())
    { 
      $tempErr = "Error setting card info: " . $stmt1->error;
      printf($tempErr . "\n");  //show mysql execute error if exists  
      $err->logThis($tempErr);
    } 
} //while

I get this error message:

Error setting card info: No data supplied for parameters in prepared statement

I used bind_parameters, so it shouldn't say I didn't supply the parameter data. I know rack can be null sometimes, but wouldn't it just set it to null in that case? I know the oracle query works because the print statement shows this:

CNVLDAT00,TA12,Ad,196,641,In Service,196,,2,,7,117,115,11L5

So why are the parameters not supplied correctly? It looks ok to me.

I looked at insert values and I think I'm doing that ok.

My table looks like this:

na_dslam_card: index n_alias shelf_pt_num card_pt_num card_pt_description

Thanks for any help...

Solution

There are eight bind placeholders in the SQL text. We would need to supply eight values.

The bind_param is only supplying four values.

But rather than supply eight values, we can modify the SQL text to replace the placeholders in the UPDATE clause with the VALUES() function, to reference the value that would have been inserted into the column.

INSERT INTO na_dslam_card
( n_alias
, shelf_pt_num
, card_pt_num
, card_pt_description
)  
VALUES (?, ?, ?, ?)
ON DUPLICATE KEY
UPDATE n_alias             = VALUES(alias)
     , shelf_pt_num        = VALUES(shelf_pt_num)
     , card_pt_num         = VALUES(card_pt_num)
     , card_pt_description = VALUES(card_pt_description)

Now there are only four placeholders that need values.

Excerpt from MySQL Reference Manual:

In assignment value expressions in the ON DUPLICATE KEY UPDATE clause, you can use the VALUES(col_name) function to refer to column values from the INSERT portion of the INSERT ... ON DUPLICATE KEY UPDATE statement. In other words, VALUES(col_name) in the ON DUPLICATE KEY UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred.

References:

https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_values