How to use php array with sql IN operator?

Viewed 150 times

I have and array with two values and I want to use it with sql IN operator in select query.

Here is the structure of my table

id comp_id
1   2
2   3
3   1

I have an array $arr which have two values Array ( [0] => 1 [1] => 2 )

I want to fetch the record of comp_id 1 and comp_id 2. So I wrote the following query.

SELECT * from table Where comp_id IN ($arr)

But it does not return the results.

Answers

Since you have plain integers, you can simply join them with commas:

$sql = "SELECT * FROM table WHERE comp_id IN (" . implode(',', $arr) . ")";

If working with with strings, particularly untrusted input:

$sql = "SELECT * FROM table WHERE comp_id IN ('" 
     . implode("','", array_map('mysql_real_escape_string', $arr)) 
     . "')";

Note this does not cope with values such as NULL (will be saved as empty string), and will add quotes blindly around numeric values, which does not work if using strict mysql mode.

mysql_real_escape_string is the function from the original mysql driver extension, if using a more recent driver like mysqli, use mysqli_real_escape_string instead.

However, if you just want to work with untrusted numbers, you can use intval or floatval to sanitise the input:

$sql = "SELECT * FROM table WHERE comp_id IN (" . implode(",", array_map('intval', $arr)) . ")";

Friday, November 25, 2022

chabdulrahman542-160a380c7286

Try this..... You can use this function for any depth of the associated array. Just contraint to this function is that the key value would not be repeat any where in array.

<?php 
function is_in_array($array, $key, $key_value){
      $within_array = 'no';
      foreach( $array as $k=>$v ){
        if( is_array($v) ){
            $within_array = is_in_array($v, $key, $key_value);
            if( $within_array == 'yes' ){
                break;
            }
        } else {
                if( $v == $key_value && $k == $key ){
                        $within_array = 'yes';
                        break;
                }
        }
      }
      return $within_array;
}
$test = array(
                0=> array('ID'=>1, 'name'=>"Smith"), 
                1=> array('ID'=>2, 'name'=>"John")
        );
print_r(is_in_array($test, 'name', 'Smith'));
?>

Thursday, November 10, 2022

chiwda

A placeholder can only represent a single, atomic value. The reason it kinda works is because the value mysql sees is of the form '123,456' which it interprets as an integer, but discards the rest of the string once it encounters the non numeric part(the comma).

Instead, do something like

$list = join(',', array_fill(0, count($ids), '?'));
echo $sql = "...where notification_id IN ($list)";
$this->dbh->prepare($sql)->execute(array_values($ids));

Thursday, October 13, 2022

uliwitness

As per @haxxxton suggestion it works

 $read_data = array();
    foreach ($data->test as $result){
      $name = $result->Name;
      $no = $result->{'Reg/Admission Number'}`
      $read_data[] = "('$name','$no')";
    }

Friday, November 11, 2022

len_holgate

It refers to the index or key, not the value. 0 and 1 are the valid indices for that array. There are also valid keys, including "length" and "toString". Try 2 in x. That will be false (since JavaScript arrays are 0-indexed).

See the MDN documentation.

Tuesday, November 29, 2022

rideintothesun

Only authorized users can answer the search term. Please sign in first, or register a free account.

Not the answer you're looking for? Browse other questions tagged :

php

sql

arrays

in-operator

How to use php array with sql IN operator?

Answers

php,sql,arrays,in-operator