Abusive use of long database query? I somehow acquired the idea that I should reduce the number of trips to the database as much as possible and so I use a lot of very long queries. 

For example, I had a function that does this:

$shelf = get_shelves($shop_id)
foreach($shelf)
{
    $weight = get_weight($shelf);
}


Everytime a user "view shop" it will list all the shelves, and when I see the loop, I thought I should "optimise" this, so that I only go to the database once. But I ended up with a long query:

$shelf = "SELECT * FROM shelf WHERE shop_id=?";
$weight = "SELECT tray.shelf_id, SUM(egg.weight) AS total_weight FROM tray LEFT JOIN egg WHERE egg.tray_id=tray.tray_id GROUP BY shelf_id";        
$query = "SELECT * FROM ($shelf) AS shelf LEFT JOIN ($weight) AS weight ON shelf.shelf_id=weight.shelf_id";


With this, I didn't have to get_weight() anymore.

Is this "optimisation" at all, is it acceptable? Is there a rule of thumb so that programmers and DBA can be happy at the same time?
You are on the right track about trying to reduce trips to the database. I think the rule of thumb would be: whatever gives the best overall performance ;-)

A single query will almost always outperform N queries. Sometimes you may have the choice between two single queries: one that's extremely complex but loads exactly the data you need, and another that's simple but loads more data than you need and you have to filter the result yourself in code. When the database is heavily loaded, the simple query can be faster.

That said, your final $query is not optimal. This would be better:

SELECT col1, col2 FROM shelf
LEFT JOIN (
    SELECT tray.shelf_id, SUM(egg.weight) AS total_weight FROM tray
    LEFT JOIN egg USING (tray_id)
    GROUP BY shelf_id
) AS weight USING (shelf_id)
WHERE shop_id = ?


Of course col1 and col2 are not real columns, I just wanted to emphasize that you should always write the column names explicitly. It's faster to load only what you really need. It also protects you from changes in the column order.

Also notice that I removed one sub-query, which should also make it faster.