WordPress 4.9.5数据库查询：生成指定嵌套结构数组的方法

Got it, let's sort out that array structure for your WordPress 4.9.5 database query. First, let's clarify what your target structure requires: a top-level array with a generalInfo key, which holds two distinct parts: a single post entry (like the post_id 84 example) and a category-named array (e.g., Hardware) filled with its associated posts.

It sounds like your current query is either mixing all posts into a single flat array, not grouping them under the generalInfo parent key, or failing to set the category-named key correctly. Let's fix this with two reliable approaches:

Approach 1: Use WordPress Native Functions (Recommended) #

This method leverages WordPress built-in functions to avoid direct database manipulation risks and ensures compatibility with your WP 4.9.5 setup.

// Initialize your target array structure
$target_array = [
    'generalInfo' => []
];

// 1. Fetch the specific "general info" post (post_id 84)
$general_post = get_post(84);
if ($general_post) {
    $general_entry = [
        'post_id' => $general_post->ID,
        'title' => get_the_title($general_post),
        'permalink' => get_permalink($general_post),
        'category' => [] // To populate categories, use wp_get_post_categories($general_post->ID)
    ];
    // Add the general post to generalInfo
    $target_array['generalInfo'][] = $general_entry;
}

// 2. Fetch posts under the "Hardware" category
$hardware_cat_id = get_cat_ID('Hardware'); // Get category ID by name
if ($hardware_cat_id) {
    $hardware_posts = get_posts([
        'cat' => $hardware_cat_id,
        'post_status' => 'publish',
        'posts_per_page' => -1 // Retrieve all published posts in the category
    ]);
    
    $hardware_entries = [];
    foreach ($hardware_posts as $post) {
        setup_postdata($post); // Required for WP template functions
        $hardware_entries[] = [
            'post_id' => $post->ID,
            'title' => get_the_title($post),
            'permalink' => get_permalink($post),
            'category' => [] // Populate with wp_get_post_categories($post->ID) if needed
        ];
    }
    wp_reset_postdata(); // Clean up post data global
    
    // Add the Hardware category posts to generalInfo
    $target_array['generalInfo']['Hardware'] = $hardware_entries;
}

// Output the final structured JSON
echo json_encode($target_array, JSON_PRETTY_PRINT);

Approach 2: Direct SQL Query (For Advanced Control) #

If you need to bypass WP functions (e.g., for custom performance tweaks), use this database query approach. Note: Replace wp_ with your actual table prefix if you modified it during installation.

Step 1: SQL Queries #

-- Fetch the general info post (post_id 84)
SELECT 
    p.ID AS post_id, 
    p.post_title AS title, 
    CONCAT('https://your-site-url/', p.post_name) AS permalink
FROM wp_posts p
WHERE p.ID = 84 AND p.post_status = 'publish';

-- Fetch posts in the "Hardware" category
SELECT 
    p.ID AS post_id, 
    p.post_title AS title, 
    CONCAT('https://your-site-url/', p.post_name) AS permalink
FROM wp_posts p
JOIN wp_term_relationships tr ON p.ID = tr.object_id
JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN wp_terms t ON tt.term_id = t.term_id
WHERE t.name = 'Hardware' 
  AND tt.taxonomy = 'category' 
  AND p.post_status = 'publish';

Step 2: PHP to Assemble the Array #

global $wpdb;

// Get general post data
$general_post = $wpdb->get_row("
    SELECT p.ID AS post_id, p.post_title AS title, CONCAT('https://your-site-url/', p.post_name) AS permalink
    FROM {$wpdb->prefix}posts p
    WHERE p.ID = 84 AND p.post_status = 'publish'
");

// Get Hardware category posts
$hardware_posts = $wpdb->get_results("
    SELECT p.ID AS post_id, p.post_title AS title, CONCAT('https://your-site-url/', p.post_name) AS permalink
    FROM {$wpdb->prefix}posts p
    JOIN {$wpdb->prefix}term_relationships tr ON p.ID = tr.object_id
    JOIN {$wpdb->prefix}term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
    JOIN {$wpdb->prefix}terms t ON tt.term_id = t.term_id
    WHERE t.name = 'Hardware' AND tt.taxonomy = 'category' AND p.post_status = 'publish'
");

// Build the target array
$target_array = [
    'generalInfo' => []
];

// Add general post to the array
if ($general_post) {
    $target_array['generalInfo'][] = [
        'post_id' => $general_post->post_id,
        'title' => $general_post->title,
        'permalink' => $general_post->permalink,
        'category' => []
    ];
}

// Add Hardware category posts
if ($hardware_posts) {
    $hardware_entries = [];
    foreach ($hardware_posts as $post) {
        $hardware_entries[] = [
            'post_id' => $post->post_id,
            'title' => $post->title,
            'permalink' => $post->permalink,
            'category' => []
        ];
    }
    $target_array['generalInfo']['Hardware'] = $hardware_entries;
}

// Output formatted JSON
echo json_encode($target_array, JSON_PRETTY_PRINT);

Key Notes #

• Replace https://your-site-url/ with your actual WordPress site URL to generate valid permalinks.
• If you want to populate the category array with actual category names/IDs, use wp_get_post_categories() (for WP functions approach) or extend the SQL query to include category details.
• WordPress 4.9.5 uses the same core database structure as newer versions for posts and terms, so these methods will work reliably.

内容的提问来源于stack exchange，提问作者Carol.Kar