How to Use PHP Array with SQL in Operator

How to use php array with sql IN operator?

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)) . ")";

Formatting a PHP array for an SQL IN clause

There is a better way

You mention in the comments that you are using CodeIgniter. Unless you are making something extraordinarily complicated, there is no practical reason you should be building your own home-baked queries when you have where_in built in.

And if that doesn't work, then there is good ol' fashioned escape.

---

OK, so, you have most people saying that you need to quote the items and are giving you this:

function createInClause($arr)
{
    return '\'' . implode( '\', \'', $arr ) . '\'';
}

but that really isn't sufficient if you have the possibility for questionable input (such as '); DROP TABLE STUDENTS; --. To protect against that, you need to make sure you check for SQL injection:

function createInClause($arr)
{
    $tmp = array();
    foreach($arr as $item)
    {
        // this line makes sure you don't risk a sql injection attack
        // $connection is your current connection
        $tmp[] = mysqli_escape_string($connection, $item);
    }
    return '\'' . implode( '\', \'', $tmp ) . '\'';
}

PHP SQL WHERE name IN (array...)

If the values in $array are strings, then your query has invalid syntax. Consider this:

$array = ['foo', 'bar'];
$q = "SELECT * FROM xx WHERE url IN (".implode(',',$array).") ORDER BY id DESC";

The resulting query will be:

SELECT * FROM xx WHERE url IN (foo, bar) ORDER BY id DESC

Whereas you need:

SELECT * FROM xx WHERE url IN ("foo", "bar") ORDER BY id DESC

Change the implode to something like this:

$q = "SELECT * FROM xx WHERE url IN ('".implode("','",$array)."') ORDER BY id DESC";

Note the single quotes I added, now the resulting query will contain quoted strings in the IN clause:

SELECT * FROM xx WHERE url IN ('foo', 'bar') ORDER BY id DESC

Having said this, concatenating strings into a query still leaves you vulnerable to SQL injection. You should consider learning about prepared statements, and start using them

Passing an array to a query using a WHERE clause