MySQL select where variable is not in row

Question

I'm trying to do MySQL query to extract specific rows without the help of PHP if conditions,

okay,
i want to select a row where the variable is not in a specific row but here's the thing

$blocked_user = "aaron";

and in MySQL

+----------+-----------------------+
| id       | blocked               |
+----------+-----------------------+
| 1        | aaron,john            |

i want it to skip "aaron" while selecting and process to john
a false example :

SELECT * FROM table WHERE $blocked_user NOT IN blocked

What i was doing is get blocked content and seperate them using php explode function then using in_array and if to confirm its not there :

if(in_array($blocked_user, explode($row['blocked'])){
  exit;
}

but now i want to get rid of this and do it using a single query, how is it done ?

Does your blocked field actually stores comma separated values? — Jorge Zapata
– Jorge Zapata, Commented Jul 25, 2012 at 17:38
imploded string in db field is not good idea. you should store each user in different row — varuog
– varuog, Commented Jul 25, 2012 at 17:38
@JorgeZapata nope, its actually a session username but just to show in example — Osa
– Osa, Commented Jul 25, 2012 at 17:38
@Osa - could you possibly add a full example in your question? I'm having trouble figuring out exactly what you want - do you just want the 'John' part of the 'Aaron,John' string? — andrewsi
– andrewsi, Commented Jul 25, 2012 at 17:40
@user1538127 they could be over 1000 by time, i cant do that, i can in case there's any other idea to do the same thing but in a single row — Osa
– Osa, Commented Jul 25, 2012 at 17:40

Zane Bien · Accepted Answer · 2012-07-25 17:53:04Z

4

You should really put each of the CSV values in different rows (perhaps in a blocked table), but what you can do is this:

SELECT * 
FROM tbl
WHERE FIND_IN_SET('$blocked_user', blocked) = 0

If the $blocked_user is contained within the CSV list in the blocked column, that row is not selected.

SQLFiddle Demo

edited Jul 25, 2012 at 17:53

answered Jul 25, 2012 at 17:43

Zane Bien

23.2k6 gold badges47 silver badges58 bronze badges

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

Comments

Matt · Accepted Answer · 2012-07-25 17:46:10Z

1

I think what you're looking for is a LEFT OUTER JOIN, but that requires that your blocked names be stored in separate rows.

Example:

+----------+-----------------------+
| id       | blocked               |
+----------+-----------------------+
| 1        | aaron                 |
| 2        | john                  |

Then you can do a LEFT OUTER JOIN on your users table like so:

SELECT * FROM users AS u
LEFT JOIN blocked as b
ON b.blocked = u.username
WHERE b.blocked IS NULL

answered Jul 25, 2012 at 17:46

Matt

7,0504 gold badges31 silver badges50 bronze badges

Comments

wanovak · Accepted Answer · 2012-07-25 17:47:30Z

1

There may be some sort of advanced regex MySQL you can do, but the problem is more deeply-set. Your table should really look more like this:

| id | blocked |
----------------
| 1  |  aaron  |
| 1  |   john  |

Where of course id is not of type primary key.

Then you could do this:

SELECT * FROM table WHERE id = 1 AND blocked NOT IN ('aaron')

As per your edit, if you have a second table, let's call it data

SELECT * FROM data WHERE id = 1 AND id NOT IN (SELECT id FROM blocked_table WHERE blocked IN ('aaron'))

edited Jul 25, 2012 at 17:47

answered Jul 25, 2012 at 17:39

wanovak

6,12727 silver badges32 bronze badges

2 Comments

Osa Over a year ago

id 1 is a single row that i want to block specific people (aaron) from selecting it

wanovak Over a year ago

So you have a separate table for that and you're linking them relationally, right?

Shades88 · Accepted Answer · 2012-07-25 17:42:58Z

0

you can try

$sql = "SELECT * FROM table WHERE blocked NOT LIKE '%{$blocked_user}%'";

answered Jul 25, 2012 at 17:42

Shades88

8,42826 gold badges95 silver badges135 bronze badges

Collectives™ on Stack Overflow

MySQL select where variable is not in row

4 Answers 4

SQLFiddle Demo

Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related