0

I want to create a query to search specific results.

suppose I have a database field, let's say category, in this field data is stored as a JSON object.

e.g. 

({"Facebook":1,"Gmail":0,"LinkedIn":0,"Social":0,"Event":0,"Other":0})

Now I want a query for filtering the data based on this category.

Let say I want to search the data which contains Facebook:1 and Social:1

So how can I make a query for filtering the data?

sorak
  • 2,607
  • 2
  • 16
  • 24
vaibhavmht225
  • 25
  • 11
  • https://stackoverflow.com/questions/23720988/how-to-filter-json-data-in-javascript-or-jquery – TarangP Mar 12 '18 at 06:19
  • Would be better if you had a `social_category` table and `linked_social` which the above table links to. Then you just need to go the other way and select on `social_category` and join the rows. https://en.wikipedia.org/wiki/Many-to-many_(data_model) – Lawrence Cherone Mar 12 '18 at 06:20
  • Yeah I know, if there are two tables than it's very easy for me, but in my case I can't change our big database structure at this stage. I really appreciate your suggestion @Lawrence Cherone – vaibhavmht225 Mar 12 '18 at 06:37
  • many to many is 3 tables.. – Lawrence Cherone Mar 12 '18 at 06:39
  • I must need to do this task as I have just database table which has multiple records with different json strings such as row #1 : {"Facebook":1,"Gmail":0,"LinkedIn":0,"Social":0,"Event":0,"Other":0} row #2 :{"Facebook":1,"Gmail":0,"LinkedIn":1,"Social":0,"Event":0,"Other":0} row #3 :{"Facebook":0,"Gmail":1,"LinkedIn":0,"Social":1,"Event":0,"Other":0} row #4 :{"Facebook":0,"Gmail":0,"LinkedIn":0,"Social":0,"Event":0,"Other":1} Now I want to filter that row which contains the category boolean value 1 for Facebook and Gmail. So ultimately I want row #1,2,3 as a result set. – vaibhavmht225 Mar 12 '18 at 06:40

1 Answers1

0

Having another table (as Lawrence mentioned in comment) is the usual way to go. But if you must use the current format, then you can have a like query with regx, if they sequence of the social networks is fixed then the query will be simpler. This SO post has some examples.

Update: If your set of categories are fixed and in same sequence as you mentioned in comment, then you don't even need to use like or regex. You can do an exact string match.

Use a function like following to get the string to match-

function getCategoryString($facebook = 0, $gmail =0, $linkedIn = 0, $social=0, $event=0, $other=0) 
{
    $qry = "({\"Facebook\":".$facebook.",\"Gmail\":".$gmail.",\"LinkedIn\":".$linkedIn.",\"Social\":".$social.",\"Event\":".$event.",\"Other\":".$other."})";
    return $qry;
}

and then use that string in the query-

$sql = "SELECT * FROM `table` WHERE `category` = ".getCategoryString(1,1,0,0,0,0)

Note that tou'll have to modify this to escape the categoryString properly. I don't work in php so not sure what functions are used to escape mysql queries in php.

AsifM
  • 680
  • 9
  • 21
  • Yes, As I put the field value as {"Facebook":1,"Gmail":0,"LinkedIn":0,"Social":0,"Event":0,"Other":0}, So this parameters are fixed, in future it will never change. So I cannot make a seperate table for storing this static values. – vaibhavmht225 Mar 12 '18 at 06:45