Can you see the problem --- correct query should be with "group by t1.cat" and work in new server Like works in old server?

CAN you debug this

old MySQL server works
$database->query(‘SELECT * FROM cars_new4 t1 INNER JOIN prices4 t2 ON t1.cat=t2.cat group by t1.cat ;’);

new MySQL Not work - error file not load
$database->query(‘SELECT * FROM cars_new4 t1 INNER JOIN prices4 t2 ON t1.cat=t2.cat group by t1.cat ;’);
in new server - new mysql this loads only without " group by t1.cat"
$database->query(‘SELECT * FROM cars_new4 t1 INNER JOIN prices4 t2 ON t1.cat=t2.cat ;’);

can you see the problem — correct query should be with “group by t1.cat” and work in new server Like works in old server???

What versions of PHP and MySQL do you have on your old and new servers?

Cent os 6.8
cent os 7.7

currently i do not know for mysql but linux ^^^

anyway most recent Maria DB / MySQL where is the problem in query??? IS may needed ORDER BY t1.aa ??? I added some rows in the table cars_new4

judging from the description of your problem, and the use of GROUP BY along with the dreaded, evil “select star”, i’d say your new server has ONLY_FULL_GROUP_BY turned on (which it is by default in version 5.7 and up)

see https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

Thank you for your reply — i will read soon doc dev link mysql

how temporary only for this query, turn this OFF???

in table = cars_new4, field cat is NOT unique

in table = prices4, field cat is unique

that’s in the manual too

i strongly urge you not to use the dreaded evil “select star” when using GROUP BY

instead, write exactly what you want in the SELECT clause

that way you won’t have to turn it off :slight_smile:

$database->query('SELECT cat.t1, model.t1, img_small.t1, img_big.t1, discount.t1, aa.t1, l.t2, l7.t2, m.t2, m7.t2, h.t2, h7.t2 FROM cars_new4 t1 INNER JOIN prices4 t2 ON t1.cat=t2.cat group by t1.cat ;'); 

no result – same error 500 …is this ok ^^^? if no, can you tell correct query??

$database->query('SELECT t1.cat, model, img_small, img_big, discount, aa, l, l7, m, m7, h, h7 FROM cars_new4 t1 INNER JOIN prices4 t2 ON t1.cat=t2.cat group by t1.cat ;'); 

also No result same fact error

Apart from checking that joins are correct, is there any legitimate reason for using the dreaded evil “select star”

SELECT cat.t1
     , model.t1
     , img_small.t1
     , img_big.t1
     , discount.t1
     , aa.t1
     , l.t2
     , l7.t2
     , m.t2
     , m7.t2
     , h.t2
     , h7.t2 
  FROM cars_new4 t1 
INNER 
  JOIN prices4 t2 
    ON t2.cat = t1.cat 
group 
    by t1.cat

your SELECT clause is incorrect

the format is tablename.columnname

so what you want is

SELECT t1.cat
     , t1.model
     , t1.img_small
     , t1.img_big
     , t1.discount
     , t1.aa
     , t2.l
     , t2.l7
     , t2.m
     , t2.m7
     , t2.h
     , t2.h7 
  FROM cars_new4 t1 
INNER 
  JOIN prices4 t2 
    ON t2.cat = t1.cat 
group 
    by t1.cat

however, this does not solve your problem

tell me again, please, why do you want to use GROUP BY

yes, when used with an inline view also known as derived table

SELECT *
  FROM ( SELECT 1 * foo / bar  AS fap
              , 3 + qux / 2    AS zap
           FROM some_table ) AS subquery
 WHERE fap > 2 * zap

it’s obvious what columns the “select star” references, by simple inspection

I WANT TO USE
GROUP BY
because the admin to appear prices - the prices change per category[cat] and Not per car, In other words, cars in same cat have exactly same price/same discount… in this car rental Website you choose car but the cars in same cat [category eg A, B1, B2, C D1, D2…] cost exactly the same…
in prices table cat is primary unique key

not sure i understand your explanation

if all the cars in the same category have the same price, you should be able to join to the prices table without needing to group by category

try this

SELECT t1.cat
     , t1.model
     , t1.img_small
     , t1.img_big
     , t1.discount
     , t1.aa
     , t2.l
     , t2.l7
     , t2.m
     , t2.m7
     , t2.h
     , t2.h7 
  FROM cars_new4 t1 
INNER 
  JOIN ( SELECT cat
              , MIN(l)  AS l  
              , MIN(l7) AS l7 
              , MIN(m)  AS m  
              , MIN(m7) AS m7 
              , MIN(h)  AS h  
              , MIN(h7) AS h7 
           FROM prices4 
         GROUP
             BY cat ) t2 
    ON t2.cat = t1.cat

in cars_new4 table may exist 2 or 3 cars same category… i wanted appear only first car of the cat… Not the rest in the cat

this is for admin/owner of site to
change prices according to his prices
for the user quote is ok getting qoute without problems at:
http://www.paphoscarhire.com/

FINALLY THE REAL REQUIREMENT!!!

please explain how you would pick “the first car”

in the meanttime, while you’re figuring that out, you can try this –

SELECT t1.cat
     , t1.model     
     , t1.img_small 
     , t1.img_big   
     , t1.discount  
     , t1.aa        
     , t2.l
     , t2.l7
     , t2.m
     , t2.m7
     , t2.h
     , t2.h7 
  FROM ( SELECT cat
              , MIN(t1.model)      model        
              , MAX(t1.img_small)  img_small   
              , MIN(t1.img_big)    img_big     
              , MAX(t1.discount)   discount    
              , MIN(t1.aa)         aa          
           FROM cars_new4
         GROUP
             BY cat)   t1 
INNER 
  JOIN prices4 t2 
    ON t2.cat = t1.cat 

cars have aa field from 1 to 40. // is Not auto increment but according to cat alphabetical order
aa cat
1 A
2 B1. // first car
3 B1
4 B1
5 C
6 D1 // first car
7 D1

keep in mine old server MySQL working OK like my first post