Question 17718 in Laravel Question posted in
The official Laravel Documentation can be found here.

Fetching value in the json array in MySQL, Laravel

I have that kinda table and data :

Table Name : data

+------+-----------------+--------+----------+
| id   | number          | name   | surname  |
+------+-----------------+--------+----------+
|    1 | [1, 2, 3, 4, 5] |   John |      Doe |
|    2 | [1, 2, 4, 8]    |  James |     Webb |
|    3 | [3, 4, 5]       |  Jenny |     Test |
+------+-----------------+--------+----------+

For example, I want to fetch the rows in the number column with the value 3 :

+------+-----------------+--------+----------+
| id   | number          | name   | surname  |
+------+-----------------+--------+----------+
|    1 | [1, 2, 3, 4, 5] |   John |      Doe |
|    3 | [3, 4, 5]       |  Jenny |     Test |
+------+-----------------+--------+----------+

I tried that with Laravel but didn’t work. :

DB::table('data')
            ->whereRaw('FIND_IN_SET(?, number)', [3])
            ->get();

How can I solve that problem? Thanks for your answers.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thanks for your answers. I found the answer to my question as follows :

    I made the Array values as strings. Like that :

    Table Name : data

+------+---------------------------+--------+----------+
| id   | number                    | name   | surname  |
+------+---------------------------+--------+----------+
|    1 | ["1", "2", "3", "4", "5"] |   John |      Doe |
|    2 | ["1", "2", "4", "8"]      |  James |     Webb |
|    3 | ["3", "4", "5"]           |  Jenny |     Test |
+------+---------------------------+--------+----------+

    And then I used to this codes. :

     $_data = DB::table('data')
            ->where('number', 'LIKE', '%"' . "3" . '"%')
            ->get();

    Also you can do with SQL commands. Like that :

    SELECT * 
  FROM data
  WHERE number LIKE '%"3"%';

    Exactly like I want, it turned to me this :

    +------+---------------------------+--------+----------+
| id   | number                    | name   | surname  |
+------+---------------------------+--------+----------+
|    1 | ["1", "2", "3", "4", "5"] |   John |      Doe |
|    3 | ["3", "4", "5"]           |  Jenny |     Test |
+------+---------------------------+--------+----------+

  2. 0

    You can use toJson(), to convert the collection to json object in Laravel.

    $_data = DB::table('data')
            ->whereRaw('FIND_IN_SET(?, number)', [3])
            ->get()->toJson();
dd($_data);

    Have a look at the [documentation] https://laravel.com/docs/9.x/responses#json-responses

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search