主页 / coding / 问题 / 79564251
Accepted
Toniq
Asked: 2025-04-09 19:44:00 +0800 CST

MySQL SELECT WHERE IN 与另一个占位符混合

  • 772

此查询有效并返回预期结果：

$arr = [7,1];//dynamic
$vids = explode(',',$arr);
$in = implode(',', array_fill(0, count($ids), '%d'));

$stmt = $wpdb->prepare("
            SELECT mt.id, mt.title, mt.options, COUNT(DISTINCT ct.id) as comment_count, COUNT(DISTINCT lt.id) as like_count
            FROM $media_table as mt
            LEFT JOIN $comments_table ct ON ct.media_id = mt.id
            LEFT JOIN $like_table lt ON lt.media_id = mt.id
            WHERE mt.id IN ($in)
            GROUP BY mt.id 
            ORDER BY $sortOrder $sortDirection", $vids);

如果我print_r($stmt)得到：

     "SELECT mt.id, mt.title, mt.options, COUNT(DISTINCT ct.id) as comment_count, COUNT(DISTINCT lt.id) as like_count FROM wp_mvp_reel_media as mt LEFT JOIN wp_mvp_reel_comments ct ON ct.media_id = mt.id LEFT JOIN wp_mvp_reel_like lt ON lt.media_id = mt.id WHERE mt.id IN (7,1) GROUP BY mt.id ORDER BY order_id ASC"

当我添加MAX(lt.user_id = %d) AS user_liked)- 时查询失败。

如果我print_r($stmt)，则返回一个空字符串""：

    $stmt = $wpdb->prepare("
                SELECT mt.id, mt.title, mt.options, COUNT(DISTINCT ct.id) as comment_count, COUNT(DISTINCT lt.id) as like_count, MAX(lt.user_id = %d) AS user_liked
                FROM $media_table as mt
                LEFT JOIN $comments_table ct ON ct.media_id = mt.id
                LEFT JOIN $like_table lt ON lt.media_id = mt.id
                WHERE mt.id IN ($in)
                GROUP BY mt.id 
                ORDER BY $sortOrder $sortDirection", $user_id, $vids);
wordpress

1 个回答

  1. Best Answer

    您需要将所有参数以正确的顺序以扁平列表的形式提供给 wpdb->prepare。$user_id 应该放在最前面，然后是 $vids 数组中的每个元素。

    
global $wpdb;


$vids = [7, 1]; 


if (!is_array($vids) || empty($vids)) {
    
    echo "Error: No valid media IDs provided.";
    
} else {
    
    $vids = array_map('intval', $vids);
    $user_id = (int) $user_id; 

    
    $in_placeholders = implode(',', array_fill(0, count($vids), '%d'));

    
    $sql_template = "
        SELECT mt.id, mt.title, mt.options,
               COUNT(DISTINCT ct.id) as comment_count,
               COUNT(DISTINCT lt.id) as like_count,
               MAX(CASE WHEN lt.user_id = %d THEN 1 ELSE 0 END) AS user_liked -- Placeholder 1 for user_id
               -- Using MAX(CASE...) is slightly more robust than MAX(lt.user_id = %d)
               -- but MAX(lt.user_id = %d) should also work in MySQL/MariaDB.
        FROM {$media_table} as mt
        LEFT JOIN {$comments_table} ct ON ct.media_id = mt.id
        LEFT JOIN {$like_table} lt ON lt.media_id = mt.id
        WHERE mt.id IN ({$in_placeholders}) -- Placeholders 2, 3, ... N for vids
        GROUP BY mt.id
        ORDER BY {$sortOrder} {$sortDirection}"; 

    
    $args = array_merge([$user_id], $vids); 

    
    $stmt = $wpdb->prepare($sql_template, $args);

    
    if (empty($stmt)) {
         echo "Error preparing statement: " . $wpdb->last_error;
         
    } else {
        

        
        $results = $wpdb->get_results($stmt);

        
    }
}
    • 3

相关问题