why am i getting 2 different results with these mysql queries?

unwashed potato! · 27 Jul 2021 at 02:56

perhaps a better question would be, why does one work and one not.

these are the 2 queries i have. the top is the one that returns nothing, supposed to be counting how many there are. currently there's 3 that this should bring back.

Code:

SELECT COUNT(id) FROM quotes WHERE hide !=1 && booked > '0' && date='29/07/2021'

Code:

SELECT * FROM quotes WHERE hide != 1 && date='29/07/2021' && booked > '0' ORDER BY id DESC LIMIT 50 OFFSET 0

the second returns the 3 rows correctly. what am i missing?

if i do this query without the date it works fine for both. if i replace date with say && service = 'cleaning'

this also works fine for both.

if i remove the && booked > '0' part, this also means it all works fine.

peterwalkley · 27 Jul 2021 at 13:40

Shouldn't the first just be 'select count(*) ....' instead of 'select count(id) ...' ?

NOT a SQL expert, but I've always done it that way.

chroniclard · 27 Jul 2021 at 13:45

count(id) will exclude any rows with null value in the "id" wont it? (not that you should have nulls in an id)

count(*) is most often used by me in SQL server

swillsy · 27 Jul 2021 at 19:44

chroniclard said:
count(id) will exclude any rows with null value in the "id" wont it? (not that you should have nulls in an id)

count(*) is most often used by me in SQL server

This by the looks of it as it looks fine - what are the values in the id fieldsfor these records?