MySQL Query
I have this query on a MySQL table
SELECT * from cvisitor where SUBSTRING(last_time,1,10) >= '2016-01-27' and SUBSTRING(last_time,1,10) <= '2016-01-28' and email not like '%@lakos.com' order by last_time
There are a number of rows where the email IS like @lakos.com. They are not excluded.
If I do the following, it works.
SELECT * from cvisitor where email not like '%@lakos.com'
Why does not the first work? Do I need parens around date comparisons?
Thanks
SELECT * from cvisitor where SUBSTRING(last_time,1,10) >= '2016-01-27' and SUBSTRING(last_time,1,10) <= '2016-01-28' and email not like '%@lakos.com' order by last_time
There are a number of rows where the email IS like @lakos.com. They are not excluded.
If I do the following, it works.
SELECT * from cvisitor where email not like '%@lakos.com'
Why does not the first work? Do I need parens around date comparisons?
Thanks
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
To Dave Baldwin,
I have been using the dates as strings for years successfully in MySQL. The query is dynamically built in php based on values on an input form. The date part works perfectly. The issue is the like '%@lakos.com'
I have been using the dates as strings for years successfully in MySQL. The query is dynamically built in php based on values on an input form. The date part works perfectly. The issue is the like '%@lakos.com'
ASKER
To all,
Subsequent to this post I discovered that some of the email addresses have non-printable characters (I think just one) at the end. I discovered that by using php strlen and getting one more that the actual # of printable chars in some of the emails.
That would seem to explain why the like '%@lakos.com' fails because it should be like '%@lakos.com%' .
It does NOT explain why SELECT * from cvisitor where email not like '%@lakos.com' works
I haven't tried that yet, I think a simpler solution is when I loop through the result set, just use php to eliminate any that contain the string '@lakos.com' using php strpos.
Richard
Subsequent to this post I discovered that some of the email addresses have non-printable characters (I think just one) at the end. I discovered that by using php strlen and getting one more that the actual # of printable chars in some of the emails.
That would seem to explain why the like '%@lakos.com' fails because it should be like '%@lakos.com%' .
It does NOT explain why SELECT * from cvisitor where email not like '%@lakos.com' works
I haven't tried that yet, I think a simpler solution is when I loop through the result set, just use php to eliminate any that contain the string '@lakos.com' using php strpos.
Richard
While MySQL returns the results as strings, it does not store the dates that way. Details matter. To the point of this question, have you been using this function successfully before?
SUBSTRING(last_time,1,10) >= '2016-01-27'
SUBSTRING(last_time,1,10) >= '2016-01-27'
ASKER
Dave,
No, but as I mentioned it works perfectly if I use ONLY the date selectivity part.
I recognize that details matter. Everything I do is about details.
No, but as I mentioned it works perfectly if I use ONLY the date selectivity part.
I recognize that details matter. Everything I do is about details.
MySQL Server
MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.
49K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Open in new window
And see what you get.