php - 使用union时mysql查询中的问题

Question

我正在寻找一种在 php 文件中组合不同 mysql 查询的方法，所以这是我的代码:

 $sql_query = "SELECT b.*, 
u.username AS MY_Sender
  FROM table_users u, 
       table_blogs b  
 WHERE b.reciever = '0'  
   AND 
   u.user_id = b.sender  

UNION  

SELECT b.*,
u2.username AS MY_Recipient
  FROM table_users u2, 
       table_blogs b  
 WHERE b.reciever != '0'  
  AND 
  u2.user_id = b.reciever  
";

这段代码工作正常，除非它不能获取 MY_Recipient

在上面的代码中，我需要获取博文的发送者和接收者

使用 Union 这样做是错误的吗？!

Answer 1

我已经猜测了你的表结构，并产生了类似的东西。无论对错，它至少可以帮助您找到合适的解决方案。

两个表，users 和 blogs:

CREATE TABLE `users` (
    `id` int(11) NOT NULL auto_increment,
    `username` varchar(255) NOT NULL,
    PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

CREATE TABLE `blogs` (
    `id` int(11) NOT NULL auto_increment,
 `sender` int(11) NOT NULL,
 `receiver` int(11) NOT NULL,
    PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

添加一些用户:

INSERT INTO `users` (username) VALUES
('Alice'), ('Bob'), ('Carol'), ('Eve');

为一些用户添加博客条目:

INSERT INTO `blogs` (sender, receiver) VALUES
(1,2), (2,1), (3,4), (4,3), (1,4), (4,1);

对于每个博客条目，列出发送者和接收者:

SELECT
    b.id,
 b.sender AS sender_id,
 b.receiver AS receiver_id,
    us.username AS sender_name,
    ur.username AS receiver_name
FROM blogs AS b
JOIN users AS us ON us.id = b.sender
JOIN users AS ur ON ur.id = b.receiver
ORDER BY b.id;

+----+-----------+-------------+-------------+---------------+
| id | sender_id | receiver_id | sender_name | receiver_name |
+----+-----------+-------------+-------------+---------------+
|  1 |         1 |           2 | Alice       | Bob           |
|  2 |         2 |           1 | Bob         | Alice         |
|  3 |         3 |           4 | Carol       | Eve           |
|  4 |         4 |           3 | Eve         | Carol         |
|  5 |         1 |           4 | Alice       | Eve           |
|  6 |         4 |           1 | Eve         | Alice         |
+----+-----------+-------------+-------------+---------------+

更新 1
table_blogs 应该看起来像这样:

CREATE TABLE IF NOT EXISTS `table_blogs` (
  `bid` int(10) NOT NULL AUTO_INCREMENT,
  `content` varchar(255) DEFAULT NULL,
  `date` varchar(14)  DEFAULT NULL,
  `sender` int(10) NOT NULL,
  `reciever` int(10) NOT NULL,
  CONSTRAINT `fk_sender`
    FOREIGN KEY (`sender` )
    REFERENCES `table_users` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_receiver`
    FOREIGN KEY (`receiver` )
    REFERENCES `table_users` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  PRIMARY KEY (`bid`)
) ENGINE=MyISAM AUTO_INCREMENT=1 ;

CONSTRAINT 子句将防止为不存在的用户插入值，并在用户从用户表中删除时删除条目。

更新 2
我认为这就是您想要的，但正如 KM 和 bobince 在评论中所述，它违反了外键约束，这并不是一个好主意。因此，假设没有外键约束，这里有一些额外的插入和修改后的查询:

INSERT INTO `blogs` (sender, receiver) VALUES
(1,0), (0,1), (4,0), (0,4), (2,0), (0,2);

SELECT
    b.id,
 b.sender AS sender_id,
 b.receiver AS receiver_id,
    IFNULL(us.username, ur.username) AS sender_name,
    IFNULL(ur.username, us.username) AS receiver_name
FROM blogs AS b
LEFT JOIN users AS us ON us.id = b.sender
LEFT JOIN users AS ur ON ur.id = b.receiver
ORDER BY b.id;

+----+-----------+-------------+-------------+---------------+
| id | sender_id | receiver_id | sender_name | receiver_name |
+----+-----------+-------------+-------------+---------------+
|  1 |         1 |           2 | Alice       | Bob           |
|  2 |         2 |           1 | Bob         | Alice         |
|  3 |         3 |           4 | Carol       | Eve           |
|  4 |         4 |           3 | Eve         | Carol         |
|  5 |         1 |           4 | Alice       | Eve           |
|  6 |         4 |           1 | Eve         | Alice         |
|  7 |         1 |           0 | Alice       | Alice         |
|  8 |         0 |           1 | Alice       | Alice         |
|  9 |         4 |           0 | Eve         | Eve           |
| 10 |         0 |           4 | Eve         | Eve           |
| 11 |         2 |           0 | Bob         | Bob           |
| 12 |         0 |           2 | Bob         | Bob           |
+----+-----------+-------------+-------------+---------------+