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 ?
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.
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'))
you can try
$sql = "SELECT * FROM table WHERE blocked NOT LIKE '%{$blocked_user}%'";
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