Select values where array value are excluded

Question

I cannot find solution of such problem. I need that $sql variable shows all values except the specified values as username != $var2 array values.

    $user_data = user_data($session_user_id, 'user_id', 'username', 'blocked_emails');

    $var = $user_data['blocked_emails'];
    $var2 = explode(',', $var);

    $sql = mysqli_query($con1, "SELECT * FROM `test` WHERE `username` != '$var2' ORDER BY user_id");

while ($row2 = mysqli_fetch_array($sql)) {

    $uid = $row2['user_id'];
    $username = $row2['username'];
    }

you could try a NOT IN (…) instead of your !=.

Renardo
– Renardo

2018-03-10 14:57:03 +00:00
Commented Mar 10, 2018 at 14:57 — Renardo
– Renardo, Commented Mar 10, 2018 at 14:57

Renardo · Accepted Answer · 2018-03-10 15:45:46Z

2

The core question seems to be how the SELECT statement should look like:

SELECT * FROM `test`
  WHERE `username` NOT IN ('[email protected]', '[email protected]', …)
  ORDER BY `user_id`

How to produce this statement in PHP is left as an exercise to the reader. Lajos Arpad's answer gives a good start but it is safer to use a prepared statement ($con1->prepare(…)), so you do not have to worry about apostrophes in values, SQL injection and the like.

answered Mar 10, 2018 at 15:45

Renardo

5595 silver badges14 bronze badges

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

Comments

Lajos Arpad · Accepted Answer · 2018-03-10 15:34:12Z

0

As Renardo suggested, you need not in, like this:

    $user_data = user_data($session_user_id, 'user_id', 'username', 'blocked_emails');

    $var = $user_data['blocked_emails'];
    $var2 = explode(',', $var);
    for ($index = 0; $index < count($var2); $index++)
        $var2 = "'".$var2[$index]."'";

    $sql = mysqli_query($con1, "SELECT * FROM `test` WHERE `username` not in (".implode(",", $var2).") ORDER BY user_id");

while ($row2 = mysqli_fetch_array($sql)) {

    $uid = $row2['user_id'];
    $username = $row2['username'];
    }

But in this solution we assume that none of the values in $var2 contain apostrophe. You will need to take further measures to sanitize your query, to ensure it will work well with any possible input and protect yourself against SQL injection at the same time.

edited Mar 10, 2018 at 15:34

answered Mar 10, 2018 at 15:06

Lajos Arpad

80.4k42 gold badges123 silver badges234 bronze badges

2 Comments

Renardo Over a year ago

Don't you need to use [$index] after $var2 in your loop? And expanding $var2 in the SELECT string yields just Array in my test.

Lajos Arpad Over a year ago

@Renardo yes, I need that, forgot to add it. Thanks for pointing that out!

Collectives™ on Stack Overflow

Select values where array value are excluded

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related