mysql and php : Data extraction

Question

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?

kmkaplan · Accepted Answer · 2009-02-25 11:58:19Z

3

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)

answered Feb 25, 2009 at 11:58

kmkaplan

19k4 gold badges55 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kmkaplan Over a year ago

My understanding is that gnanesh wants all transactions even if a pair of friends did multiple transactions. Whats more there is a supposedly distinguishing tid column so they’ll always be distinct.

Gumbo · Accepted Answer · 2009-02-25 11:59:58Z

0

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)

answered Feb 25, 2009 at 11:59

Gumbo

657k112 gold badges792 silver badges852 bronze badges

Comments

garrow · Accepted Answer · 2009-02-25 12:07:40Z

0

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

answered Feb 25, 2009 at 12:07

garrow

3,4891 gold badge24 silver badges24 bronze badges

Comments

soulmerge · Accepted Answer · 2009-02-25 12:32:40Z

0

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

edited Feb 25, 2009 at 12:32

answered Feb 25, 2009 at 11:59

soulmerge

76.2k20 gold badges121 silver badges160 bronze badges

Collectives™ on Stack Overflow

mysql and php : Data extraction

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related