mysql and php : Data extraction problem

Question

Hi All,

I'm stuck trying to figure out a solution for the problem below. Its quite complicated. So stay with me. :-)

I retrieve a field from the user table which has his friends' user ids in the form of CSV (3,4,5,6,7)

There is another table called transaction which looks something like this

tid    user1   user2   type_of_trade
1       3        4      barter
2       5        6      share
3       6        7      bargain 
4       4        3      barter
5       3        7      share

Now I need to display the transactions of my friends.

So i split the CSV, take them in an array and for each friend i'm running the query

friends = explode(",","3,4,5,6,7");

for($i=0;$i<=count(friends);$i++)
{
// I execute this--SELECT * FROM transaction WHERE user1 = friends[$i] or user2 = friends[$i]
}

Now the same happens for 3,4,5,6, and 7

When the query executes for 4, the same result comes and hence I'm getting duplicates. I've tried everything. But not able to figure out this.

Can someone please suggest me with a better solution or can this itself be modified? PLEASE!

Answer 1

In this particular case, do not explode it.

SELECT * FROM transaction WHERE user1 IN (3, 4, 5, 6, 7) OR user2 IN (3, 4, 5, 6, 7)

Answer 2

You should myke use of the mysql IN() function. This will guarantee unique results.

SELECT * FROM transactions WHERE user1 IN (3,4,5,6,7) OR user2 IN (3,4,5,6,7);

Answer 3

You could do a IN comparison:

SELECT DISTINCT *
FROM transaction
WHERE user1 IN (3,4,5,6,7) OR user2 IN (3,4,5,6,7)

Answer 4

If you can change the database design at all, I would recommend using a Many to Many relationship for your users friends.

This will allow you to store a list of a users friends without needing to parse the CSV.

An implementation of this in PHP & mysql is here; tonymarston.net, was just the first result for google: 'mysql many to many'.