SQL SELECT query causing different column error

Question

i am trying to sort out the mail trash section of my site and it is proving troublesome, i had problems with the query before and finally got it to work, but now the pagination is also causing trouble, i am getting this error

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

The code surrounding line 9 looks like this

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

Line 9 being the mysql_fetch_array line

My user_inbox table looks like this

+------------+---------------+------+-----+----------+
| 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')                  |
+------------+---------------+------+-----+----------+

And my user_outbox table looks like this

+------------+---------------+------+-----+----------+
| 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')                  |
+------------+---------------+------+-----+----------+

I would really appreciate any help you guys can give, its been nearly 2 days trying to sort out this part of the site and i need to get it done. Thanks for any feedback.

Answer 1

The problem is your use of UNION returning a different number of columns.

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

You can get MySQL to total the row count for you:

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

Or:

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