Problem with query

[danielson2k]

Ok, so I have two tables in my database, users and message_info.

Users consists of the following fields:

id
username
password
email

message_info consists of:

message_info_id
user_id
blocked_ids
message_count

In the message_count table I am storing a comma seperated list of user ids in the blocked_ids field for example:

blocked_ids: 2,1,4,6

Using this list, I want to get all the corresponding usernames from the user table. I am currently using the following SQL:

SELECT username, id FROM users, message_info WHERE users.id IN (message_info.blocked_ids)

altho this only returns one row (the first id in the blocked_ids field).

Where am I going wrong with the query?

[sdjensen]

You need to like the users and the message_info table together like this...

SELECT username, id FROM users, message_info WHERE users.id IN (message_info.blocked_ids) and users.id = message_info.user_id

[danielson2k]

Oops, forgot about that, but it still only returns the first username from the list.

I realise I could use a seperate query to grab the blocked_ids list but I want to use as few queries as possible ideally.

[sdjensen]

I don't think there is a problem with the select statement, I think the problem lies in the rest of your code.

Please show us the rest of the code.

[danielson2k]

Ok, I simply want it to display a list of usernames using the blocked_ids list:

$sql = "SELECT username, id FROM users, message_info WHERE users.id IN (message_info.blocked_ids) AND users.id = message_info.user_id";

$results = mysql_query($sql);

while($results_array = mysql_fetch_array($results)){

echo($results_array['username']."<BR>");

}

[sdjensen]

nope, that seen to be right......are you sure you have more than one user and more messages? ;-)

[danielson2k]

I do indeed. My guess is that I cant use the list directly. Ill try using a seperate query to put it into the above query.

Anyway, thanks for the help 🙂