PHP & MySQL Error - Duplicate column name 'user_id'

Question

I get the following Error below from my query, and was wondering how can I fix this problem?

Duplicate column name 'user_id'

Here is My MySQL query.

"SELECT COUNT(users_friends.user_id) FROM ((SELECT *
FROM users_friends
INNER JOIN users ON users_friends.user_id = users.user_id
WHERE users_friends.user_id = '" . $user_id . "' 
AND users_friends.friendship_status = '1')
UNION
(SELECT *
FROM users_friends
INNER JOIN users ON users_friends.friend_id = users.user_id
WHERE users_friends.friend_id = '" . $user_id . "'
AND users_friends.friendship_status = '1')) as friends"

Here is my new query.

SELECT COUNT(user_id) FROM ((SELECT users_friends.user_id
FROM users_friends
INNER JOIN users ON users_friends.user_id = users.user_id
WHERE users_friends.user_id = '" . $user_id . "' 
AND users_friends.friendship_status = '1')
UNION
(SELECT users_friends.user_id
FROM users_friends
INNER JOIN users ON users_friends.friend_id = users.user_id
WHERE users_friends.friend_id = '" . $user_id . "'
AND users_friends.friendship_status = '1')) as friends

Answer 1

You are using SELECT * from , bringing all columns from 2 tables user_friends and users, both of them have a column called user_id. When you use a UNION it is my understanding that a temporary table is created, so MySQL is complaining that you have 2 columns with the same name. Try to explictly define the user_id you want to use for instance SELECT users.user_id, user_friends.abc etc

Edit: If i understand this correctly, you are trying to get user ids of the friends.To me it seems like your previous query would return the $user_id in all records.

Try this:

SELECT COUNT(*) as CNT FROM 
(

(SELECT users.user_id as uid
FROM users_friends
JOIN users ON users_friends.user_id = users.user_id
WHERE users_friends.friend_id = '" . $user_id . "' 
AND users_friends.friendship_status = '1')

UNION

(SELECT users.user_id as uid
FROM users_friends
JOIN users ON users_friends.friend_id = users.user_id
WHERE users_friends.user_id = '" . $user_id . "'
AND users_friends.friendship_status = '1')

) as myfriends

Answer 2

There's a couple of problems here. You obviously (read humor) only want a row count, so no need to SELECT *, true? I'm assuming you want to use a UNION to add rows together... Thus:

SELECT COUNT(*) AS the_count
FROM 
(SELECT user_id AS ID
FROM users_friends
INNER JOIN users ON users_friends.user_id = users.user_id
WHERE users_friends.user_id = '" . $user_id . "' 
AND users_friends.friendship_status = '1'
UNION
SELECT friend_id AS ID
FROM users_friends
INNER JOIN users ON users_friends.friend_id = users.user_id
WHERE users_friends.friend_id = '" . $user_id . "'
AND users_friends.friendship_status = '1'
) AS uf1;

Note: I'm assuming that the rest of the query (inner join, where, etc) works.

Edited, fixing the syntax problem with the ambiguous field names for ya.

SELECT COUNT(*) AS the_count
FROM 
(SELECT uf.user_id AS ID
FROM users_friends uf
INNER JOIN users u ON uf.user_id = u.user_id
WHERE uf.user_id = '" . $user_id . "' 
AND uf.friendship_status = '1'
UNION
SELECT uf.friend_id AS ID
FROM users_friends uf
INNER JOIN users u ON uf.friend_id = u.user_id
WHERE uf.friend_id = '" . $user_id . "'
AND uf.friendship_status = '1'
) AS uf1;