如何加入两个表用户名和消息表

如何加入两个表用户名和消息表

表一:用户 .

|user_id |  username|
                        |-------------------|  
                        |1       |     joe  |
                        |2       |     james|    
                        |3       |     john |
                        --------------------

表二:: - 消息

---------------------
                        |user_id|  msg      |
                        |------ |  -------- |
                        |2      |   hey     |
                        |3      |   hi      |
                        |1      |  sups guys| 
                        |2      |   am good |
                        ---------------------

我想要这样的结果:

--------------------
                        |username| msg      |
                        --------|-----------|
                        |james   |hey       |
                        |john    | hi       |  
                        |joe     | sups guys|
                        |james   | am good  |

最后我怎样才能在网络浏览器上使用php从数据库中回显它谢谢

回答(2)

2 years ago

我认为就像这样:

SELECT u.username, m.msg
FROM users u
INNER JOIN msg m ON m.user_id = u.user_id

应该可以

2 years ago

您需要使用PDO或mysqli扩展来进行数据库操作 .

你的查询应该是

SELECT username, msg FROM msg JOIN users ON msg.user_id = users.user_id

使用PDO的示例:

$host = '127.0.0.1';
$db   = 'test';
$user = 'root';
$pass = '';
$charset = 'utf8';

$dsn = "mysql:host={$host};dbname={$db};charset={$charset}";
$opt = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
$pdo = new PDO($dsn, $user, $pass, $opt);

$stmt = $pdo->prepare('SELECT username, msg FROM msg JOIN users ON msg.user_id = users.user_id');
$stmt->execute();
$usersMessages = $stmt->fetchAll(PDO::FETCH_ASSOC);