Issues trying: Multiple LIKES && ORDER BY multiple values

[justin7410]

hey guys,

 

i have a MySQL query that has multiple LIKE wildcard statement and multiple ORDER BY statements.

"SELECT * FROM `content` WHERE `release_date` LIKE '%2013-06%' OR `release_date` LIKE '%2013-05%' OR `release_date` LIKE '%2013-04%' AND `release_country` = 'USA' AND `is_active_member` = 1 ORDER BY `votes' DESC, `release_date` DESC LIMIT 0 , 50"

as you can see i have

 

• 3 wildcard's
• 5 total conditionals
• 2 ORDER BY fields

 

The ISSUE:

 

my wildcard statements work just fine and filters what i need, everything after ( release_country, is_active_member) dont seem to filter, also my ORDER BY only passes the first conditional. and release_date is not sorted.

 

i have done plenty of research to see if there are other formats to this( which there is ) but they also don't seem to work. The data and field names are correct, and when i place the other fields first, they come out correctly. but i cant get this exact query to work.

 

any suggestions as why this is happening ?

 

thanks guys

[DavidAM]

First: You don't need to use back-ticks for column names and table names unless the name is a mySql reserved word. I never use them as they clutter up the code and make it difficult to read (IMO).

 

This leads to one problem in your query: `votes'. In the ORDER BY clause you started with a back-tick to surround "votes" but closed it with a single-quote.

 

Second: When mixing AND and OR in a WHERE clause, It is best to use parenthesis to group the conditions. Your statement is actually evaluated as:

WHERE `release_date` LIKE '%2013-06%' 
OR `release_date` LIKE '%2013-05%' 
OR (`release_date` LIKE '%2013-04%' AND `release_country` = 'USA' AND `is_active_member` = 1 )

Which I doubt is your intention. Try:

 

WHERE (`release_date` LIKE '%2013-06%' 
OR `release_date` LIKE '%2013-05%' 
OR `release_date` LIKE '%2013-04%')

AND `release_country` = 'USA' 
AND `is_active_member` = 1 

Finally: If release_date is a DATE or DATETIME (or even TIMESTAMP) column, you do not need the percent symbol at the beginning of those LIKE expressions. The format of a DATE column in mySQL is: YYYY-MM-DD.

[justin7410]

First: You don't need to use back-ticks for column names and table names unless the name is a mySql reserved word. I never use them as they clutter up the code and make it difficult to read (IMO).

 

This leads to one problem in your query: `votes'. In the ORDER BY clause you started with a back-tick to surround "votes" but closed it with a single-quote.

 

Second: When mixing AND and OR in a WHERE clause, It is best to use parenthesis to group the conditions. Your statement is actually evaluated as:

WHERE `release_date` LIKE '%2013-06%' 
OR `release_date` LIKE '%2013-05%' 
OR (`release_date` LIKE '%2013-04%' AND `release_country` = 'USA' AND `is_active_member` = 1 )

Which I doubt is your intention. Try:

 

WHERE (`release_date` LIKE '%2013-06%' 
OR `release_date` LIKE '%2013-05%' 
OR `release_date` LIKE '%2013-04%')

AND `release_country` = 'USA' 
AND `is_active_member` = 1 

Finally: If release_date is a DATE or DATETIME (or even TIMESTAMP) column, you do not need the percent symbol at the beginning of those LIKE expressions. The format of a DATE column in mySQL is: YYYY-MM-DD.

 

Thanks man,

 

great answer just what i was thinking but thanks for putting it in writing.

 

also i tried your no %% for the wildcard for DATE field. which i am pretty sure it is with the format of yyyy-mm-dd but that gave me a failed query, with the %% it worked just fine.

 

other than that, thanks for the great answer sir

[Barand]

 

also i tried your no %% for the wildcard for DATE field. which i am pretty sure it is with the format of yyyy-mm-dd but that gave me a failed query, with the %% it worked just fine.

 

What he actually said was you don't need the % at the beginning of those LIKE expressions

 

ie LIKE '2013-05%'