SQL Syntax Error - PHP/MySQL

Question

Can anyone tell me what's going on here. I'm not sure why this code is not working and throwing the following error:

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 'AND role != 7 AND role != 4' at line 3

    $sql = 'SELECT * FROM tblusers
     INNER JOIN permissions on tblusers.usrID = permissions.user_id
     WHERE permissions.team_id='.$team_id.' AND role != 7 AND role != 4';

 require("connection.php");

 $result = mysql_db_query($DBname,$sql,$link) or die(mysql_error()); 

 while($row = mysql_fetch_assoc($result))
 {
      $message->addTo($row['usrEmail'], $row['usrFirst'] . ' ' . $row['usrLast']);
    }

I know that the variable $team_id is working fine, because if I "echo" it, it works fine. Any ideas on what I'm doing wrong here? Thanks!

Answer 1

echo out $sql, try the statement in the database or paste it here so we can debug it. I initially suspected that you needed quotes around the variable but you probably don't since its a number.

Do both tables have a row column or does just one table have it?

Answer 2

I get that exact error message if $team_id is empty - are you sure it's set at that point in the code?

Answer 3

By using prepared statements you can avoid quotes problems.

$dbConn = new mysqli("127.0.0.1", "username", "password", "db");
$stm = $dbConn->prepare("SELECT * FROM tblusers WHERE team_id = ?");
$stm->bind_param("i", $team_id); /* 'i' for an integer */
$stm->execute();

Answer 4

role field is ambiguous try tblusers.role