Select in Mysql with an array

Question

I have a form with a field multiple select:

<form action="processa-chose.php" method="post">
      <select id="selecionar" name="fornecedor[]"
        data-placeholder="Fabricantes"
        style="width:350px;"
        multiple class="chosen-select" tabindex="8">

        <option>XXX</option>
        <option>XXX</option>
        <option>XXX</option>
        <option>XXX</option>
        <option>XXX</option>
        <option>XXX</option>
        <option>XXX</option>
        <option>XXX</option>
      </select>
      <input type="submit" value="envie" />

And I want to select in the table provider column the data belonging to the array and then bring the column with the Ids.

I tried this script and it didn’t work:

<?php
$conect = mysqli_connect("localhost","root","","XXXXXXXXX");
$fornecedor = $_POST['fornecedor'];
$dados = implode(",",$fornecedor);
$sql = "SELECT * FROM fornecedores WHERE fornecedor IN ('$dados')";
$result = mysqli_query($conect,$sql);

while($row = mysqli_fetch_assoc($result)){
   echo $row["id"];
}
?>

This is the table:

CREATE TABLE IF NOT EXISTS `fornecedores` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `fornecedor` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;

-- Extraindo dados da tabela `fornecedores`
--

INSERT INTO `fornecedores` (`id`, `fornecedor`) VALUES
(1, 'XXX'),
(2, 'XXX'),
(3, 'XXX'),
(4, 'XXX'),
(5, 'XXX'),
(6, 'XXX'),
(7, 'XXX'),
(8, 'XXX');

Answer 1

To make a IN dynamic use implode to join/transform an array into a comma delimiter string. As it is a bit more complicated to pass multiple arguments to bind_param() it was necessary to call the function call_user_func_array who picks up an object $stmt and applies the method bind_param

<?php

$arrPost = array(2,4,6,10,30);

$totalParametros = count($arrPost);
$placeHolders = str_repeat('?,', $totalParametros );
$placeHolders = substr($placeHolders,0, -1); //remove o ultimo caracter, que é uma virgula.

$str = implode(',', $arrPost);

$db = new mysqli('localhost', 'usuario', 'senha', 'base');

$sql = 'SELECT * FROM fornecedores WHERE id IN('.$placeHolders.')' ;
$stmt = $db->prepare($sql) or die(mysqli_error($db));

$arrReferencia = array();
foreach($arrPost as $chave => $valor){
    $arrReferencia[$chave] = &$arrPost[$chave];
}

$tipo = str_repeat('i', $totalParametros);

call_user_func_array(array($stmt,'bind_param'), array_merge(array($tipo), $arrReferencia));
$stmt->execute();
$itens = $stmt->get_result();


foreach($itens as $item){
    echo $item['fornecedor'] .'<br>';
}

Response based on comments from the manual

Answer 2

I would do so:

<?php

$conect = mysqli_connect("localhost","root","","XXXXXXXXX");

$fornecedor = $_POST['fornecedor'];

foreach($fornecedor as $value){

   $sql = "SELECT id FROM fornecedores WHERE fornecedor='$value'";

   $result = mysqli_query($conect,$sql);

   $row = mysqli_fetch_assoc($result);

   echo $row["id"];

}

You can also use mysqli::multy_query()