Loop to a select with multiple Ids

Question

I intend to make a query to the database with the filter of several Ids, to return the corresponding lines. I am trying this way:

First send the ids through js:

function update_estado()
{   
    var pedpront = [];
      $.each($("input[name='updpront']:checked"), function(){            
          pedpront.push($(this).val());
          
      });

    var dadosajax = {
        'Id[]' : pedpront ,
        'Fornecedor' : $("#Fornecedor3").val(),
        'Estado' : $("#Estado1").val(),
        'Observacao' : $("#Observacao").val()
    }
    $.ajax({
        url: 'pedencom.php',
        type: 'POST',
        cache: false,
        data: dadosajax,
        error: function(){
          $(".error_message").removeClass('hide');
          swal("Erro!", "Tente novamente. Caso persista o erro, contatar Administrador!", "error");
        },
        success: function(result2)
        {
        $('.limp9')[0].reset();
      }
    });
}

Send separate Ids this way:

Id[]:23
Id[]:24

Then in php I’m trying to do the loop in this way:

for($i=0;$i<count($Id);$i++)
{

if($Id[$i]!="")
{
$stmt1 = $conn->prepare("SELECT dados.Encomendas.Id_Enc, dados.Encomendas.Orcamento, NotaEnco, dados.Encomendas.Fornecedor AS IdForn, 

dados.NovoForn.Fornecedor, Ref, Designacao, Quantidade, Observacao

FROM dados.Encomendas LEFT OUTER JOIN dados.Orcamento ON dados.Orcamento.Id = dados.Encomendas.Id_Enc

LEFT OUTER JOIN dados.NovoForn ON dados.NovoForn.Id = dados.Encomendas.Fornecedor

LEFT OUTER JOIN dados.EncEmail ON dados.EncEmail.IdEnc = dados.Encomendas.Id_Enc WHERE dados.Encomendas.Id_Enc IN (:Id)

ORDER BY dados.NovoForn.Fornecedor ASC");

    $stmt1->bindValue(':Id', $Id[$i], PDO::PARAM_INT);
    $stmt1->execute();
}
}

while($row1=$stmt1->fetch(PDO::FETCH_ASSOC)){       
        $Orcamento = $row1["Orcamento"];
        $Ref1 = $row1["Ref"];
        $Designacao1 = $row1["Designacao"];
        $Quantidade1 = $row1["Quantidade"];
}

But this way it only returns one row, even if it sends more than one id through the url. Intended for example to send 3 ids, return the 3 lines corresponding to the 3 ids.

Answer 1

Try to loop to concatenate the Ids and then bind the query. So with the array of $Id being [0] => 23, [1] => 24 create a foreach:

$ids = '';
foreach($id as $dados)
{
  $ids .= "{$dados}, ";
}

But at the end of foreach the variable $ids would have the value: "23, 24, " to solve this we have to remove the last two characters from the string, so we use substr:

$ids = substr($ids, 0,-2);

And the value of $ids would be "23, 24" with this we can give bind $stmt1->bindValue(':Id', $ids);

Finally, the code would be:

$stmt1 = $conn->prepare("SELECT dados.Encomendas.Id_Enc, dados.Encomendas.Orcamento, NotaEnco, dados.Encomendas.Fornecedor AS IdForn, 

dados.NovoForn.Fornecedor, Ref, Designacao, Quantidade, Observacao

FROM dados.Encomendas LEFT OUTER JOIN dados.Orcamento ON dados.Orcamento.Id = dados.Encomendas.Id_Enc

LEFT OUTER JOIN dados.NovoForn ON dados.NovoForn.Id = dados.Encomendas.Fornecedor

LEFT OUTER JOIN dados.EncEmail ON dados.EncEmail.IdEnc = dados.Encomendas.Id_Enc WHERE dados.Encomendas.Id_Enc IN (:Id)

ORDER BY dados.NovoForn.Fornecedor ASC");

$stmt1->bindValue(':Id', $ids);
$stmt1->execute();
$row1 = $stmt1->fetchAll(PDO::FETCH_ASSOC)

Remember that you only use fetch() to return a single line for your case would be three, so use the fetchAll(), thereby $row1 will be an array.

I recommend that a var_dump($row1) to see how the return will look before using.