Jump to content

Issues trying: Multiple LIKES && ORDER BY multiple values


justin7410
Go to solution Solved by DavidAM,

Recommended Posts

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

Link to comment
Share on other sites

  • Solution

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.
Link to comment
Share on other sites

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

Link to comment
Share on other sites

 

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%'

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.