Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found

0
votes

I currently work on a projet which display a recipe with ingredients checked in a formular. I near to the end but i really not understand what is my error. If someone can explain to me :

Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column '$idingredient' in 'where clause' in /homepages/30/d675437312/htdocs/assets/recettemalin/recetteselectionnee.php:55 Stack trace: #0 /homepages/30/d675437312/htdocs/assets/recettemalin/recetteselectionnee.php(55): PDO->query(' SELECT DIST...') #1 {main} thrown in /homepages/30/d675437312/htdocs/assets/recettemalin/recetteselectionnee.php on line 55

Here's my code where it block :

if (isset($_POST['ingredients'])) {
        $idingredient = implode(",", $_POST['ingredients']);
        echo $idingredient.'<br>';
        $recette = $bdd->query('    SELECT DISTINCT r.*
                                    FROM ingredient i
                                    INNER JOIN contient c
                                       ON i.id_i = c.id_i
                                    INNER JOIN recette r
                                       ON c.id_r = r.id_r
                                    WHERE i.id_i IN ($idingredient)');
    while ($donnees = $recette->fetch()) {
        echo $donnees['nom_r'];
        echo $donnees['type_r'];
        echo $donnees['description'];
    }
}

When I echo $idingredient I get a list of id of ingredient like this : 6,9,11,12,1,5 but it seem's to not be liked by the clause where and I'm wondering why ?

2
phpmysqlpdo

2 Answers

0
votes

This is too long for a comment.

First, parameterize your queries. Don't just dump random strings into the query, because it can cause syntax errors.

Second, your specific problem is that you are putting in a string, but there are no string delimiters. Hence, the value is interpreted as an identifier -- specifically a column alias. I don't recommend this solution, but WHERE i.id_1 IN ('$idingredient') probably does what you want.

Third, don't use IN for a comparison to a single value. It is misleading. Furthermore, the value is a single value, even if it has commas . . . 'butter,margarine' is one value with a comma in it, not two values.

0
votes

You should use " instead of ' in the query string to evaluate variables:

$recette = $bdd->query("  SELECT DISTINCT r.*
                                    FROM ingredient i
                                    INNER JOIN contient c
                                       ON i.id_i = c.id_i
                                    INNER JOIN recette r
                                       ON c.id_r = r.id_r
                                    WHERE i.id_i IN ($idingredient)");

Or you can juste concatinate the variable to the string:

'.....' . $idingredient . '...';