Jump to content

display range within count() results


manhattes
Go to solution Solved by manhattes,

Recommended Posts

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

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • Solution

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` "; 
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

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?

Link to comment
Share on other sites

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.

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.