如何从wp_postmeta表查询指定州的退伍军人完整信息？

Alright, let's break down why your current query only returns the veteran_state value, then fix it to pull all the veteran details you need, plus format them correctly in the browser.

The Issue with Your Original Query #

Your existing query filters wp_postmeta to only rows where meta_value = 'colorado'—which maps directly to the veteran_state key. That means you're only fetching the state entry for each veteran, not their name or website (which are stored as separate rows in wp_postmeta with their own unique meta_key values).

Corrected SQL Query #

We need to restructure the query to pull all three metadata fields (veteran_name, veteran_website, veteran_state) for each published veteran post in Colorado. Here are two reliable approaches:

Approach 1: Multiple Joins (Easy to Read) #

This joins wp_postmeta three times, once for each metadata field we need:

SELECT 
    p.ID,
    name.meta_value AS veteran_name,
    website.meta_value AS veteran_website,
    state.meta_value AS veteran_state
FROM wp_posts p
-- First, filter to only veterans in Colorado
JOIN wp_postmeta state 
    ON p.ID = state.post_id 
    AND state.meta_key = 'veteran_state' 
    AND state.meta_value = 'colorado'
-- Pull the veteran's name
LEFT JOIN wp_postmeta name 
    ON p.ID = name.post_id 
    AND name.meta_key = 'veteran_name'
-- Pull the veteran's website
LEFT JOIN wp_postmeta website 
    ON p.ID = website.post_id 
    AND website.meta_key = 'veteran_website'
WHERE 
    p.post_type = 'veterans' 
    AND p.post_status = 'publish';

Approach 2: Aggregate with CASE (Great for Scaling) #

If you might add more metadata fields later, this method uses CASE statements to pivot rows into columns:

SELECT 
    p.ID,
    MAX(CASE WHEN pm.meta_key = 'veteran_name' THEN pm.meta_value END) AS veteran_name,
    MAX(CASE WHEN pm.meta_key = 'veteran_website' THEN pm.meta_value END) AS veteran_website,
    MAX(CASE WHEN pm.meta_key = 'veteran_state' THEN pm.meta_value END) AS veteran_state
FROM wp_posts p
JOIN wp_postmeta pm ON p.ID = pm.post_id
WHERE 
    p.post_type = 'veterans' 
    AND p.post_status = 'publish'
    -- Ensure we only get veterans in Colorado
    AND EXISTS (
        SELECT 1 FROM wp_postmeta pm2 
        WHERE pm2.post_id = p.ID 
        AND pm2.meta_key = 'veteran_state' 
        AND pm2.meta_value = 'colorado'
    )
GROUP BY p.ID;

Displaying Results in the Browser #

Since this is a WordPress site, use the $wpdb class to safely run the query and output data in your desired format. Drop this into a template file or custom shortcode:

<?php
global $wpdb;
$target_state = 'colorado';

// Prepare the query (prevents SQL injection)
$veterans_query = $wpdb->prepare(
    "SELECT 
        name.meta_value AS veteran_name,
        website.meta_value AS veteran_website
    FROM wp_posts p
    JOIN wp_postmeta state 
        ON p.ID = state.post_id 
        AND state.meta_key = 'veteran_state' 
        AND state.meta_value = %s
    LEFT JOIN wp_postmeta name 
        ON p.ID = name.post_id 
        AND name.meta_key = 'veteran_name'
    LEFT JOIN wp_postmeta website 
        ON p.ID = website.post_id 
        AND website.meta_key = 'veteran_website'
    WHERE 
        p.post_type = 'veterans' 
        AND p.post_status = 'publish'",
    $target_state
);

$veterans = $wpdb->get_results($veterans_query);

if (!empty($veterans)) {
    // Output the header
    echo '<h2>Veteran Website</h2>';
    // Loop through each veteran and display their details
    foreach ($veterans as $vet) {
        // Sanitize output to prevent XSS attacks
        $safe_name = esc_html($vet->veteran_name);
        $safe_website = esc_url($vet->veteran_website);
        echo "<p>{$safe_name} {$safe_website}</p>";
    }
} else {
    echo '<p>No published veterans found in Colorado.</p>';
}
?>

This will output exactly the format you want:

Veteran Website
John Doe http://website.com
Mary Veteran http://mywebsite.com

内容的提问来源于stack exchange，提问作者user3497812