外键不等于主键

Question

So my query looks like this

`$dbUser = "SELECT * FROM accaount, role WHERE Username = :username AND Password = :password UNION SELECT * FROM role , accaount WHERE Role_idRole = idRole ";

$query = $conn->prepare( $dbUser);
$query->bindParam(":username", $Username);
$query->bindParam(":password", $Password);
$query->execute();`

The mistake is UNION SELECT * FROM role , accaount WHERE Role_idRole = idRole. what I try to do is to make my foreign key (Role_idRole) equal to my primary key from anothor table called role with the primary key of idRole. What i try to do is to look if your account is an admin account or not . so if i am an admin then Name = 1 of not Name = 0. Name is in the table from role.

Here is my full code for login

`<?php
if(!isset($_SESSION))
{
    session_start();
}
require('dbConnection.php');

    $Password = $_POST['LPassword'];
    $Username = $_POST['LUsername'];

    $dbUser = "SELECT * FROM accaount, role WHERE Username = :username AND Password = :password UNION SELECT * FROM role , accaount WHERE Role_idRole = idRole ";

    $query = $conn->prepare( $dbUser);
    $query->bindParam(":username", $Username);
    $query->bindParam(":password", $Password);
    $query->execute();

    $result = $query->fetch(PDO::FETCH_ASSOC);

    var_dump($result);

    if ($result["Username"] == $Username && $result["Password"] == $Password) {
        if($result['Name'] == 0) {
            $_SESSION['LOGINUsername'] = $Username;
            echo "Member";
        }
        $tpl->assign('USERNAME', $Username);
        if($result['Name'] == 1) {

            $_SESSION['LOGINAdmin'] = $Username;
            echo "Admin";
        }
    }
    else {
        echo "FAIL!";
}`

And I know account is misspelled but thats not the mistake its also like that in my database. also this is what var dump shows

array (size=8)

'idAccaount' => int 2

'Username' => string 'rwere' (length=5)

'Password' => string 'rwere' (length=5)

'salt' => string '380' (length=3)

'Reset' => null

'Role_idRole' => string '45' (length=2)

'idRole' => string '40' (length=2)

'Name' => int 1

as you can see this person had to be a member not a admin. Name int had to be 0 if we look at idRole 45.

I am really confused.

Answer 1

$dbUser = "SELECT accaount.Username, accaount.Password, accaount.Role_idRole, role.Name FROM accaount INNER JOIN role ON accaount.Role_idRole=role.idRole WHERE accaount.Username = :username AND accaount.Password = :password AND Role_idRole = idRole";