IN statment on a MySQL INNER JOIN php pdo

Question

i have an array i want to implode, then pass to a query and spit out all the matching values, but all i get is a 1064 violation, im sure its some silly syntax i've missed.

$filmImplode = implode("','", $filmList);
$query = "
    SELECT
        watch.film_id,
        films.film_api_id
    FROM
        watch
        INNER JOIN films ON films.film_id = watch.film_id
    WHERE
        films.film_api_id IN ('$filmImplode')
        AND watch.user_id = :user_id";
$query_params = array(':user_id' => 1);
try {
    $stmt = $db->prepare($query);
    $getWatched = $stmt->execute($query_params);
} catch (PDOException $ex) {
    echo 'something went wrong' . $ex->getMessage();
}
$getWatched = $stmt->fetchAll();

The SQL error reads

something went wrongSQLSTATE[42000]:

Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'item0','item1','item2','itme3','item4' at line 3

I'm voting to close this question as off-topic because This topic has been answered in [**info**](http://stackoverflow.com/tags/pdo/info) on PDO tag — david strachan, Mar 23 '16 at 10:01

score 0 · Accepted Answer · edited May 23 '17 at 10:30

I see 2 potential issues:

you may have film names with quote that will mess up your query. Escape them.
you have a space in your : user_id parameter

Try this:

array_walk($filmList, function(&$film){
    $film = $db->quote($film);
});
$filmImplode = implode(",", $filmList);
$query = "
    SELECT
        watch.film_id,
        films.film_api_id
    FROM
        watch
        INNER JOIN films ON films.film_id = watch.film_id
    WHERE
        films.film_api_id IN ($filmImplode)
        AND watch.user_id = :user_id";
$query_params = array(':user_id' => 1);
try {
    $stmt = $db->prepare($query);
    $getWatched = $stmt->execute($query_params);
} catch (PDOException $ex) {
    echo 'something went wrong' . $ex->getMessage();
}
$getWatched = $stmt->fetchAll();

An even better approach, as suggested here, would be to build dynamically the IN condition writing the placeholders and to then bind the parameters in the prepare method.

so array walk, seems to destroy everything. not getting any output or errors. And the space i corrected, but still no joy — Paddy, Mar 23 '16 at 10:36

score -2 · Answer 2 · answered Mar 23 '16 at 09:34

-2

The problem seems to be around filmImplode. Are your film_api_ids int? If not, you should make sure they are passed as string constants in your SQL.

WHERE films.film_api_id IN ('XXX-123-XX', 'YYY-456-YY')

Instead of

WHERE films.film_api_id IN (XXX-123-XX, YYY-456-YY)

Also, those single quotes look shady, try without single quotes if all filmIds are integer.

WHERE films.film_api_id IN ($filmImplode)

answered Mar 23 '16 at 09:34

Radu Lupaescu

47
3

Yep they are strings, and yea the output is showing they are correctly marked. ill edit and show error – Paddy Mar 23 '16 at 09:37

IN statment on a MySQL INNER JOIN php pdo

2 Answers2