SQL select all rows where col1 is equal to one of the values in a comma separated string-CodePudding

Select multiple rows matching values from a coma separated string.

example 1: A0001,A0002
example 2: A0001,A0003,A0005,A0006
example 3: A0007,A0008,A0009,A0010,A0011,A0012.


Table: Data
col1    col2    col3
--------------------
A0001   A   B
A0002   C   D
A0003   E   F
A0004   G   H
A0005   I   J
A0006   K   L
A0007   M   N
A0008   O   P
A0009   Q   R
A0010   S   T
A0011   U   V
A0012   W   X
A0013   Y   Z

Using OR

$sqlData = $this->con->prepare("SELECT col1, col2, col3 
                                FROM data 
                                WHERE col1=A0001 or col2=A0001");
$sqlData->execute();

I cannot use OR because the number of values in the coma separated string varies.

Using array

$comaSeperatedString = "A0007,A0008,A0009,A0010,A0011,A0012";
col1_arr = explode(",", $comaSeperatedString);

foreach (col1_arr as dataItem) {
    $sqlData = $this->con->prepare("SELECT col1, col2, col3 
                                    FROM data 
                                    WHERE col1=dataItem");
    $sqlData->execute();
    echo col1;
    echo col2;
    echo col3;
}

I could push the coma separated string into an array then use a foreach loop to iterate through the array running the query for each instance. However this seems sloppy and wasteful.

I want to select all rows where col1 is equal to one of the values in a comma separated string.

Can this be done in one simple SQL statement?

CodePudding user response：

You can use FIND_IN_SET() which returns the item's position in the comma separated list or 0 if not found:

$comaSeperatedString = "A0007,A0008,A0009,A0010,A0011,A0012";

$query = $this->con->prepare('SELECT col1, col2, col3 FROM data WHERE FIND_IN_SET(col1, :values)');
$query->bindParam(':values', $comaSeperatedString);
$query->execute();