manhattes Posted October 5, 2015 Share Posted October 5, 2015 // Setup Variables $query = "SELECT Symbol, `Completion Date`, COUNT(Symbol) FROM CleanedCalendar GROUP BY Symbol,`Completion Date`"; $result2 = mysql_query($query) or die(mysql_error()); // Print out result while($row2 = mysql_fetch_array($result2)){ echo "There are ". $row2['COUNT(Symbol)'] ." ". $row2['Symbol'] ." items." . " ". $row2['Completion Date']; echo "<br />"; } The following code sorts my entire table. How would I make it only display the last 90 days? I tried adding WHERE STR_TO_DATE(`Completion Date`, '%W, %M %e, %Y') BETWEEN CURDATE() AND CURDATE() + INTERVAL (90) DAY;"; but it said I had the wrong syntax by where. Quote Link to comment Share on other sites More sharing options...
ginerjm Posted October 5, 2015 Share Posted October 5, 2015 How about SHOWING us the query that is giving you the problem instead of the pre-changed one? Quote Link to comment Share on other sites More sharing options...
manhattes Posted October 5, 2015 Author Share Posted October 5, 2015 // Setup Variables $query = "SELECT Symbol, `Completion Date`, COUNT(Symbol) FROM CleanedCalendar GROUP BY Symbol,`Completion Date` WHERE STR_TO_DATE(`Completion Date`, '%W, %M %e, %Y') BETWEEN CURDATE() AND CURDATE() + INTERVAL (90) DAY"; $result2 = mysql_query($query) or die(mysql_error()); // Print out result while($row2 = mysql_fetch_array($result2)){ echo "There are ". $row2['COUNT(Symbol)'] ." ". $row2['Symbol'] ." items." . " ". $row2['Completion Date']; echo "<br />"; } there you go. it returns the same result. Quote Link to comment Share on other sites More sharing options...
ginerjm Posted October 5, 2015 Share Posted October 5, 2015 Is Completion Date a string or a date field? If it is NOT a string, why are you using str_to_date on it? If it is a string, why isn't it a true datetime field in your database? Would make it much easier to use instead of the format that your format-string implies. Also - your str_to_date call produces a datetime format value. The curdate function returns a string value. Kinda hard to compare the two. Quote Link to comment Share on other sites More sharing options...
Barand Posted October 5, 2015 Share Posted October 5, 2015 (edited) For last 90 days WHERE ... BETWEEN CURDATE() - INTERVAL 90 DAY AND CURDATE() And GROUP BY should be at the end of the query Edited October 5, 2015 by Barand Quote Link to comment Share on other sites More sharing options...
manhattes Posted October 5, 2015 Author Share Posted October 5, 2015 the `Completion Date` is a field and was imported in a format that SQL doesn't recognize. The string to date allows me to compare it to the current date as I can do with this statement: $query2 = "SELECT * FROM CleanedCalendar WHERE STR_TO_DATE(`Completion Date`, '%W, %M %e, %Y') BETWEEN CURDATE() AND CURDATE() - INTERVAL (90) DAY;"; Quote Link to comment Share on other sites More sharing options...
Barand Posted October 5, 2015 Share Posted October 5, 2015 No. Have another look at my BETWEEN clause. The earlier date must come first. It would be be more efficient to reformat the date once on import instead of in every query. Quote Link to comment Share on other sites More sharing options...
ginerjm Posted October 5, 2015 Share Posted October 5, 2015 Read the function name again. "string to date". It produces a datetime, not a date string like yyyy-mm-dd. Basically it is not a human-readable value. You say that the input for the completion date field was not in a recognizable format, yet you are trying to use it with a MySQL function. Couldn't you have done that when you imported it and avoided this complexity? Quote Link to comment Share on other sites More sharing options...
manhattes Posted October 5, 2015 Author Share Posted October 5, 2015 yeah I guess it is possible. I am no expert. I am still learning. I am sure my code could be alot cleaner. Do you have any idea as to how to select the date range assuming using the count() assuming the formatting is correct? Quote Link to comment Share on other sites More sharing options...
ginerjm Posted October 5, 2015 Share Posted October 5, 2015 Barand gave you the syntax for the comparison. I'm trying to tell you that str_to_date will not work Those are the two pieces of your puzzle that you need to work on. Either write a query to convert your table completely or use the correct function in place of str_to_date to get a datetime result for your compare. Since you apparently don't like to do your own research ( a bad tendency for a future programmer!), here's a link that you should find useful: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html. Quote Link to comment Share on other sites More sharing options...
Solution manhattes Posted October 5, 2015 Author Solution Share Posted October 5, 2015 Ok thank you. Sorry I am still missing something. It is saying I have the wrong syntax: $query = "SELECT *, COUNT(Symbol) FROM CleanedCalendar , WHERE `Completion Date` BETWEEN CURDATE() - INTERVAL 90 DAY AND CURDATE() GROUP BY Symbol, `Completion Date` "; Quote Link to comment Share on other sites More sharing options...
ginerjm Posted October 6, 2015 Share Posted October 6, 2015 Did you display the sql error message? It might have pointed you at the problem if you only bothered to do these kinds of things as a beginner. Always code to check things instead of just assuming that they are going to run for you. Check if a query ran and if not output the message. Check if your db connection succeeded and send an echo if it doesn't. Check if the external file you attempted to open actually was opened. Enable php error checking always when doing development so that you can see and clean up errors as they occur. Programming is about putting together a fail-safe solution to a problem. Kind of like baby-sitting a two year old - you can't assume anything! Hint: check for unneeded commas. Quote Link to comment Share on other sites More sharing options...
manhattes Posted October 6, 2015 Author Share Posted October 6, 2015 I already fixed it it. Thank you for the advice.. Quote Link to comment Share on other sites More sharing options...
Barand Posted October 6, 2015 Share Posted October 6, 2015 Read the function name again. "string to date". It produces a datetime, not a date string like yyyy-mm-dd. @ginerjm, If you were to RTFM I think you would find it (str_to_date) does create a date in yyyy-mm-dd format mysql> SELECT STR_TO_DATE('Tuesday, October 6 2015', '%W, %M %e %Y') as date; +------------+ | date | +------------+ | 2015-10-06 | +------------+ Quote Link to comment Share on other sites More sharing options...
ginerjm Posted October 6, 2015 Share Posted October 6, 2015 I did RTFM and am pointing out that it says it creates a datetime field, or a DATE or TIME field and not a string STR_TO_DATE(str,format) This is the inverse of the DATE_FORMAT() function. It takes a string str and a format string format. STR_TO_DATE() returns a DATETIME value if the format string contains both date and time parts, or a DATE or TIME value if the string contains only date or time parts. So his original query was trying to compare the string (type) value that curdate provides against a date(type) value from str_to_date. Have I read RTFM incorrectly? Quote Link to comment Share on other sites More sharing options...
Barand Posted October 6, 2015 Share Posted October 6, 2015 His original query (from opening post) was ... WHERE STR_TO_DATE(`Completion Date`, '%W, %M %e, %Y') BETWEEN CURDATE() AND CURDATE() + INTERVAL (90) DAY which does convert the date string, but contains an error in the BETWEEN clause (ie next 90 days instead of last 90 days).And the output from STR_TO_DATE() mysql> SELECT STR_TO_DATE('Tuesday, October 6 2015', '%W, %M %e %Y') as date; +------------+ | date | +------------+ | 2015-10-06 | +------------+ is definitely yyyy-mm-dd format and is human-readable, contrary to your statement. Quote Link to comment Share on other sites More sharing options...
ginerjm Posted October 6, 2015 Share Posted October 6, 2015 Then the MySQL functions from my dev.mysql.com are not accurately described for my feeble mind.. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.