Posted by Steve on 11/30/05 13:53

> SELECT * FROM product INNER JOIN stock ON product.id_product =
> stock.id_product WHERE price < $maxspend AND id_currency = 1 ORDER BY
> product.viewed + product.weight DESC LIMIT 2 ;

> SELECT * FROM product INNER JOIN stock ON product.id_product =
> stock.id_product WHERE price < $maxspend AND id_currency = 1 ORDER BY
> rand() LIMIT 2;

> they each work individually, but i cannot UNIONise them because I think
> ORDER BY works on a single result set. How can I avoid this ?

> Or is it possible to run a query, then append another result resource
> (before fetching the array) to the first in php ?

Use a temporary table.

First create a temp table using CREATE TEMPORARY TABLE
<http://dev.mysql.com/doc/refman/4.1/en/create-table.html>.

Use INSERT INTO ... SELECT to populate it from each of your queries
<http://dev.mysql.com/doc/refman/4.1/en/insert-select.html>.

SELECT all the rows from the temporary table, ordering them however you
wish.

DROP the temporary table
<http://dev.mysql.com/doc/refman/4.1/en/drop-table.html>

---
Steve

[Back to original message]