php variable not setting in mysql query

[kwkened]

Hello,

 

I'm having difficulty getting my mysql queries to work when I use variables in the query.  Here's the code

$query = "SELECT * FROM `table` WHERE (`column` BETWEEN $startdate AND $enddate) ";

with literal dates in place of the $startdate and $enddate variables the query works and I get the correct records displayed to the browser, but with the variables there I get nothing.

 

When I var_dump() the variables I get this: 

object(DateTime)[3]
  public 'date' => string '2014-03-12 00:00:00' (length=19)
  public 'timezone_type' => int 3
  public 'timezone' => string 'UTC' (length=3)

object(DateTime)[4]
  public 'date' => string '2014-03-19 00:00:00' (length=19)
  public 'timezone_type' => int 3
  public 'timezone' => string 'UTC' (length=3)

so I see the variables are being set as DateTime correctly (also, the table column in my database is set to DateTime as well so I don't see why its not working).  But when I echo the query to the browser, the variables are being stripped from the query or just not being included in the query for some reason.  Here's the output:  

SELECT * FROM `Table` WHERE (`Column` BETWEEN '' AND '') 

You can see where the variable has been removed and just single quotes remain.  Even if I don't include the single quotes the variable is removed.  Also, I tried a different query referencing an int type column and I plugged an int variable into the query and the variable was still removed, but if I plugged in an actual integer into the query I got the correct result to the browser.  So for some reason my queries don't allow php variables. 

Any ideas what the problem could be or how I might troubleshoot this problem?  Thanks in advance.

[mac_gyver]

we cannot really help you with your code, without seeing your code, but you have implied $startdate and $enddate are instance of datetime objects. you would need to reference the correct property, in the query, or reference a method that returns just the date string or have a magic method that returns the date string when you use the object in a string context.

[kwkened]

Wow, I've been following along in a php mysql lesson book I got at barnes and it doesn't cover that in the example code lol.  Seriously though I'm a noob trying to learn by application trying to start simple and I thought plugging a php variable into a mysql query would be like phpmysql101, but maybe more like 201?

 

Here's the code where I setup the variables: 

$startdatestr = $_POST["StartDate"];
$enddatestr = $_POST["EndDate"];

$startdate = date_create("$startdatestr");
var_dump($stardate);

$enddate = date_create("$enddatestr");
var_dump($enddate);

At first I was trying the string variables but it didn't work.  Only worked if I plugged the actual dates in.  And again the column in the database is of DateTime type.

And here's the query that's not working:  

$query = "SELECT * FROM `Table` WHERE '$startdate' <= ` Column` AND `Column` <= '$enddate'";

And again this query works with the actual dates plugged in but not the variables.  How can I get the user date selection into the query to pull the pertinent records if I can't put the variable in there?  Thanks again.

[mac_gyver]

the $query = ... code should be producing a fatal error (unless your code is handling the exception itself) - Catchable fatal error: Object of class DateTime could not be converted to string ... and i'm pretty sure this behavior hasn't changed across the php versions that support the datetime class, so either the query isn't being produced at all or the snippets of code being posted isn't the whole story. (i did discover that using var_dump() on a datetime object modifies the object itself, but doesn't change the error that is produced.)

 

in any case, as already stated, you cannot just use an object as a value without referencing the correct property or method of that object. the datetime object has a format() method that is the correct way of getting a string representation of the datetime value.

 

ref: http://www.php.net/manual/en/datetime.format.php