Sort a dropdown built from two seperate queries? 1

[Apollo6]

The following snippet populates a dropdown with a date/company. I must populate the dropdown from two different tables(databases). This is working fine but going by date, all the records from one company appear at the top, then the dates start again with the second company at the bottom of the dropdown. I wanted to see if I could sort the dropdown by date once the dropdown is loaded from both results(companies) by date.

Thanks for any suggestions.


if ($s_count > 0 And $t_count > 0)
{
echo "<select name='perend'>\n";
while ($rows = odbc_fetch_row($s_result11)) {
$perend = odbc_result ($s_result11,'transdate');
$company = odbc_result ($s_result11,'audtorg');
$pattern = "/(\d{4})(\d{2})(\d{2})/";
$mod_s_date = preg_replace($pattern,"$2/$3/$1",$perend);
$out = $mod_s_date . ' ' . $company;
echo '<option value="'.$out.'">'.$out.'</option>'."\n";
}
while ($rows = odbc_fetch_row($t_result11)) {
$perend = odbc_result ($t_result11,'transdate');
$company = odbc_result ($t_result11,'audtorg');
$pattern = "/(\d{4})(\d{2})(\d{2})/";
$mod_s_date = preg_replace($pattern,"$2/$3/$1",$perend);
$out = $mod_s_date . ' ' . $company;
echo '<option value="'.$out.'">'.$out.'</option>'."\n";
}
echo "</select>\n";
}

 

[DRJ478]

Accumulate the produced lines in an array.
Apply sort($out). All values will be sorted from lowest to highest, which should take care of your requirement.
Collapse the array with implode("\n",$out) and print the resulting string.
Eh voila!

 

[hos2]

there is a way to join 2 queries and sort them afterwards but I forgot the command for it perhaps look in the mysql forum

 

[Apollo6]

DRJ478-

That sounds like exactly what I want to do but I am not sure I understand exactly how to apply your solution to the code I currently have. Could you point me in a direction or give an example using my existing code?

Additionally, once your solution is applied, you mentioned sort($out) would sort from lowest to highest. Can this be done where it would sort highest to lowest. In other words, the most current dates be first in the dropdown?

 

[DRJ478]

if ($s_count > 0 And $t_count > 0) {    
	echo "<select name='perend'>\n";
	while ($rows = odbc_fetch_row($s_result11)) {
		$perend = odbc_result ($s_result11,'transdate');
		$company = odbc_result ($s_result11,'audtorg');
		$pattern = "/(\d{4})(\d{2})(\d{2})/";
		$mod_s_date = preg_replace($pattern,"$2/$3/$1",$perend);
		$out = $mod_s_date . ' ' . $company;
		[COLOR=red]# instead of echo write it to the array[/color]
		$dataSet[] = '<option value="'.$out.'">'.$out.'</option>'."\n";
	}
	while ($rows = odbc_fetch_row($t_result11)) {
		$perend = odbc_result ($t_result11,'transdate');
		$company = odbc_result ($t_result11,'audtorg');
		$pattern = "/(\d{4})(\d{2})(\d{2})/";
		$mod_s_date = preg_replace($pattern,"$2/$3/$1",$perend);
		$out = $mod_s_date . ' ' . $company;
		[COLOR=red]# same here[/color]
		$dataSet[] = '<option value="'.$out.'">'.$out.'</option>'."\n";
	}
	# sort
	sort($dataSet);
	# now collapse and output
	echo implode('',$dataSet);
	# close select
	echo '</select>';
}

That should be it.
Instead of echoing the line with the option it goes into an array. The array is later sorted and imploded.
All array elements differ only in what $out looks like. The sort should therefore work in ascending order.


The code is not tested, so let me know what happens.

 

[Apollo6]

DRJ478-

Works great but in ascending order as you mentioned. I am looking at the manual to see if a different sort function would get it in descending order.

Example of Results:

01/21/2005 TOPLLC
01/22/2001 TOPLLC
01/22/2002 TOPLLC
01/22/2003 TOPLLC
01/22/2004 TOPLLC
02/07/2002 TOPLLC
02/08/2003 SOINC
02/08/2003 TOPLLC
03/15/2001 TOPLLC
03/15/2002 TOPLLC
04/07/2001 SOINC
04/07/2001 TOPLLC

I understand why it is sorting the way it is but don't understand why, even in ascending order, it isn't putting the 2005 record on the bottom of the list. Is the sort() looking at it as a value vs. a date or something along those lines?

 

[DRJ478]

Try rsort();

 

[DRJ478]

Also, we are assuming different date formats. Your date format is not sortable in an alphanueric way. It is sorted char by char from the left.
A format like 2005-01-01 YYYY-MM-YY is sortable.
Solution:
Create the array with a version of $out that formats the date in a sortable format + company name.
Then assign the sortable version to the array key:
$dataSet[$sortable] = 'echo <option.....
The function krsort() will sort the array reversly by the key.
That's it.

 

[Apollo6]

Sounds good and right on with the situation. I'm attempting the changes now. Thanks for the help and I'll let you know how it turns out.