Null Coalesce Operator and SQL IN clause

Question

My homepage is displaying a table with different data taken from a DB based on the current user's team. I am creating a cookie with the team when the user logs in which I am reading when displaying the table.

When the user is not logged-in, I also want to display the table, but I want it to show the data for 2 (or more) teams. I am using a temporary solution for now using the null coalesce operator which defaults to the first team and looks like this: $team = $_COOKIE ['team'] ?? 1;

And my query: $associates = "SELECT associate_id, first_name, last_name FROM scp.associates WHERE team = '$team' ORDER BY associate_id ASC";

Is there any way to modify either or both of these to get my desired output? So far, I have tried the following:

$team = $_COOKIE ['team'] ?? '1, 2';
$associates = "SELECT associate_id, first_name, last_name FROM scp.associates
    WHERE team IN ('$team') ORDER BY team ASC, associate_id ASC";

which works if the cookie is set and:

$team = $_COOKIE ['team'] ?? "'1', '2'";
$associates = "SELECT associate_id, first_name, last_name FROM scp.associates
    WHERE team IN ($team) ORDER BY team ASC, associate_id ASC";

which works when the cookie is not set... I have tried other variations of these, but could not get it to work. Any ideas? Thanks!

Edit: My cookie is a string and I am now using prepared statements. The new code looks like this:

$team = $_COOKIE['team'] ?? '1';
$associates = "SELECT associate_id, first_name, last_name FROM scp.associates
    WHERE team IN (?) ORDER BY team ASC, associate_id ASC";
$res_associates = odbc_prepare ( $conn, $associates );
odbc_execute ( $res_associates, array ( $team ) );

When I change to '1, 2', I am getting no results from the DB. My if ( odbc_num_rows ( $res_associates ) > 0 ) is false.

Edit2: When I add the values directly in my query, it works but when it takes them from the variable (either prepared or not) it doesn't...

So this works:

$associates = "SELECT associate_id, first_name, last_name FROM scp.associates
    WHERE team IN ('1', '2') ORDER BY team ASC, associate_id ASC";

But this does not:

$team = $_COOKIE['team'] ?? " '1', '2'";
$associates = "SELECT associate_id, first_name, last_name FROM scp.associates
    WHERE team IN (?) ORDER BY team ASC, associate_id ASC";

(the space between " and ' is needed so it doesn't think it's a file)

Solution:

$team = $_COOKIE['team'] ?? '1,2';
$terms = explode ( ',', $team );
$placeholders = rtrim ( str_repeat ( '?, ', count ( $terms ) ), ', ' );

$associates = "SELECT associate_id, first_name, last_name FROM scp.associates
    WHERE team IN ($placeholders) ORDER BY team ASC, associate_id ASC";

$res_associates = odbc_prepare ( $conn, $associates );
odbc_execute ( $res_associates, $terms );

Answer 1

You should split on a ,s, put placeholders into your query, and then bind each term.

Something like (I assume you're using PDO, if not empty the execute call and use the appropriate call for your driver) this would do it:

$team = $_COOKIE['team'] ?? '1, 2';
$terms = explode(',', $team);
$placeholders = rtrim(str_repeat('?, ', count($terms)), ', ');
$associates = "SELECT associate_id, first_name, last_name 
               FROM scp.associates 
               WHERE team IN ($placeholders) 
               ORDER BY team ASC, associate_id ASC";
$get_stuff = $pdo->prepare($associates);
$get_stuff->execute($terms)); 

Rough demo: https://3v4l.org/REqPc

Answer 2

This is likely a type issue, couple things to ask when solving this type of problem. What type is $_COOKIE ['team']? and what type is team in the database?

Option 1: I think PHP probably thinks $_COOKIE ['team'] is an int when it should be a string, and it needs quotes. So you could do something like this which implicitly casts it to a string and adds quotes:

$team = $_COOKIE ['team'] ? "'" . $_COOKIE ['team'] . "'" : "'1', '2'";

Option 2: Add quotes in the query as in the first one WHERE team IN ('$team') and then merely cast it to a string.

$team = $_COOKIE ['team'] ? (string) $_COOKIE ['team'] : '1, 2';