wp_postmeta: querying multiple meta_values from a random post_id

  • I am trying to pull a random post content along with its meta data from a custom post type called “BRAND” (try saying that again ^_^).

    I can pull one meta data, but I cannot pull multiple meta data for that post. My code is as follows:

    SELECT p.ID, p.post_content, m.meta_key, m.meta_value
FROM wp_posts p
	INNER JOIN wp_postmeta m
		ON p.ID = m.post_id
WHERE p.post_type = 'BRAND'
	AND p.post_status = 'publish'
	AND m.meta_key LIKE 'brand-%'
ORDER BY RAND()
LIMIT 1

    Note, that the meta_key can be something like “brand-a”, “brand-b”, “brand-c”, etc.

    How do I pull all those meta_keys? Thanks!

Viewing 2 replies - 1 through 2 (of 2 total)
  • Moderator keesiemeijer

    (@keesiemeijer)

    Try it with group_concat:

    <?php
global $wpdb;
$query = "SELECT p.ID, p.post_content, GROUP_CONCAT(meta_key) AS metakey, GROUP_CONCAT(meta_value) AS metavalue
FROM $wpdb->posts p
INNER JOIN $wpdb->postmeta m ON p.ID = m.post_id
WHERE p.post_type = 'BRAND'
	AND p.post_status = 'publish'
	AND m.meta_key LIKE 'brand-%'
	GROUP BY p.ID
ORDER BY RAND()
LIMIT 1";

$results = $wpdb->get_results( $query );
?>

    Thread Starter Han

    (@hswebdev)

    You are a lifesaver! Very clever way to put multiple data into one column. Thanks!

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘wp_postmeta: querying multiple meta_values from a random post_id’ is closed to new replies.