0

I want to find out value from array with comma separated field. Here is my table structure

╔════╦══════════════╗
║ id ║  columnA     ║
╠════╬══════════════╣
║  1 ║ [1,2,3,4,5 ] ║
║  2 ║ [6,2,4,5,8 ] ║
╚════╩══════════════╝

If i provide "1" and columnA has match this 1 value from this array then fetch this row.

<?php

return $val = Model::where(1 exists in ColumnA then fetch this row)->first/get();

?>
Miss Chanandler Bong
  • 4,081
  • 10
  • 26
  • 36
Shahnaouz Razu Sharif
  • 9
  • 5
  • 2
    MySQL does not support arrays. Are you trying to say that `ColumnA` is a string? If so, why are you not using a more normalized format? – Gordon Linoff Sep 09 '19 at 14:42
  • 1
    This is precisely why you shouldn't store multiple values in a single comma separated cell (believe it or not, I've had people tell me I was wrong about this!) Your best bet is to pull all "like" records, then decode it into a PHP array and use PHP to check if actually contains the information you want. – GrumpyCrouton Sep 09 '19 at 14:43
  • Strip every row of its [] make something lile this https://stackoverflow.com/questions/57844393/mysql-field-data-parsing/57845202#57845202 to split up your row unto many row and look there for your number. – nbk Sep 09 '19 at 14:51
  • There is a way - using LIKE, but it will be problematic when for example You will have 11 and search for 1. It will be better, if you store JSON in TEXT field - so u can look for ... LIKE '%"1"%' – Grzegorz Miśkiewicz Sep 09 '19 at 14:52
  • @GrzegorzMiśkiewicz JSON in TEXT field? Why not use a Json field? – brombeer Sep 09 '19 at 15:00
  • @kerbholz You can. But earlier versions of mysql hasn't that field available. – Grzegorz Miśkiewicz Sep 11 '19 at 13:21

1 Answers1

0

As mentioned in a comment above, this is not the way to go about this problem. You need two tables to manage this in a proper way.

PARENT:
╔════╗
║ id ║
╠════╣
║  1 ║
║  2 ║
╚════╝

CHILD:
╔════╦══════════════╦══════════════╗
║ id ║  parent_id   ║  value       ║
╠════╬══════════════╬══════════════╣
║  1 ║ 1            ║ 1            ║
║  2 ║ 1            ║ 2            ║
║  3 ║ 1            ║ 3            ║
║  4 ║ 1            ║ 4            ║
║  5 ║ 1            ║ 5            ║
╚════╩══════════════╩══════════════╝

With this setup you can easily find single values on a parent without having to extract multiple values and look over them in PHP.

You will need to read up on Eloquent Relationships to make this work, as in the example you show. Take a look at Laravels documentation. I'm sure you already have it bookmarked. Should make sense. Good luck.

Severin
  • 962
  • 5
  • 21