CODEWITHSUNDEEP
phpmysql
Aniket Singh
Aniket Singh

Reputation: 877

how to search multiple username in a single query

I want to search for some username in my database like this->

$skip = $_POST['username'];
$_SESSION['skip_user'] = array();
array_push($_SESSION['skip_user'],$skip);
$str = $_SESSION['skip_user'];
$string = rtrim(implode(',', $str), ',');

Now string variable looks like "name1, name2, name3";

mysqli_query($db, "SELECT * FROM users WHERE username in ({$string}) ORDER BY id DESC");

This fetches the users but i don't want these users. I mean is there any query where i can i write WHERE username !in ({$string})!

get all users except "name1, name2, name3" these users

Now after adding NOT IN I'm receiving error

mysqli_query($db, "SELECT * FROM users  WHERE username NOT IN ({$string}) ORDER BY id DESC")or die(mysqli_error($db)); php is giving error Unknown column 'name1' in 'where clause'

Upvotes: 1

Views: 1078

Answers (3)

Thanos
Thanos

Reputation: 2572

Try NOT IN in the SQL query.

First though try to add quotes to the values you are trying in the NOT IN part of the sql query.

$str = '';
foreach ($_SESSION['skip_user'] AS $word) {
    $str .= "'$word',";
}
$str = rtrim($str, ',');

Then use this $str in your query. Also, try to make a habit out of using `` for column names, like this:

SELECT `SOMETHING` FROM `TABLE_NAME` WHERE <CONDITION>

I hope that helps!

Upvotes: 2

I wrestled a bear once.
I wrestled a bear once.

Reputation: 23409

yep, just type "not" instead of "!"

select * from table where junk not in ('item1', 'item2', 'item3');

1) You have a few other problems though you're not adding quotes to your implode:

// you need quotes here
$string = implode("','", $str);
// And here
mysqli_query($db, "SELECT * FROM users WHERE username in ('{$string}') ORDER BY id DESC");

However, this is what you should really be doing.

2) You should bind your parameters instead as you're open to SQL injection:

$params = array();
$params[0] = "";
$sql = "SELECT * FROM users WHERE username NOT IN (";
foreach($str as $s){
    $params[0] .= "s";
    array_push($params, $s);
    $sql .= "?, ";
}
$sql = rtrim($sql, " ,").") ORDER BY id DESC";

$stmt = $conn->prepare($sql);

// this is the same as doing: $stmt->bind_param('s', $param);
call_user_func_array(array($stmt, 'bind_param'), $params);

// execute and get results
$stmt->execute();

Upvotes: 1

codedge
codedge

Reputation: 5174

You should use NOT IN to exclude certain values.

mysqli_query($db, "SELECT * FROM users WHERE username NOT IN ('name1', 'name2') ORDER BY id DESC");

Upvotes: 2

Related Questions