How to use a string with array value in Mysql?

Question

I want to select some results from my database but it must exclude the last 3 results which I got already.

The result I got already is in a string inside a foreach.

$result is a foreach string with three ID values (one for each), eg: 1, 2, and 3.

If I use $result in my query, it'll only exclude ID 3, because it's the last one.

This is what I've tried already, which works but only excludes the ID 3:

foreach ($pdo->query($sql) as $abc) {
 $result = $abc['imp'];
}

SELECT * FROM t1 INNER JOIN t2 ON t1.cat = t2.id INNER JOIN t3 ON t1.aut = t3.id WHERE t1.imp <> $result AND t1.status = 1 ORDER BY t1.pub DESC LIMIT 3

What could I do to exclude all $result instead?

First of all, you should show us the `foreach` loop you're using, and any *directly relevant* code from nearby, so that we can understand what you've tried so far, and your use case. Second, you should probably look to use `NOT IN` instead of `<>`; we can give more specifics once we see some code. — Greg Schmidt, Jan 16 '23 at 22:14
The format of this sql would be `WHERE t1.imp NOT IN (1,2,3) and t1.status = 1` YOu'll want to `implode()` the array into a comma delimited string to bind into the `IN` list your sql. [Something like in the answer here](https://stackoverflow.com/questions/37553812/php-bind-array-to-in) or [here](https://stackoverflow.com/questions/9618277/how-to-use-php-array-with-sql-in-operator). — JNevill, Jan 16 '23 at 22:15
@GregSchmidt thanks for helping! I updated including the `foreach`. I tried `NOT IN` using a subquery but MySQL doesn't support it. — poteitow, Jan 16 '23 at 22:20
MySQL definitely does support subqueries in `NOT IN()`. The only restriction is that the subquery can't use `LIMIT`. — Barmar, Jan 17 '23 at 00:12
[How to bind an array of strings with mysqli prepared statement?](https://stackoverflow.com/q/17226762/2943403) and [Use an array in a mysqli prepared statement: `WHERE .. IN(..)` query](https://stackoverflow.com/q/3703180/2943403) — mickmackusa, Jan 17 '23 at 00:42

score 1 · Accepted Answer · edited Jan 17 '23 at 00:37

Make $result into an array. Then use them in the IN() list. You can create a list of ?,?,... of the same length to use as placeholders in the query, and use the array as the values argument to $stmt->execute()

$result = [];
foreach ($pdo->query($sql) as $abc) {
    $result[] = $abc['imp'];
}

$placeholders = implode(',', array_fill(0, count($result), '?'));

$stmt = $pdo->prepare("
    SELECT * FROM t1 
    INNER JOIN t2 ON t1.cat = t2.id 
    INNER JOIN t3 ON t1.aut = t3.id 
    WHERE t1.imp NOT IN ($placeholders)
        AND t1.status = 1 
    ORDER BY t1.pub DESC 
    LIMIT 3");
$stmt->execute($result);

How to use a string with array value in Mysql?

1 Answers1