CODEWITHSUNDEEP
phpmysqljsonjoomla
jonnypixel
jonnypixel

Reputation: 327

Mysql query WHERE Json value = this

I need to write a mysql query within a xml field. Joomla provides me the format and its relatively simple for most things. Except can I actually write a query that checks a json value within the attribs column.

Below is just my example, I know it won't work. But that's what I want to do.

SELECT * 
FROM com_content 
WHERE atrribs(form_show) = 1

also below is the json format that the attribs column is holding

{

"form_show":"1",
"form_avilable":"0",
"mycategory":"4",
"response":"Thank you!",
"forms":{

"fieldinstruction":["blah","blah"],
"fieldmanditory":["0","0"]

}

}

Note: Here is the format joomla explains. Taken from joomla docs

docs.joomla.org/SQL_form_field_type

<field 
    name="myfield" 
    type="sql" 
    default="10" 
    label="Select an article" 
    query="SELECT id, title FROM #__content" 
    key_field="id" 
    value_field="title" 
/>

You can also assemble or calculate fields in the SQL statement. For example, suppose you wanted to append the created date/time of each article to the article title in the list. Then you could use this SQL statement:

SELECT id, concat( title, ' (', created, ')') AS title 
FROM #__content

What I need to do is write a mysql query using the above format but it needs to get a value from within one column and its also a json.

Thanks to anyone in advance

Upvotes: 0

Views: 615

Answers (2)

Elin
Elin

Reputation: 6755

What you can try is to query 

$mystring = '%'.'"form_show":"1"' . '%';
$query->where($db->quoteName('attrib')   .' LIKE '. $db->quote($mystring) );

(fixed this)

Upvotes: 0

Brian Bolli
Brian Bolli

Reputation: 1873

You cannot, which is precisely the reason storing JSON strings in a DB should be approached with caution. That being said, none of the example columns you used are stored in JSON in Joomla's content table. Are those the actual values you were looking for?

Your only option is to parse the returned result set in PHP to do any further filtering. Depending on your end goal you might be able to insert PHP logic into your MVC path model or table class to handle any filtering. Are you writing a custom component or implementing a layout override?

Upvotes: 1

Related Questions