display range within count() results

[manhattes]

// 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.

[ginerjm]

How about SHOWING us the query that is giving you the problem instead of the pre-changed one?

[manhattes]

// 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.

[ginerjm]

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.

[Barand]

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

[manhattes]

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;"; 

[Barand]

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.

[ginerjm]

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?

[manhattes]

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?

[ginerjm]

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.

[manhattes]

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` "; 

[ginerjm]

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.

[manhattes]

I already fixed it it. Thank you for the advice..

[Barand]

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 |
+------------+

[ginerjm]

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?

[Barand]

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.

[ginerjm]

Then the MySQL functions from my dev.mysql.com are not accurately described for my feeble mind..