Issue with a query, var_dump says bool(false)

Question

So what I'd really like to do is combine the two queries. I broke them up into two to help me figure out where the issue is. sql2 is where the issue is. When I run it in phpMyAdmin (without WHERE)it works so what's going on here?

$holidayID = formval('holidayID');  

            $sort= formval('sort', 'rating');
            $dir = formval('dir', 'ASC');

            $sql = "SELECT recipeID, holidayID, recipeName, rating
                FROM recipes
                WHERE holidayID = $holidayID
            ORDER BY $sort $dir ";
//execute

$result = mysqli_query($dbc, $sql);

$sql2 = "SELECT recipes.holidayID, holidays.holidayName

            FROM recipes
            JOIN holidays ON recipes.holidayID=holidays.holidayID
            WHERE holidayID = $holidayID
            LIMIT 1";
$result2 = mysqli_query($dbc, $sql2);

var_dump($result2);

The first query works fine. So what am I doing wrong? Thank you for your time.

Your code is vulnerable to SQL injections. Please learn to use [prepared statements](https://www.youtube.com/watch?v=nLinqtCfhKY). — tereško, May 14 '17 at 21:12
Although, it looks like the `holidayID` column is ambiguous - you need to specify it - is it from the `recipes` or `holidays` table? — Qirel, May 14 '17 at 21:13
You're comparing `recipes.holidayID` to `holidays.holidayID`, so when you later specify `holidayID`, it doesn't know which table to look in. Specify it manually, or better yet, you should probably just have an `ID` field in the `holidays` table :) — Obsidian Age, May 14 '17 at 21:14

score 1 · Accepted Answer · edited May 23 '17 at 11:47

The problem with your query is that the holidayID in your WHERE condition is ambiguous - MySQL doesn't know if you mean from the recipes or holidays table. Specify it, like you do when selecting and joining,

SELECT recipes.holidayID, holidays.holidayName
FROM recipes
JOIN holidays ON recipes.holidayID=holidays.holidayID
WHERE holidays.holidayID = $holidayID -- Specify the column, here its holidays
LIMIT 1

You should also note that this query might be vulnerable to SQL injection, and that you should utilize prepared statements in order to protect your database against that.

How can I prevent SQL injection in PHP?

Using proper error-reporting, with mysqli_error($dbc);, MySQL would've told you this as well.

PHP.net on mysqli_error();

Issue with a query, var_dump says bool(false)

1 Answers1