php - SQL SELECT 查询导致不同的列错误

Question

我正在尝试整理我网站的邮件垃圾部分，但事实证明这很麻烦，我之前遇到过查询问题并最终使其正常工作，但现在分页也造成了麻烦，我收到此错误

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\Arken\pagination\function.php on line 9
SELECT COUNT(*) as `num` FROM user_inbox WHERE user_inbox.receiver_user_id='4' AND user_inbox.mail_deleted ='1' UNION SELECT * FROM user_outbox WHERE user_outbox.sender_user_id='4' AND user_outbox.mail_deleted ='1'

The used SELECT statements have a different number of columns

第 9 行周围的代码如下所示

$query = "SELECT COUNT(*) as `num` FROM {$query}";
        $row = mysql_fetch_array(mysql_query($query)) or die($query."<br/><br/>".mysql_error());;
        $total = $row['num'];

第 9 行是 mysql_fetch_array 行

我的 user_inbox 表是这样的

+------------+---------------+------+-----+----------+
| Field             | Type          | Extra          |
+------------+---------------+------+-----+----------+
| message_id        | int(11)       | auto_increment |
| receiver_user_id  | int(11)                        |
| receiver_username | varchar(255)                   |
| sender_user_id    | int(11)                        |
| sender_username   | varchar(255)                   |
| mail_subject      | varchar(255)                   |
| mail_message      | text                           |
| mail_date_sent    | datetime                       |
| mail_viewed       | enum('0','1')                  |
| mail_deleted      | enum('0','1')                  |
+------------+---------------+------+-----+----------+

我的 user_outbox 表看起来像这样

+------------+---------------+------+-----+----------+
| Field             | Type          | Extra          |
+------------+---------------+------+-----+----------+
| message_id        | int(11)       | auto_increment |
| sender_user_id    | int(11)                        |
| sender_username   | varchar(255)                   |
| receiver_user_id  | int(11)                        |
| receiver_username | varchar(255)                   |
| mail_subject      | varchar(255)                   |
| mail_message      | text                           |
| mail_date_sent    | datetime                       |
| mail_viewed       | enum('0','1')                  |
| mail_deleted      | enum('0','1')                  |
+------------+---------------+------+-----+----------+

我真的很感激你们能提供的任何帮助，我花了将近 2 天的时间来整理网站的这一部分，我需要完成它。感谢您的任何反馈。

Answer 1

问题是您使用 UNION 返回不同数量的列。

SELECT COUNT(*) as `num` FROM user_inbox ...
UNION
SELECT * FROM user_outbox ...

您可以让 MySQL 为您计算总行数:

SELECT COUNT(*) AS `num`
FROM
(
     SELECT * FROM user_inbox ...
     UNION ALL
     SELECT * FROM user_outbox ...
) AS your_table_alias

或者:

SELECT 
    (SELECT COUNT(*) FROM user_inbox ...) +
    (SELECT COUNT(*) FROM user_outbox ...) AS `num`