This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I think that dates are not stored as strings but in there own special format. Try...
And see what you get.
SELECT SUBSTRING(last_time,1,10)
And see what you get.
Try BETWEEN like this:
MYSQL: BETWEEN CONDITION
SELECT *
FROM cvisitor
WHERE
last_time BETWEEN '2016-01-27' AND '2016-01-28' AND email NOT LIKE '%@lakos.com'
ORDER BY last_time
MYSQL: BETWEEN CONDITION
If last_time is a datetime field then
To extract the date portion of a date time string you can use the DATE function https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date
SELECT *
FROM cvisitor
WHERE
DATE(last_time) BETWEEN '2016-01-27' AND '2016-01-28'
AND email NOT LIKE '%@lakos.com'
ORDER BY DATE(last_time)
To extract the date portion of a date time string you can use the DATE function https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date
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'
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial