First, you might want to look into MySQL's natural language full-text search. It returns a relevance value along with the search results.
Second, it is always best to spell out each column. I know it is a hassle, but it will save you wasted hours of query troubleshooting. I only use the asterisk when I know I need every column from a single table only. Even then, it is usually best to spell it out because tables grow and columns can be added. Down the road you may end up pulling more data than necessary.
Usually I try to avoid mixing summary queries and detail queries as a matter of simplicity. But if you must mix them, then do it in your SELECT clause:
SELECT
(SELECT COUNT(order_id) FROM Products WHERE customer_id = C.customer_id) AS order_count,
C.customer_name,
P.product_name
FROM Customers C JOIN Products P USING(customer_id)
This is going to give you a repeat of order_count for each matching row in the product table, but it avoids the pitfalls of mixing JOIN types. This also allows you to use order_count in your ORDER BY statement:
SELECT
(SELECT COUNT(order_id) FROM Products WHERE customer_id = C.customer_id) AS order_count,
C.customer_name,
P.product_name
FROM Customers C JOIN Products P USING(customer_id)
ORDER BY order_count, customer_name, product_name
Obviously, you'll have to selectively ignore the repeated order count in your code:
if($row['order_id'] != $prev_order_id)
print $row['order_id'];
. . .
. . .
$prev_order_id = $row['order_id'];
