Ndtyjky

The Goal of my query is to identify all the suppliers with due invoices for this week. So the goal is just to have one entry per supplier, identifying the ones that should be showed this week.

SO my big question is why a query works online but not on local machine?

To get that i used the group by statement ant it was working so far! Now that i have a copy of what was online on my local server it stoped working as it should.

I have this query running online and everything works great:

$query_facturas_group = "SELECT TbFacturas.PkFacturas, TbFacturas.FkFornecedor, TbFacturas.FacturaData, TbFacturas.FacturaValor, TbFacturas.FacturaNumero, TbFacturas.FacturaEstado, TbFacturas.FacturaDataPagamento, Fornecedores.PkFornecedor, Fornecedores.NomeFornecedor, Fornecedores.EmailFornecedor 

                         FROM TbFacturas 

                         LEFT JOIN Fornecedores ON TbFacturas.FkFornecedor=Fornecedores.PkFornecedor 

                         WHERE TbFacturas.FacturaDataPagamento<'$pay_day' AND TbFacturas.FacturaEstado='0' AND TbFacturas.FacturaTrash='0'

                         GROUP BY TbFacturas.FkFornecedor 

                         ORDER BY Fornecedores.NomeFornecedor ASC";

The problem is that when I try to run it locally (to make some changes and upgrade it) it stopped working (no results).

Also, when I run it straight in the MySQL console, it works great...

I started removing parts of the code and it works (but not giving me the result I'm looking for...)

As soon as I remove the GROUP BY statement it starts working - but not the expected result...

I tried using it with SELECT DISTINCT but with no success as it still shows Repeat Suppliers Name (Fornecedores.NomeFornecedor field).

Any guess??
UPDATE:
I noticed that there is another query in my project that does not work with GROUP BY

• PRINT SCREEN FROM THE RESULT IN HTML:

• PRINT SCREEN RESULT FROM CONSOLE

Could be you are using two different version of mysql one oldest than 5.6 on server and one more recent locally .. the group by behaviour is changed starting fro d.6 and do the fact
you are using group by without aggregation function

this is deprecated in SQL not allowed in most recent version on mysql and for the oldest versione the result for column values not involved in group is unpredictable ..

so if you just want distinct result the you could use DISTINCT avoiding group by

$query_facturas_group = "SELECT DISTINCT TbFacturas.PkFacturas

    , TbFacturas.FkFornecedor

    , TbFacturas.FacturaData

    , TbFacturas.FacturaValor

    , TbFacturas.FacturaNumero

    , TbFacturas.FacturaEstado

    , TbFacturas.FacturaDataPagamento

    , Fornecedores.PkFornecedor

    , Fornecedores.NomeFornecedor

    , Fornecedores.EmailFornecedor 

  FROM TbFacturas 

  LEFT JOIN Fornecedores ON TbFacturas.FkFornecedor=Fornecedores.PkFornecedor 

  ORDER BY Fornecedores.NomeFornecedor ASC";

if you just want to reduce the number of rows the you should use aggreagtion function and group eg min() or max()

$query_facturas_group = "SELECT 

      min(TbFacturas.PkFacturas)

    , TbFacturas.FkFornecedor

    , min(TbFacturas.FacturaData)

    , min(TbFacturas.FacturaValor)

    , min(TbFacturas.FacturaNumero)

    , min(TbFacturas.FacturaEstado)

    , min(TbFacturas.FacturaDataPagamento)

    , min(Fornecedores.PkFornecedor)

    , min(Fornecedores.NomeFornecedor)

    , min(Fornecedores.EmailFornecedor )

  FROM TbFacturas 

  LEFT JOIN Fornecedores ON TbFacturas.FkFornecedor=Fornecedores.PkFornecedor 

  WHERE TbFacturas.FacturaDataPagamento<'$pay_day' AND TbFacturas.FacturaEstado='0' AND TbFacturas.FacturaTrash='0'

  GROUP BY TbFacturas.FkFornecedor 

  ORDER BY Fornecedores.NomeFornecedor ASC";