Find in single value from mysql column array values in laravel/php [duplicate]

Question

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();

?>

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
– Gordon Linoff, Commented Sep 9, 2019 at 14:42
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
– GrumpyCrouton, Commented Sep 9, 2019 at 14:43
Strip every row of its [] make something lile this stackoverflow.com/questions/57844393/mysql-field-data-parsing/… to split up your row unto many row and look there for your number. — nbk
– nbk, Commented Sep 9, 2019 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
– Grzegorz Miśkiewicz, Commented Sep 9, 2019 at 14:52
@GrzegorzMiśkiewicz JSON in TEXT field? Why not use a Json field? — user8034901
– user8034901, Commented Sep 9, 2019 at 15:00

Severin · Accepted Answer · 2019-09-09 14:57:09Z

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.

Collectives™ on Stack Overflow

Find in single value from mysql column array values in laravel/php [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related