Merge Results from Queries

[mcmuney]

I need to merge the two sections below (A), but can't get it to work. Basically, I'm trying to pull data where scf_mem_id='$mem_id' OR scf_frnd_id='$mem_id' and perform sort on it. With the current code, it is pulling 1st scf_mem_id='$mem_id' and 2nd scf_frnd_id='$mem_id' and the sorting is only happening within 1st and then 2nd (see C below for details), I need them to sort as one. The table looks like this:

  `scf_id` int(11) NOT NULL auto_increment,
  `scf_frnd_id` int(11) NOT NULL default '0',
  `scf_mem_id` int(11) NOT NULL default '0',
  `scf_status` int(5) NOT NULL default '0',
  `scf_postdate` varchar(30) default NULL,
  `scf_readflg` tinyint(1) NOT NULL default '0',
  `top_positon` tinyint(2) NOT NULL default '-1',
  PRIMARY KEY  (`scf_id`)

<b>A</b>
[code]
function getfriendslevel($mem_id,$level,$flag)
{
$db = New PicSQL;
$sql="select * from sc_friend where scf_mem_id='$mem_id' and scf_status=1 AND top_positon='-1' order by scf_id desc";
$res=$db->select_data($sql);
for ($i=0;$i<count($res);$i++)
{
$sql1="select * from sc_member where scm_mem_id='".$res[$i][scf_frnd_id]."'";
$res1=$db->select_data($sql1);
$arr[$i][mem_id]=$res1[0][scm_mem_id];
$arr[$i][mem_fname]=$res1[0][scm_firstname];
$arr[$i][mem_lname]=$res1[0][scm_lastname];
$arr[$i][mem_sex]=$res1[0][scm_gender];
$arr[$i][mem_flag]=$res1[0][scm_online_flg];

}

$sql="select * from sc_friend where scf_frnd_id='$mem_id' and scf_status=1 AND top_positon='-1'";
$res=$db->select_data($sql);
for ($ii=$i,$c=0;$c<count($res);$ii++,$c++)
{
$sql1="select * from sc_member where scm_mem_id='".$res[$c][scf_mem_id]."'";
$res1=$db->select_data($sql1);
$arr[$ii][mem_id]=$res1[0][scm_mem_id];
$arr[$ii][mem_fname]=$res1[0][scm_firstname];
$arr[$ii][mem_lname]=$res1[0][scm_lastname];
$arr[$ii][mem_sex]=$res1[0][scm_gender];
$arr[$ii][mem_flag]=$res1[0][scm_online_flg];

}

return $arr;
}
[/code]

<b>B</b>
This is an example of hwo it's sorting now. Let's say 1st batch results are
Bob (sci_id=4)
Sam (sci_id=2)
Tom (sci_id=1)

...and 2nd batch results are:

Cathy (sci_9d=3)
Rita (sci_id=5)

The current display would be as:

Bob, Sam, Tom, Rita and Cathy

IT NEEDS TO BE:

Rita, Bob, Cathy, Sam and Tom

I'm not sure how to merge the two results and then apply the sorting.

[Psycho]

You could reduce the number of queries by utilizing JOIN clauses in your queries.

In the first section you first do a query for all records in the sc_friend table where [b]scf_mem_id='$mem_id'[/b]. Then you loop through those records to find records in the sc_member table where [b]scm_mem_id=the scf_frnd_id[/b] in the first resulst. You are basically doing in mutiple loops what you could do with a single query with a join clause.

Try this query. I think it may replace all the other queries you have.
[code]
SELECT *
FROM sc_friend sf
LEFT JOIN sc_member sm
    ON sf.scf_frnd_id = sm.scm_mem_id OR sf.scf_mem_id = sm.scm_mem_id
WHERE sf.scf_mem_id='$mem_id' and sf.scf_status=1 AND sf.top_positon='-1'
ORDER BY sf.scf_id DESC
[/code]