How to use a PHP variable in Inner Join MYSQL query?

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\n", 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

PHP strings behave differently depending on whether they're double or single quoted. — Sam Dufel, May 21 '13 at 22:03
Just sure that your user isn't Bobby Tables (http://xkcd.com/327/). Any user supplied input should be wrapped in the `mysql_real_escape_string`, or better yet: http://www.php.net/manual/en/mysqli.prepare.php — Joshmaker, May 21 '13 at 22:16
Some friendly advice would be to look up the user information using the User.id instead of the User.UserName. This is part of a larger concept known as 'data normalization' and learning it will save you may hairs because in a UTF-8 character set, 'Bill' and 'Bïll' and 'Bîll' would accidentally end up being the same user, but foreign keys 1001, 1802, and 2242 are clearly different users. — Strixy, May 21 '13 at 22:24

score 1 · Accepted Answer · answered May 21 '13 at 22:02

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.

score 0 · Answer 2 · edited May 23 '17 at 11:57

0

When you're using variables inside strings, you should put these strings within double, not single quotes, otherwise the variables are not replaced with their values.

Also check this question: What is the difference between single-quoted and double-quoted strings in PHP?

edited May 23 '17 at 11:57

Community

1
1

answered May 21 '13 at 22:03

Filippos Karapetis

4,367
21
39

score 0 · Answer 3 · edited May 21 '13 at 22:26

0

If you use double quotes, then put your variables in curly braces - else use Concatinatoin with the dot.

edited May 21 '13 at 22:26

Undo

25,519
37
106
129

answered May 21 '13 at 22:07

Haben

95
5

How to use a PHP variable in Inner Join MYSQL query?

3 Answers3