Having issue with MySQL JOIN query (query runs fine in my remote client but error in PHP prepared statement).

Question

I'm getting thrown the following error in my second query:

("SELECT DISTINCT first_name, last_name FROM users INNER JOIN teams ON (teams.league_id = :id) AND (users.id = teams.manager_id");

The error is:

ERROR: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1

The strange thing is that the query runs fine in Navicat. The only difference is the :id placeholder, but I got no issues with that in the preceding query.

Here's the full function without any html view stuff:

function populateLeague() {
    global $con;
    $leagueID = 1;
    try{
        $stmt = $con->prepare("SELECT team_name FROM teams WHERE league_id = :id");
        $stmt->bindParam(':id', $leagueID);
        $stmt->execute();

        while ($row = $stmt->fetch()) {
            $teamNames[] = $row;
        }
    } catch (PDOException $e) {
        echo 'ERROR: ' . $e->getMessage();
        return false;
    }
    try{
        $stmt = $con->prepare("SELECT DISTINCT first_name, last_name FROM users INNER JOIN teams ON (teams.league_id = :id) AND (users.id = teams.manager_id");
        $stmt->bindParam(':id', $leagueID);
        $stmt->execute();

    } catch (PDOException $e) {
        echo 'ERROR: ' . $e->getMessage();
        return false;
    }
}

PS I know global $con isn't best practice but haven't fully grasped one of the solutions yet.

Answer 1 · 2014-08-01T08:49:00Z

August 1, 2014 8:49am

Wow, I just caught it.

The original incorrect code:

$stmt = $con->prepare("SELECT DISTINCT first_name, last_name FROM users INNER JOIN teams ON (teams.league_id = :id) AND (users.id = teams.manager_id");

The correct code:

$stmt = $con->prepare("SELECT DISTINCT first_name, last_name FROM users INNER JOIN teams ON (teams.league_id = :id) AND (users.id = teams.manager_id)");

I was simply missing the closing parentheses after manager_id at the end. Amazing what 12 hours away from the computer will do. It was working fine in the client because, of course, the query wasn't wrapped in parentheses.

Answer 2 · 2014-08-01T04:30:37Z

August 1, 2014 4:30am

Hi,

Sorry for the late response, but, assuming you're still experiencing this issue, have you tried binding $leagueID as an integer?

For instance:

$stmt->bindParam(':id', $leagueID, PDO::PARAM_INT);

Welcome to the Treehouse Community

Looking to learn something new?

Łukasz Czuliński

Łukasz Czuliński

Having issue with MySQL JOIN query (query runs fine in my remote client but error in PHP prepared statement).

2 Answers

Łukasz Czuliński

Łukasz Czuliński

Joseph Kato

Joseph Kato

Łukasz Czuliński

Łukasz Czuliński