Php Mysql Column Value Not In Comma Separated String

Question

I have below database structure where column ip_visto is comma separated ip addresses. I need to have a query where current ip address not in ip_visto values

I have below query so far

  $current_ip=$_SERVER['REMOTE_ADDR'];
  $ad_info = $db->fetchRow("SELECT * FROM my_TABLE WHERE status='Active' and id!=" . $me_info['id'] ");

how can I pass $current_ip not in ip_visto value in above query

`WHERE !FIND_IN_SET($current_ip, ip_visto)`. Maybe excess space between comma and IP in `ip_visto` needs either `$current_ip=" ".$_SERVER['REMOTE_ADDR'];` or `WHERE !FIND_IN_SET($current_ip, REPLACE(ip_visto, ' ', '')`. — Akina, Jan 29 '21 at 04:43

score 0 · Answer 1 · edited Jan 29 '21 at 19:58

First of All I want to say that your approach is not good to do this. You should save these IP Addresses in a separate table instead of saving them comma separated values.

Now, According to your current approach, I can see that there is a comma exist on each of the ip_visto values. So it means we can write a query for this purpose.

$ad_info = $db->fetchRow("SELECT * FROM my_TABLE WHERE status='Active' and id!=" . $me_info['id'] AND ip_visto NOT LIKE '%, ".$current_ip.",%'");

The reason why I am adding a comma after % sign is that, If the current_ip=1.2.3.4 then it will also matches with the ip 111.2.3.4. So this is the reason I am adding a comma before the ip address because you are saving the ip address with a comma separated values.

score -1 · Answer 2 · answered Jan 29 '21 at 05:24

-1

You can use FIND_IN_SET and TRIM leading/trailing comma separated

$ip = 'YOUR IP HERE';

$q = "SELECT * FROM table_name 
   WHERE 
      status = 'Active' 
   AND 
      id != " . $me_info['id'] . " 
   AND
      NOT FIND_IN_SET('" . $ip . "', TRIM(BOTH ',' FROM ip_visto))";

answered Jan 29 '21 at 05:24

Jerson

1,700
2
10
14

1

See about sql injection and the importance of prepared and bound queries – Strawberry Jan 29 '21 at 06:58

Atul Vekariya · Answer 3 · 2021-01-29T03:57:34.387

-3

In your case, you need to use "NOT LIKE"

"SELECT * FROM my_TABLE WHERE status='Active' AND id!=". $me_info['id']" AND ip_visto NOT LIKE '%".$current_ip.",%'

edited Jan 29 '21 at 03:57

answered Jan 29 '21 at 03:52

Atul Vekariya

5
6

Will give excess incorrect matches. For example, `1.2.3.4` will be found if `111.2.3.4` is present in IPs list. – Akina Jan 29 '21 at 04:47

Php Mysql Column Value Not In Comma Separated String

3 Answers3