Select in MySQL with an array

Question

Select in MySQL with an array

Navigation

#1 by (4 votes)
#2 by (-6 votes)

4

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 the data belonging to the array in the vendor column of the table and then bring the column with the IDs.

I tried this script and it did not 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');

php mysql mysqli

asked by anonymous 25.08.2014 / 15:06

2 answers

It is faster to delete + insert or select + update Is it possible to use composite key for Entity Framework with Model First?

score 4 · Answer 1

To make a IN dynamic use implode to join / transform an array into a string delimiter per comma. Since it is a little more complicated to pass multiple arguments to bind_param() it was necessary to call the call_user_func_array that takes 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 manual comments

score -6 · 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 ()