mysql-从一个表和一个列中全选另一个，在其中找到$ var [英] mysql - Select all from one table and one column form another where $var is found

问题描述

Ooooookay.我有两个表客户端和用户.两者都具有AUTO_INCREMENT ID，但客户端表具有可信列，这是外键，并且引用了用户表的ID.

Ooooookay. I have two tables client and users. Both have AUTO_INCREMENT id but client table has credid-column whis is foreign key and references to users table's id.

我想准备一个PHP PDO语句，该语句从客户端表中获取所有列，并且仅从用户表中获取用户名列，其中客户端表的列credid =:var和用户表的列id =:var

I want to prepare a PHP PDO statement that fetches all columns from client-table and only username-column from users-table where client table's column credid = :var and user table's column id = :var

到目前为止，我有类似的东西，但它不起作用

So far I have something like this and it is not working

$userid = $_SESSION['id']; //echoes a number
    $STH = $DBH->prepare("SELECT * FROM client WHERE credid = :id AND username FROM users
    WHERE id = :id");
    $credentials = array(
        ':id' => $userid
    );
    $STH->execute($credentials);

然后这样

    if ($STH->rowCount() > 0) {
        $result = $STH->fetch(PDO::FETCH_ASSOC);
        echo $result['columnfoo'];
        echo $result['anothercolumn'];
        echo etc.
        .
        .
        .

    }

这将返回有关sql语法的错误....

This return errors about sql syntax....

我也尝试过这样的事情:

I also tried something like this:

    SELECT client.*,users.username FROM client,users WHERE client.credid =users.id = :id

不返回错误，但也不返回任何数据... 我应该如何构造我准备好的查询?

Returns no errors but not any data either... How should I construct my prepared query?

推荐答案

您需要使用join:

SELECT c.*, u.username
FROM   client c
JOIN   users u ON u.id = c.credid
WHERE  credid = :id

这篇关于mysql-从一个表和一个列中全选另一个，在其中找到$ var的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！