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
– Greg Schmidt, Commented Jan 16, 2023 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 or here. — JNevill
– JNevill, Commented Jan 16, 2023 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
– poteitow, Commented Jan 16, 2023 at 22:20
MySQL definitely does support subqueries in NOT IN(). The only restriction is that the subquery can't use LIMIT. — Barmar
– Barmar, Commented Jan 17, 2023 at 0:12

mickmackusa · Accepted Answer · 2023-01-17 00:37:41Z

1

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

edited Jan 17, 2023 at 0:37

mickmackusa♦

49.2k13 gold badges98 silver badges165 bronze badges

answered Jan 17, 2023 at 0:17

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use a string with array value in Mysql?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related