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?

  • 6
    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
  • 2
    @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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.