Jump to content

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


ludah

Recommended Posts

 <?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 

Link to comment
Share on other sites

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) . ")");
Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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 by Jacques1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.