1

I need to make custom code for a Wordpress project. I need to get the product gallery.

This is the query to get all the ID's of the productimages of that product. This returns a string like for example "212, 213, 214".

SELECT meta_value FROM `wp_postmeta` WHERE meta_key='_product_image_gallery' AND post_id=?

Then I use it as a subquery like this:

SELECT guid FROM wp_posts WHERE ID IN (SELECT meta_value FROM `wp_postmeta` WHERE meta_key='_product_image_gallery' AND post_id=?)

The problem is that the WHERE ID IN is not working because the subquery returns a string of the ID's and does not view the ID's as separate instances. This is because all the productimages ID's are stored in a single cell.

How do I fix this? Is there some kind of split() function?

TL;DR

I need to convert

SELECT guid FROM wp_posts WHERE ID IN ("213, 214, 215")

to

SELECT guid FROM wp_posts WHERE ID IN (213, 214, 215)

knowing that "213, 214, 215" is retrieved from a subquery.

View of the row the subquery returns: Wordpress row product gallery

O'Niel
  • 1,622
  • 1
  • 17
  • 35
  • *This returns a string like for example "212, 213, 214".* Do you want to say that this is a value which is stored in one column of one row? You store a lot of values in CSV? – Akina Apr 14 '21 at 18:36
  • @Akina Yes, this is how Wordpress stores it. I'm not a fan of it either. – O'Niel Apr 14 '21 at 18:38

1 Answers1

2

You can use the function FIND_IN_SET():

SELECT guid 
FROM wp_posts 
WHERE FIND_IN_SET(
  ID, 
  REPLACE((SELECT meta_value FROM `wp_postmeta` WHERE meta_key='_product_image_gallery' AND post_id=?), ' ', '')
)

I use REPLACE() because I see that there are spaces in the comma separated list.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • This returns an empty result. – O'Niel Apr 14 '21 at 18:41
  • @O'Niel then maybe the id does not match any item of he comma separated list, or the list is not as you described it. Post sample data. – forpas Apr 14 '21 at 18:43
  • It works. My test-data was corrupted (altered it manually for this question). Thanks for the quick response mate. – O'Niel Apr 14 '21 at 18:48
  • This is very helpful. Thank you. Do you have suggestions for how to achieve the same outcome without a where clause in the subquery? – user3890141 Sep 30 '21 at 03:42