Best Way to Combine Results from 2 mySQL Searches, into One Sorted List?

[Vik Rubenfeld]

I have to search 2 mySQL tables, and show the user a single sorted list
that contains all the results from both mySQL queries.

My question is, how do you get all the resulting items from both
databases into one single, sorted list, to present to the user?

I guess one way would be, to use PHP to copy all the results from each
database, into a PHP array, and then sort it. Is that how this kind of
thing is usually done?

Thanks in advance to all for any info.

[Rik]

On Mon, 20 Aug 2007 23:32:46 +0200, Vik Rubenfeld
<vi**@mindsprin g.com.invalidwr ote:

I have to search 2 mySQL tables, and show the user a single sorted list
that contains all the results from both mySQL queries.

I'm curious about the data/tables, it might be possible to do all this on
a MySQL level instead of PHP, which would usually be better.

My question is, how do you get all the resulting items from both
databases into one single, sorted list, to present to the user?

$list = array();

$r1 = mysql_query("En ter query one here");
while($row = mysql_fetch_ass oc($r1)) $list[] = $row;

$r2 = mysql_query("En ter query two here");
while($row = mysql_fetch_ass oc($r2)) $list[] = $row;

//untested....:
$fieldname = 'foo'; //<- enter the fieldname to sort by
usort($list, create_function ('$a,b',"if(\$a['{$fieldname}'] ==
\$b['{$fieldname}']) return 0; return \$a['{$fieldname}']
\$b['{$fieldname}'] ? 1 : -1;");

I guess one way would be, to use PHP to copy all the results from each
database, into a PHP array, and then sort it. Is that how this kind of
thing is usually done?

The database software is optimised for this kind of stuff, so if it can be
done there that would be highly preferable.
--
Rik Wasmus

[ZeldorBlat]

On Aug 20, 5:32 pm, Vik Rubenfeld <v...@mindsprin g.com.invalidwr ote:

I have to search 2 mySQL tables, and show the user a single sorted list
that contains all the results from both mySQL queries.

My question is, how do you get all the resulting items from both
databases into one single, sorted list, to present to the user?

I guess one way would be, to use PHP to copy all the results from each
database, into a PHP array, and then sort it. Is that how this kind of
thing is usually done?

Thanks in advance to all for any info.

Use union:

<http://dev.mysql.com/doc/refman/5.0/en/union.html>

[Vik Rubenfeld]

Awesome. Thanks very much, guys!