How to apply WHERE clause again array or jscon encoded value
M4l33n’s Questions:
Values are stored under company_id like
["2"]
["2", "1"]
where 2 and 1 are the IDs of companies. Now i want all result of ID 2. How can i fire query again json encoded data.
select * from tbl where company_id = 2
This is what I need to perform.
For more info, json format is the result of dynamic (fill by database values) Select List in Zend2.
And what if it was array instead of json, how this simple select query can be executed.
As each ID is a number inside double quotes you can just query with a LIKE statement:
select * from tbl where company_id LIKE '%"2"%'
And what if it was array instead of json, how this simple select query can be executed.
You can store JSON into a mysql database because it is a string. But an array is not a string, so you can not store it there. You would need to serialize it but for that you should prefer JSON.
The alternative would be to create a table that stores the relationships to the companies.
MySQL has a datatype called SET You can use this data type on this task. This will allow you to enter comma separated values and still be able to query as per single item.
Manual: http://dev.mysql.com/doc/refman/5.0/en/set.html
Your query will have to be updated to something like this:
SELECT * FROM tbl_name WHERE FIND_IN_SET('2', company_id);
But, this problems arises because the tables are not normalized properly. Adding a reference table and querying this reference table will be a much better option, when the application reaches large-scale.
