Innerjoin With Where Clause?

Issue

$InnerJoinQuery = $STD->query("
        SELECT Users.ID, Users.Username, Users.Password, UserInformation.LastName, UserInformation.Firstname, UserInformation.DOB
        FROM Users AS Users 
        INNER JOIN UserInformation AS UserInformation 
        ON Users.ID = UserInformation.UserID WHERE Users.Username=".$_SESSION['real_name']."");
        $InnerJoinArray = $InnerJoinQuery->fetch_array(MYSQLI_ASSOC);

My Above code is causing an error. It works up to the point of my WHERE Clause.

WHERE Users.Username=".$_SESSION['real_name']."

How can i Impliment this into my innerjoin?

Update:

$InnerJoinQuery = $STD->query("
        SELECT Users.ID, Users.Username, Users.Password, UserInformation.LastName, UserInformation.Firstname, UserInformation.DOB
        FROM Users
        INNER JOIN UserInformation
        ON Users.ID = UserInformation.UserID WHERE Users.Username = '".$_SESSION['real_name']."'");
        $InnerJoinArray = $InnerJoinQuery->fetch_array(MYSQLI_ASSOC);



        $_SESSION['UID'] = $InnerJoinArray['ID'];
        $_SESSION['Password'] = $InnerJoinArray['Password'];
        $_SESSION['Firstname'] = $InnerJoinArray['Firstname'];
        $_SESSION['LastName'] = $InnerJoinArray['LastName'];
        $_SESSION['DOB'] = $InnerJoinArray['DOB'];
            print_r($_SESSION);

This returns:

Array ( [real_name] => inhumaneslayer [Password] => [UID] => [Firstname] => [LastName] => [DOB] => )

Which is not expected.

When I change my Query To:

$InnerJoinQuery = $STD->query("
        SELECT Users.ID, Users.Username, Users.Password, UserInformation.LastName, UserInformation.Firstname, UserInformation.DOB
        FROM Users
        INNER JOIN UserInformation
        ON Users.ID = UserInformation.UserID WHERE Users.Username = 'inhumaneslayer'");

I get the expected result:

Array ( [real_name] => inhumaneslayer [Password] => PASSWORDHIDDEN [UID] => 5 [Firstname] => xx [LastName] => xx [DOB] => DOBHIDDEN )

Which is expected.

I am unsetting session by unset($_SESSION); prior to changing the SQL

Solution

You have to enclose the string in quotes:

... Users.Username='".$_SESSION['real_name']."'")

Also – you need to escape the variable properly. How exactly you would do that – depends on the DB library you use. In your case it’s http://php.net/manual/en/mysqli.real-escape-string.php

... Users.Username='". $STD->real_escape_string($_SESSION['real_name']) ."'")

But better – learn how to use prepared statements http://php.net/manual/en/mysqli.prepare.php

$stmt = $STD->query("
        SELECT Users.ID, Users.Username, Users.Password, UserInformation.LastName, UserInformation.Firstname, UserInformation.DOB
        FROM Users AS Users 
        INNER JOIN UserInformation AS UserInformation 
        ON Users.ID = UserInformation.UserID WHERE Users.Username=?");

$stmt->bind_param("s", $_SESSION['real_name']);

$stmt->execute();
$result = $stmt->get_result();

$InnerJoinArray = $result->fetch_array();

Answered By – zerkms

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published