[Resolved] combine and sort two query results

[sunshine66]

Oh great thinkers....

How would I rewrite this so that the results are combined and sort in the order of $lastname, $firstname regardless of which table they came from?

I'm so stuck!

//Database connection has been made, along with the query to get $CoID value
//HTML FORM elements are here

// Find COBRA participants (employees & dependents) for Company ID who are
$query4 = "SELECT * FROM dependentProfile WHERE CompanyID=$CoID AND COBRA='Yes' 
	  ORDER BY DependentLast,DependentFirst"; 
$result4 = mysql_query($query4) or die("Error with query 4 : " . mysql_error() );

$query8 = "SELECT * FROM employeeProfile WHERE CompanyID=$CoID AND COBRA='Yes'
	  ORDER BY EmployeeLast,EmployeeFirst";
$result8 = mysql_query($query8) or die("Error with query 8 : " . mysql_error() );  

// Display all profiles for Company ID in OPTION tags
	while($row8 = mysql_fetch_array($result8))  

	{  

	$lastname = $row8["EmployeeLast"];
	$firstname = $row8["EmployeeFirst"]; 
	$pID = $row8["EmployeeID"];   

	echo "<OPTION VALUE=$pID>$lastname, $firstname</OPTION>";  

	}	
	while($row4 = mysql_fetch_array($result4))  

	{  

	$lastname = $row4["DependentLast"];
	$firstname = $row4["DependentFirst"]; 
	$pID = $row4["DependentID"];   

	echo "<OPTION VALUE=$pID>$lastname, $firstname</OPTION>";  

	}

//HTML FORM elements are here

[Neumy]

I can't be 100% this is perfect. I'm assuming a few things like no employee number is the same (on either db search). But this might give you enough to make it work for you.

 
$name_8 = array();
while($row8 = mysql_fetch_array($result8))   

{   

	$name_8[$row8["EmployeeID"]] = $row8["EmployeeLast"].", ".$row8["EmployeeFirst"];
}     

$name_4 = array();
while($row4 = mysql_fetch_array($result4))   

{   

	$name_4[$row4["DependentID"]] = $row4["EmployeeLast"].", ".$row4["EmployeeFirst"];
} 

$combined = array_merge($name_8,$name_4);
asort($combined);

foreach($combined as $key=>$val)
{
	echo "<OPTION VALUE=$key>$val</OPTION>";
}

I hope this helps

[Tewl]

I would tryin editing your select statements my sql is a bit rusty.

SELECT DependentLast As LastName, DependentFirst As FirstName, DependentID as ID FROM dependentProfile WHERE CompanyID=$CoID AND COBRA='YES' ORDER BY LastName, FirstName


SELECT EmployeeLast As LastName, EmployeeFirst As FirstName, EmployeeID as ID FROM employeeProfile WHERE CompanyID=$CoID AND COBRA='YES' ORDER BY LastName, FirstName

Then do Neumy's idea of merging the arrays

$combined = array_merge(mysql_fetch_array($emp_result), mysql_fetch_array($dep_result));

asort($combined);

• I think there is a way to combine the two select statements into one i just cant remember the syntax for sure.

[sunshine66]

Thanks for the suggestions!

Trying Neumy's method first...
For some reason, getting no functionality and I think it has to do with the '=>' assignment operator in the line (doesn't like the greater than sign):

  foreach($combined as $key=>$val) 

I've even taken examples from the PHP manual and I can't get them to work; for example:

$fruits = array (
    "fruits"  => array ("a"=>"orange", "b"=>"banana", "c"=>"apple"),
    "numbers" => array (1, 2, 3, 4, 5, 6),
    "holes"   => array ("first", 5 => "second", "third")
);

I'm disheartened

[sunshine66]

Okay, it works if I replace :

foreach ($combined as $key => $val) { 
    echo "<OPTION VALUE=$key>$val</OPTION>"; 
}

with:

while (list($key, $value) = each($combined)) {
echo "<OPTION VALUE=$key>$value</OPTION>\n";
}