James Tu wrote:
> The database server and the web server are on separate machines.
> Table A contains a record for each user.
> Let's say Table B contains 'relationship' information. They can be of
> type 'friend' or 'family'.
> If a user knows another user, this relationship would be kept in this
> table, along with the type of relationship. Table B can get big.
> 10,000's or maybe 100,000's.
>
>
> I'm doing a query in PHP and want to end up with two arrays.
> One for type friend and one for type family.
>
> Which is better:
> (Method 1) Do ONE query for all the records that meet a certain
> criteria (let's say 'active'). Then use PHP to loop through the
> results and put each record into either the friend array or the family
> array.
>
> (Method 2) Do TWO queries. One just for friend. Loop through the
> records and put into friend array;
> Then do another query for family...and loop through again.
>
>
> Method (1) needs to evaluate an IF statement in PHP for every record.
> Method (2) hits the database twice, but doesn't require a PHP IF.
>
> (Should I take an extra hit on the database and use Method 2?)
>
> -James
>
Either way, I think you are running into a problem with just having two
arrays. Keep in mind that the relationship is relative, so to speak. A
person who is a friend is not an absolute friend; they are going to be a
friend of somebody else.
With that in mind, assuming that you just want two "absolute" arrays,
here's what I would suggest (and this is a shot in the dark)
Given:
USER
USER_ID
'more columns
AND
RELATIONSHIP
RELATIONSHIP_ID
FRIEND_A
FRIEND_B
$query = "SELECT USER.*,RELATIONSHIP_DESCRIPTION FROM USER LEFT JOIN
RELATIONSHIPS ON (USER.USER_ID = RELATIONSHIP.FRIEND_A OR USER.USER_ID =
RELATIONSHIP.FRIEND_B)";
$retval = mysql_query($query) or die(mysql_error);
while ($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
$array[$row["USER_ID"];
}
--
The NCP Revue -- http://www.ncprevue.com/blog
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
