What is wrong with this SQL Statement?

[adamjblakey]

Hi,

Please can someone help me with this. It is not returning any results but it should.

[PHP]SELECT * FROM listings WHERE (category = 'Weddings' or category2 = 'Weddings' or category3 = 'Weddings') AND (subcategory = 'DJs Discos and Karaoke' or subcategory2 = 'DJs Discos and Karaoke' or subcategory3 = 'DJs Discos and Karaoke') AND status = '1' ORDER BY ('premium', 'sdate')[/PHP]

Cheers,
Adam

[ronverdonk]

This is a MySQL question, so I'll move the thread to that forum. - moderator

When 'premium' and 'sdate' are table columns, you don't specify any quotes around column names (back-ticks if you want to), so should sort by

ORDER BY premium, sdate

Ronald

[nathj]

Hi,

Please can someone help me with this. It is not returning any results but it should.

[PHP]SELECT * FROM listings WHERE (category = 'Weddings' or category2 = 'Weddings' or category3 = 'Weddings') AND (subcategory = 'DJs Discos and Karaoke' or subcategory2 = 'DJs Discos and Karaoke' or subcategory3 = 'DJs Discos and Karaoke') AND status = '1' ORDER BY ('premium', 'sdate')[/PHP]

Cheers,
Adam

Hi,

It's difficult to tell without seeing the data involved. Could it be a case sensitivity issue?

Try converting the fields to lowercase:

SELECT * FROM listings WHERE 'weddings' IN(lower(category), lower(category2), lower(category3)) AND 'djs discos and karaoke' IN (lower(subcategory), lower(subcategory2), lower(subcategory3)) AND status = '1' ORDER BY ('premium', 'sdate')

I have also modified the code sligtly but that shouldn't have anmy impact on it's own.

Cheers
nathj

[ronverdonk]

No, it is definitely the ORDER BY.

Ronald

[nathj]

No, it is definitely the ORDER BY.

Ronald

Hi,

Show my own lack of understanding here but how would an 'ORDER BY' affect the output in terms of what is retrieved? I thought it simply ordered the results byt the fields specified.

nathj

[adamjblakey]

It was the ORDER BY that was causing the problem. It should have been e.g. ORDER BY premium ASC, sdate ASC

Cheers,
Adam

[ronverdonk]

Hi,

Show my own lack of understanding here but how would an 'ORDER BY' affect the output in terms of what is retrieved? I thought it simply ordered the results byt the fields specified.

nathj

nathj: Not when it is incorrectly specified. See Adam's answer: it was the ORDER BY.

Adam: glad this is solved. See you next time around.

ROnald