如何在选择查询中获取2个表中的列值？

Question

So I have 2 tables named tbl_admin and tbl_users. Each of them has a column named acct_type. All acct_type values in tbl_admin is 1 and in tbl_users, 0. I know it would be easy for me if I have just created one table for them. My question is, how can I query this so that I can redirect the admin to its page and the users to its page.

Here is my code

$uname = $_POST['txt_un'];
$pword = sha1($_POST['txt_pw']);

$query = "SELECT * FROM tbl_admin WHERE uname=? AND pword=?";


$stmt = $dbc->prepare($query);
$stmt->bindParam(1, $uname);
$stmt->bindParam(2, $pword);
$stmt->execute();

$row = $stmt->fetch(PDO::FETCH_ASSOC);

if ($row > 0) {
    if ($row['acct_type'] == 1) {
        header("Location: adminpage.php");
    } elseif ($row['acct_type'] == 0) {
        header("Location: userspage.php");
    }
} else {
    echo "<script>alert('Incorrect username/password');location.href='../index.php'</script>";
}

I know it will not work since I am only querying select from tbl_admin. Your help would be very much appreciated, thank you!

Answer 1

assuming that your table (tbl_admin,tbl_users) have the same number of columns and each user entry in these two tables are unique. "you will have some serious problems in the future if not."

$uname = $_POST['txt_un'];
$pword = sha1($_POST['txt_pw']);

$query = "SELECT * FROM tbl_admin WHERE uname=? AND pword=? UNION SELECT * FROM tbl_users WHERE uname=? AND pword=?";


$stmt = $dbc->prepare($query);
$stmt->bindParam(1, $uname);
$stmt->bindParam(2, $pword);
$stmt->bindParam(3, $uname);
$stmt->bindParam(4, $pword);
$stmt->execute();

$row = $stmt->fetch(PDO::FETCH_ASSOC);

if ($row > 0) {
if ($row['acct_type'] == 1) {
    header("Location: adminpage.php");
} elseif ($row['acct_type'] == 0) {
    header("Location: userspage.php");
}
} else {
echo "<script>alert('Incorrect username/password');location.href='../index.php'</script>";
}

For additional info regarding UNION, please check: http://dev.mysql.com/doc/refman/5.0/en/union.html

Edit: as Half Crazed suggested, this approach should only be done if each user is unique in both tables. If you have additional time, you should revise the way you fetch your users