CODEWITHSUNDEEP
mysqldatabasejsoncodeigniter
Taylor Gomez
Taylor Gomez

Reputation: 330

Mysql WHERE field containing JSON variable

I have a database having a filed myfield containing json string:["2","4","1","6"] OR ["1"] . I want to make a filter for this filed and was thinking to query it with:

SELECT id FROM table_name WHERE myfield = '5' -> this return can not echo id because ["2","4","1","6"] does not have the value 5.

SELECT id FROM table_name WHERE myfield = '1' -> return can echo id-> because -> in json string on filed myfield have the value 1.

how can fix it? what do i do?

I tried as but it don't work for me:

SELECT id FROM table_name WHERE myfield = '1'

Upvotes: 0

Views: 1718

Answers (3)

SuReSh
SuReSh

Reputation: 1511

Use serialization and deserialization to save or retrieve data from database field.

Upvotes: 0

Manoj Dhiman
Manoj Dhiman

Reputation: 5166

The given solution by @juergen will not work if you search first or last item . For this you need to replace square brackets also . This worked for me 

SELECT * 
FROM `tbl_tasks_weekly` 
WHERE FIND_IN_SET('1', REPLACE( REPLACE( REPLACE(`myfield`,']','') ,  '[',''),'\"',''))

Upvotes: 0

juergen d
juergen d

Reputation: 204784

To check for id=1 for instance you can do

SELECT 1 as id
FROM table_name 
WHERE find_in_set('1', replace(myfield, '"', '')) > 0

You can use it like this in your code

$this->db->query("SELECT 1 as id 
                  FROM table_name 
                  WHERE find_in_set('1', replace(myfield, '\"', '')) > 0");

Upvotes: 1

Related Questions