SQL SELECT query causing various column error

I am trying to figure out the mail junk section on my site, and it turns out to be unpleasant, I had problems with the request before and finally its work, but now pagination also causes problems, I get 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 is as follows:

$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 is a line mysql_fetch_array

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 that you guys can give, it was almost 2 days trying to figure this part of the site out and I need to do this. Thanks for any feedback.

+3
source share
3 answers

, 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`
+3

( UNION) COUNT (*).

SELECT COUNT(*) as `num` FROM user_inbox WHERE user_inbox.receiver_user_id='4' AND user_inbox.mail_deleted ='1' UNION SELECT COUNT(*)  FROM user_outbox WHERE user_outbox.sender_user_id='4' AND user_outbox.mail_deleted ='1' 
+3

You should investigate the query "$ query" more. It looks like you are choosing a different number of columns in the query that you defined for the variable "$ query" in your PHP (I suppose) code (at least the error says this).

0
source

All Articles