Need help with WHERE part of a mySQL query

[HuntsvilleMan]

I'm trying to figure out how to write a query and something in the WHERE  part is my problem.  Without the WHERE part the code runs ok.

 

The field "confirm_time"  is text values that look like "2001-04-09 12:14:54"

 

Since I only want values back that match all days like "2001-04-09" I tried the substring function

 

Really appreciate suggestion. This has stumpted me all day.

 

Most likely I need to find atutorial on the correct way to use PHP variables in mySQL queries.  All suggestions appreciated?

 

Thanks

 

Mike

 

  ...

  $thisdate = "2011-05-29";

  $query = "SELECT * FROM " . $myTABLE;

  $query .= " WHERE substring(confirm_time,1,10) = " . $thisdate;

  $result = mysql_query($query);

  ...

 

[PFMaBiSmAd]

Literal date values (your $thisdate value) inside a query need to be enclosed in single-quotes. Your query should end up looking like -

 

SELECT * FROM your_table WHERE substring(confirm_time,1,10) = '2011-05-29'

 

Your substring() code will work, but using the mysql DATE() function is probably the fastest method -

 

 

SELECT * FROM your_table WHERE DATE(confirm_time) = '2011-05-29'

 

If the $thisdate value you picked to show is intended to be the current date -

 

SELECT * FROM your_table WHERE DATE(confirm_time) = CURDATE()

[HuntsvilleMan]

Wow! thanks for getting me back on the right track.  My minor variation on your great suggestion was:

 

  $thisdate = "2011-05-29";

  $query = "SELECT * FROM " . $myTABLE;

  $query .= " WHERE substring(confirm_time,1,10) = " . "'" . $thisdate . "'";

 

As it happened, time was stored as char(19) data so I had to do a character comparison.

 

No way to express how much I appreciate your helpful suggestion.  This mystery bogged me down all day. 

 

Is a utility that helps cook up these mySQL/PHP expressions once you have the mySQL query worked out.

 

Thanks

 

Mike