MySQL SELECT WHERE IN mixed with another placeholder

This query is valid and returns expected results:

$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);

If I print_r($stmt) I get:

     "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"

When I add MAX(lt.user_id = %d) AS user_liked) - the query fails.

If I print_r($stmt), an empty string is returned "":

    $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);

Solution

You need to provide all arguments to wpdb->prepare as a flat list in the correct order. The $user_id should come first, followed by each individual element from the $vids array.


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);

        
    }
}