phpmysqlpdonotinbindvalue

PDO MySQL "not in()" not working


I have a query that returns related video titles from a given video so same genre, country, date published etc.

I want to exclude certain video_names using not in () function. The problem is that the query returns the videos I want to exclude and they are sorted first in the array also. Why is this happening?

public function interval($month, $not, $limit) {
    $this->not = array_unique($not);
    $i = implode(',', $this->not);

    echo $i;//prints: onajr,babyjem,posh

    $query = '
        select title, artists, published, views, video_name, yt_id, duration, play_start, genre, country from videos
        where
        published BETWEEN :published - INTERVAL :month MONTH AND :published + INTERVAL :month MONTH
        and MATCH(country) AGAINST(:country IN boolean mode)
        and MATCH(genre) AGAINST(:genre IN boolean mode)
        and
        video_name not in (" :i ")
        ORDER BY RAND() limit :limit
    ';

    $run_query = $this->pdo->prepare($query);

    $run_query->bindValue(':published', $this->published);
    $run_query->bindValue(':country', '+' . $this->data->country);
    $run_query->bindValue(':genre', '+' . $this->data->genre);
    $run_query->bindValue(':limit', $limit, PDO::PARAM_INT);
    $run_query->bindValue(':month', $month, PDO::PARAM_INT);
    $run_query->bindValue(':i', $i);

    $run_query->execute();
    $data =  $run_query->fetchAll(PDO::FETCH_ASSOC);

    print_r($data);
    //contains all three of them onajr,babyjem,posh
}

Solution

  • You can create array of the parameters and the values like this:

    $this->not = array_unique($not);
    $i = array();
    foreach( $this->not as $key => $val ) {
        $i[':vid_' . $key] = $val;
    }
    

    Bind it to the query:

    ' ..... video_name not in ('. implode(',', array_keys($i)) .') ....'
    

    And then, bind the params:

    foreach( $i as $key => $val ) {
        $run_query->bindValue($key, $val);
    }