0

What I need: return all posts matching a search criteria:

In a Wordpress post, I have an ACF that accepts a shortcode like

[shortcode id="1234"]

Edit: In the wp_postmeta table, it is referenced like

post_id    meta_key    meta_value
5          _shortcode    [shortcode id="1234"]

In the table that stores the shortcode data (wp_shortcode), it stores values that I need to search in this format below. One shortcode may contain multiple rows like below

id      data
1234    {"sale_price":"1500000","bathroom":"1"}
1234    {"sale_price":"2000000","bathroom":"3"}
1234    {"sale_price":"3000000","bathroom":"5"}

When I do a search like

sale_price_from = 1000
sale_price_to = 2000000
bathroom = 3

The post id should return true if any row found in sale_price is between the sale_price_from and sale_price_to as well as if the bathroom as equal to 3. In this case, the search criteria is met by the second row in the wp_shortcode table so it should return the post that contains the shortcode.

If the search is like

sale_price_from = 1000
sale_price_to = 2000
bathroom = 3

Then it should not return the post as the sale_price_from and sale_price_to range isn't found in any of the rows in the wp_shorcode table.

I'm lost as I'm not sure how to access the wp_shortcode table data as it is stored in json format.

Lhen
  • 181
  • 3
  • 15
  • MySQL / MariaDB now natively support JSON queries, that may be an option. If you have control over the data structure, I'd recommend changing it. – random_user_name May 14 '18 at 14:11
  • Possible duplicate of [How to search JSON data in mysql?](https://stackoverflow.com/questions/30411210/how-to-search-json-data-in-mysql) – random_user_name May 14 '18 at 14:11
  • @cale_b I'm not sure as I'm not looking 4 regexp and I think this would call 3 tables (?). Inner Join, Left Join? – Lhen May 14 '18 at 14:17

0 Answers0