Take the 2-minute tour ×

Here it is my query:

$query_products = "
    , p.product_sku
    , p.product_name
    , p.hide_product
    , p.new_style_image
    , p.ext
    , p.product_thumb_image
    , p.product_full_image
    , SUM(s.stoc_final)
    , s.active 
    produse p 
    LEFT JOIN stoc_intern s ON p.product_sku=s.cod_website 
        (" . $product_sku_search . ") 
        OR (" . $product_name_search . ") 
        OR ("  .$product_culoare_search . ")
    AND p.hide_product != 'd' 
GROUP by p.product_sku 
ORDER BY SUM(s.stoc_final) DESC 
LIMIT " . $items_per_page . " 
OFFSET " . $_offset . "";

My problem is: the last field I'm selecting, the s.active one, has in the table the value of "n" but when I display it, it shows empty (echo $s_active); If I change the field s.active with say other field s.description, it will work.

Also, in the table "stoc_intern" some fields that I select do display well, some do not display a thing (even if table data contains info).

Why is that?

share|improve this question
I bet it's because of the non-standard usage GROUP BY. When you omit non-aggregate columns in the GROUP BY clause (which is not allowed in other DBMS) MySQL more or less chooses a random row. –  a_horse_with_no_name Jun 5 '12 at 12:18
what should I do ? do you have a reccomendation ? should i GROUP BY p.product_sku, s.active ? –  Adrian Tanase Jun 5 '12 at 12:19
now it's working. I took your comment's advice and fiddled a bit with the code...so instead of GROUP BY p.product_sku I wrote GROUP BY s.active, p.product_sku. And now it displays as before with the ability of getting the s.active field in the row data. Thanks !! –  Adrian Tanase Jun 5 '12 at 12:28
@a_horse_with_no_name That causes so many issues. I still don't know why they allow it by default. You can shut it off with the only_full_group_by sqlmode option –  Derek Downey Jun 5 '12 at 16:11
@DTest: I agree. It's another one of those stupid MySQL "features" that no one can really understand. –  a_horse_with_no_name Jun 5 '12 at 18:07

1 Answer 1

up vote 5 down vote accepted

a_horse_with_no_name already provided with the answer.

But to disable this "one of those stupid MySQL "features" that no one can really understand", you can set sql_mode to be more strict about it. Consider the following setup in your my.cnf config file:


See documentation. This will not allow you to reference non-grouped columns unless with an aggregation function.

share|improve this answer

Your Answer


