SQLSRV using an array for the IN clause in the UPDATE statement

Question

I want to update a table where column user_id has values, stored in an array, but I get this error:

Array ( [0] => Array ( [0] => 42S22 [SQLSTATE] => 42S22 [1] => 207 [code] => 207 [2] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name 'Array'. [message] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name 'Array'. ) )

This is my code:

$sql = "
   SELECT usuario_id 
   FROM control_asistencias 
   WHERE 
      ano=".$ano." and 
      mes=".$mes." and 
      dia".$dia."='T' and 
      comida_habitual ='T' 
";
$stmt = sqlsrv_query( $conn, $sql);

if( $stmt === false ) {
   die( print_r( sqlsrv_errors(), true) );
}


$usuarios_comida_habitual = array();
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
    array_push($usuarios_comida_habitual,$row['usuario_id']);
}

// Una vez que tenemos los usuarios Que han fichado para ese dia hacemos un Update

$sql_update = "
   UPDATE VLD_PRESENCIA 
   SET VLD_PRESENCIA_DIA".$dia." = 'T' 
   WHERE 
      VLD_PRESENCIA_CODUSUARIO IN (".$usuarios_comida_habitual.") AND
      VLD_PRESENCIA_COMIDA_HABITUAL = 'T'
   ";
     
   $stmt2 = sqlsrv_query( $conn2, $sql_update);
   if( $stmt2 === false ) {
       die( print_r( sqlsrv_errors(), true) );
   }
   else
   {
      echo 'eureka';
   }

Is it possible that the way the array was passed is wrong? Or is it possible to put the condition that I want to implement what userid should be in that array?

I found this

 array_walk($usuarios_comida_habitual , 'intval');
 $ids = implode(',', $usuarios_comida_habitual);

i Do this before generating $sql_update

Answer 1

Although using an array for the IN clause has already a similar answer, you may try to use the sqlsrv_prepare()\sqlsrv_execute() combination to prepare a statement and execute it multiple times. But in both cases you need to consider the following:

• Always try to use parameters in your statements to prevent possible SQL injection issues.
• When the column name is used dynamically (possible wrong design), check the genarated name using sys.columns system catalog view to prevent possible SQL injection issues.

Note, the reason for your error is the fact, that you are trying to build an SQL statement concatenating a string literal and a PHP array.

The following example, based on your code and using parameterized queries, is a possible solution to your problem:

<?php
// SELECT statement
$sql = "
    SELECT usuario_id 
    FROM control_asistencias 
    WHERE ano = ? AND mes = ? AND dia".$dia." = 'T' AND comida_habitual = 'T' 
";
$params = array($ano, $mes);
$stmt = sqlsrv_query($conn, $sql, $params);
if ( $stmt === false ) {
    die( print_r( sqlsrv_errors(), true) );
}
$usuarios_comida_habitual = array();
while ( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
    $usuarios_comida_habitual[] = $row['usuario_id'];
}

// Multiple UPDATE statements
$sql = "
    UPDATE VLD_PRESENCIA 
    SET VLD_PRESENCIA_DIA".$dia." = 'T' 
    WHERE 
        VLD_PRESENCIA_CODUSUARIO = ? AND 
        VLD_PRESENCIA_COMIDA_HABITUAL = 'T'
";
$usuario = 0;
$params2 = array(&$usuario);
$stmt2 = sqlsrv_prepare($conn2, $sql, $params2);
if ( $stmt2 === false ) {
    die( print_r( sqlsrv_errors(), true) );
}
foreach ($usuarios_comida_habitual as $usuario) {
    if (sqlsrv_execute($stmt2) === false ) {
        die( print_r( sqlsrv_errors(), true) );
    } else {
        echo 'eureka';
    }
}   
?>       

If you want to execute a single UPDATE statement, the following approach is an option, but again with parameterized statement:

<?php
// SELECT statement
$sql = "
    SELECT usuario_id 
    FROM control_asistencias 
    WHERE ano = ? AND mes = ? AND dia".$dia." = 'T' AND comida_habitual = 'T' 
";
$params = array($ano, $mes);
$stmt = sqlsrv_query($conn, $sql, $params);
if ( $stmt === false ) {
    die( print_r( sqlsrv_errors(), true) );
}
$usuarios_comida_habitual = array();
while ( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
    $usuarios_comida_habitual[] = $row['usuario_id'];
}

// Single UPDATE statement
$usuarios_placeholders = substr(str_repeat(',?', count($usuarios_comida_habitual)), 1);
$sql = "
    UPDATE VLD_PRESENCIA 
    SET VLD_PRESENCIA_DIA".$dia." = 'T' 
    WHERE 
        VLD_PRESENCIA_CODUSUARIO IN (".$usuarios_placeholders.") AND 
        VLD_PRESENCIA_COMIDA_HABITUAL = 'T'
";
$stmt2 = sqlsrv_query($conn2, $sql, $usuarios_comida_habitual);
if ( $stmt2 === false ) {
    die( print_r( sqlsrv_errors(), true) );
} else {
        echo 'eureka';
}   
?>   

Answer 2

Consider a single SQL query without any loop since IN supports subqueries. And since connections run across different servers, consider OPENROWSET for ad hoc queries. Adjust ODBC connection string parameters and [database].[schema] identifiers accordingly. (Note: default schema is dbo).

$sql = "UPDATE VLD_PRESENCIA 
        SET VLD_PRESENCIA_DIA".$dia." = 'T' 
        WHERE VLD_PRESENCIA_CODUSUARIO 
           IN (SELECT sub.usuario_id 
               FROM OPENROWSET('SQLNCLI',
                               'DRIVER={SQL Server};SERVER=ServerName;UID=userID;PWD=password',
                               'SELECT * FROM [database].[schema].control_asistencias') sub
               WHERE sub.ano = ? 
                 AND sub.mes = ? 
                 AND sub.dia".$dia." = 'T'
                 AND sub.comida_habitual = 'T') 
        AND VLD_PRESENCIA_COMIDA_HABITUAL = 'T'"; 

$prms = array($ano, $mes);
$stmt = sqlsrv_query($conn, $sql, $prms);

if( $stmt === false ) {
     die( print_r( sqlsrv_errors(), true));
}