Hello! I wanted to share how I managed to automatize queries in SQL which used the IN() instruction with PHP.

The problem

A very common situation consists of an array in PHP in which I have a list of things I want to use to filter my query's results. To do that I should use this IN() instruction, but SQL won't understand a PHP array so easily.

The next code shows what I would like to achieve:

SELECT Name FROM animals WHERE Species IN({$selectedSpecies})

where $selectedSpecies would be an array like:

<?php
$selectedSpecies = array("Dog", "Cat", "Fish");

Well, unfortunately, for the query to work, the PHP variable between the braces {} should be a string.

The solution

Solving this problem is very simple as we just need to transform that array into a string that can fit in the IN() statement. A very simple way is this PHP function, which I use very often:

<?php
function arrayToStringForInStatement($array): string
{
    $string = "";
    foreach ((array)$array as $elem) {
        $string .= "'$elem'" . ", ";
    }
    return substr($string, 0, strlen($string) - 2);
}

The reason I have to create my own function and I am not using the native implode(), is that I need each element of the array to be between single quotation marks if I am searching for strings. implode() would work if I was searching for integers, but the function I created works fine in every case.

Finally, the code would look like this:

<?php
$selectedSpecies = array("Dog", "Cat", "Fish");
$selectedSpeciesString =         
        arrayToStringForInStatement($selectedSpecies);

// The query, but now it will work fine!
$sql = "SELECT Name 
        FROM animals 
        WHERE Species IN({$selectedSpeciesString})";
$result = mysqli_query($link, $sql);
$showResult = mysqli_fetch_array($result);

Seems quite simple, right? But this has for sure been very useful to me. I hope it can be also useful to you! Thanks for reading!