Select in Mysql with an array

Asked

Viewed 6,461 times

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 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');
  • 1

    Your options don’t have value! There’s a good reason...

  • It would be the lolusion, but I can’t use the array inside the SELECT,

  • Your query is correct, just enter the values. For example: <option value="1">bla</option>.

  • @bfavaretto if the ID is used as option value, clause where will need to be amended as well: SELECT * FROM fornecedores WHERE id IN ('$dados')

  • True, @gmsantos, I hadn’t noticed. I would actually use the id instead of the name.

  • True! with id worked! I just don’t know why it doesn’t work with strings...

  • 3

    It doesn’t work because you’re riding IN('foo, bar') instead of IN('foo', 'bar').

Show 2 more comments

2 answers

6

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

-6

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()

  • 3

    Iterating multiple queries in the database is a bad idea. Imagine a list of 20 suppliers, it makes sense to make 20 queries to the bank and one already solves the problem?

  • It depends on which is more efficient, or whatever is faster and spend less server resources. Here we are talking about 8 searches. The solution I presented has an 8 cycle loop and can be done with 3 variables. Many times to save 1 in the database, spend 2 in php. Just testing.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.