PDO placeholder not being quoted....i think

[generalProf]

Hi all.

Intro
so just to give you a idea i moved over from mysqli (none oop method) to PDO and all my queries work 100% except for this one little bugger.

My Code

$query = $link->prepare("SELECT n1.id,n1.category_id,(SELECT name FROM food_categories WHERE id=n1.category_id) AS category_name FROM foods AS n1 WHERE n1.name=:dish");
$query->execute(array(':dish'=>$dish));

What i have tired
First i checked my query in PHPMyAdmin to see if it works and it does if i put single quotes around the :dish placeholder (with the actual food’s name), without the quotes i get a error like i would normally get which said “Unknown column ‘food1’ in ‘where clause’”

So i fired up the Googler and found out that there is a quote function for PDO but that is not for prepared statements because prepared statements auto quotes placeholders for you.

Then i moved on to check if there is any errors in the execution itself and the code i got back was the 00000, which i understands means that there is no problem and the query didn’t have a error.

I also used the:

$link->bindParam(':dish',$dish);

and

$link->bindParam(':dish',$dish,PDO::PARAM_STR);

which both of them didn’t work, so i am sitting here and wondering if PDO is not quoting the placeholder for some reason because that is the only thing i have seen which caused there to be no result.

I have also checked the $dish variable and there is nothing wrong with it, plus i replaced it with 'food1' to see if it’s that but it still does not work.

Thank you for reading.

[Dormilich]

that’s a weird way of writing a JOIN. better use the proper syntax:

SELECT
    f.id
FROM
    food AS f
INNER JOIN
    food_categories AS c
    ON
        c.id = f.category_id
WHERE
    c.name = :dish
;

[generalProf]

Hi @Dormilich

Thank you for the tip, i changed it to fit my needs:

SELECT
    f.id,c.name
FROM
    foods AS f
INNER JOIN
    food_categories AS c
    ON
        c.id = f.category_id
WHERE
    f.name = food1

although it didn’t fix my problem (which i thought would not change anything) i’m not the best at mysql and always great to improve my skill. Do you have a idea of what may be wrong with the query/PDO for not working?

[generalProf]

Hahaha don’t you just love it when you do everything under the sun to figure out what is wrong with your code just to fine out that when you where setting the result $queryResults['name']; you used the wrong column name

So my problem was when i set the $foodCategory i used $queryResults['dishCategory']; as a placeholder for when i get around to going this query and never changed it to the actual column name “name”

Hopefully this will help others to just check over the simple things next time and make sure that it’s not something simple.