CODEWITHSUNDEEP
phpmysql
gnanesh
gnanesh

Reputation: 101

mysql and php : Data extraction

I'm stuck trying to figure out a solution for the problem below. It's 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, put 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 I'm unable to figure this out.

Can someone please suggest a better solution or can this itself be modified?

Upvotes: 1

Views: 235

Answers (4)

soulmerge
soulmerge

Reputation: 75784

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);

Upvotes: 0

garrow
garrow

Reputation: 3489

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'.

Upvotes: 0

Gumbo
Gumbo

Reputation: 655825

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)

Upvotes: 0

kmkaplan
kmkaplan

Reputation: 18970

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)

Upvotes: 3

Related Questions