如何在Inner Join MYSQL查询中使用PHP变量？

Question

I have been trying for the past day to get mysql to recognize my PhP variable, but I have had no luck so far.

The code:

...connect to db...
session_start();
//Calls up Session stored variable
$currentUsr= $_SESSION['username'];
//SQL Query
$sql= 'SELECT Users.Username, books.* FROM Users
       INNER JOIN UserLinkBooks lb
       ON Users.Username = lb.Username
       INNER JOIN Books
       ON lb.bkTitle = books.Title
       WHERE Users.Username = "$currentUsr"';
$result=mysqli_query($conn,$sql);                                                                
//Error Check
if (!$result) {                                                                                   
   printf("Error: %s
", mysqli_error($conn)); 
   exit();}
//display row
while($row=mysqli_fetch_array($result)){                                                                                                        
    echo "<strong>".$row['Title']."</strong>".$row['Description']."</br>";}   

My issue is that the $currentUsr is not properly calling the username that was passed. After doing an error check on it, it seems to be empty.

What I do not understand is that when I use the code :

$sql = "SELECT * FROM Users WHERE `Username`='$currentUsr'";

The variable is processed and works fine, calling up the book title's and description perfectly. Also, if I manually type in: WHERE Users.UserName = "Bill"'; It works fine.

Some of the other errors I've gotten from various attempts are:

  WHERE Users.UserName = '.'$currentUsr';
  Error: Unknown column '$currentUsr' in 'where clause'

or

  WHERE Users.UserName = '.$currentUsr;  
  Error:  Unknown column 'Bill' in 'where clause'

Any help would be greatly appreciated. Thanks

Answer 1

Your variable is in a single quoted string, preventing interpolation. You can try:

$sql = "SELECT Users.Username, books.* FROM Users
        INNER JOIN UserLinkBooks lb
        ON Users.Username = lb.Username
        INNER JOIN Books
        ON lb.bkTitle = books.Title
        WHERE Users.Username = '" . $currentUsr ."'";

Using concatenation makes the code more readable in my opinion. Having said that, you should look into using parameterized queries as they cut down on injection issues. Mysqli has such capabilities.