Mysql query WHERE Json value = this

Question

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

Brian Bolli · Accepted Answer · 2014-03-18 01:06:01Z

1

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?

answered Mar 18, 2014 at 1:06

Brian Bolli

1,8731 gold badge12 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Elin Over a year ago

@Brian_Bolli I'm confused by you saying that attrib is not stored in JSON. It is is the images field, the url field, and the metadata field.

jonnypixel Over a year ago

@Brian_Bolli thanks im looking at your comments now.

jonnypixel Over a year ago

@Brian_Bolli It goes into the attribs column via a plugin called alter articles. A very simple plugin i was able to add to a form tab within the article. I now want to retrieve a value from that json.

Brian Bolli Over a year ago

@jonnypixel OK, sorry for the delay, just got to home office. :) It sounds like you want to include the param column as a SELECT and then use the native PHP function json_decode to translate the JSON string into a PHP object. Does that make sense?

Brian Bolli Over a year ago

@Elin I was riding the bus home when I posted and only saw the last query example and assumed, in my error, the SELECT's where the columns he was after.

Elin · Accepted Answer · 2014-03-18 10:02:38Z

0

What you can try is to query

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

(fixed this)

edited Mar 18, 2014 at 10:02

answered Mar 18, 2014 at 1:16

Elin

6,7803 gold badges28 silver badges49 bronze badges

Collectives™ on Stack Overflow

Mysql query WHERE Json value = this

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related