Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Here it is my query:

$query_products = "
SELECT 
    p.Id
    , 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 
FROM 
    produse p 
    LEFT JOIN stoc_intern s ON p.product_sku=s.cod_website 
WHERE 
    (
        (" . $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
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

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:

[mysqld]
sql_mode = ONLY_FULL_GROUP_BY

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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