Help on Select data from mysqli using an array in WHERE clause

[ludah]

 <?php
          $array = array(
              "FBR-15","FBR-16","FBR-17","FBR-18","FBR-19","FBR-20","FBR-500"
          );

          $array =

            
            $q = mysqli_query($dbc,"SELECT * FROM tree_tbl WHERE `id` IN (array_map('intval','$array'))");
            while($data = mysqli_fetch_array($q)){
              echo "<li>".$data['tree_code']."</li></br>";
            }
          ?>

Please I heed helpin solving this 

[cyberRobot]

Assuming that you are using PHP's array_map() function, the call needs to be made outside of the string. Try changing this

$q = mysqli_query($dbc,"SELECT * FROM tree_tbl WHERE `id` IN (array_map('intval','$array'))");

To this

$q = mysqli_query($dbc,"SELECT * FROM tree_tbl WHERE `id` IN (" . array_map('intval', $array) . ")");

[ginerjm]

1 - I think you have an extra set of quotes around $array in your array_map call.

2 - Why apply "intval" to array elements that are clearly not numeric?

3 - To use the IN operator in SQL I think you need to explicitly list the array elements. Try doing an explode of the array into a string with a comma inserted as in:

$items = implode(',',$array);
...
...
where id in ($items)

[cyberRobot]

Also note that you're overwriting the $array variable here:

$array =
 
$q = mysqli_query(...

 

Basically, $array will contain whatever is assigned to $q. The script will probably work fine though since $array gets used before the value of $q is assigned to $array.

[cyberRobot]

1 - I think you have an extra set of quotes around $array in your array_map call.

2 - Why apply "intval" to array elements that are clearly not numeric?

 

Good catch. For some reason I though intval extracted the number from a string.

 

@ludah - Are you trying to get 20 and 500 from "FBR-20" and "FBR-500"? If so, you could use explode() to break each string based on the hyphen.

 

 

3 - To use the IN operator in SQL I think you need to explicitly list the array elements. Try doing an explode of the array into a string with a comma inserted as in:

 

 

Yep, thanks for catching that ginerjm!

[ginerjm]

I'm having a good day!

[Jacques1]

intval() is harmful even for integers, because it will truncate big numbers. And inserting the data straight into the query comes with the risk of SQL injections or at least syntax errors. The IDs may be safe right now, but this can change. In fact, this should change, because hard-coded magic strings buried somewhere in the application code suck.

 

Use a prepared statement instead:

$tree_ids = [
    'FBR-15',
    'FBR-16',
    'FBR-17',
    'FBR-18',
    'FBR-19',
    'FBR-20',
    'FBR-500',
];

$tree_stmt = $database_connection->prepare('
    SELECT tree_code
    FROM tree_tbl
    WHERE id IN ('.implode(',', array_fill(0, count($tree_ids), '?')).')
');

$bind_args = [];
$bind_args[] = str_repeat('s', count($tree_ids));
for ($i = 0; $i < count($tree_ids); $i++)
{
    $bind_args[] = &$tree_ids[$i];
}

call_user_func_array([$tree_stmt, 'bind_param'], $bind_args);

$tree_stmt->execute();

$tree_stmt->bind_result($tree_code);
while ($tree_stmt->fetch())
{
    var_dump($tree_code);
}

Yes, this is unbelievably cumbersome with MySQLi, which is why we generally recommend PDO.

Edited February 3, 2016 by Jacques1