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
– Sam Dufel, Commented May 21, 2013 at 22:03
Just sure that your user isn't Bobby Tables (xkcd.com/327). Any user supplied input should be wrapped in the mysql_real_escape_string, or better yet: php.net/manual/en/mysqli.prepare.php — Joshmaker
– Joshmaker, Commented May 21, 2013 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
– Strixy, Commented May 21, 2013 at 22:24

webbiedave · Accepted Answer · 2013-05-21 22:02:47Z

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.

answered May 21, 2013 at 22:02

webbiedave

49k8 gold badges92 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:57:23Z

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, 2017 at 11:57

CommunityBot

11 silver badge

answered May 21, 2013 at 22:03

Filippos Karapetis

5,20423 silver badges39 bronze badges

Comments

Undo · Accepted Answer · 2013-05-21 22:26:02Z

0

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

edited May 21, 2013 at 22:26

Undo♦

25.7k38 gold badges114 silver badges132 bronze badges

answered May 21, 2013 at 22:07

Haben

955 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related