MySQL INNER JOIN以日期为条件

I'm trying to use inner join to get the data from two tables using date as one of the condition.
But since the second table contain dateTime column type, I want to use only the date to check for condition.
When i run the code using Postman, it says I have error in SQL syntax at line (DATE)checkInDateTime = $rideDate .
I also have test the SQL without taking the date into condition and the SQL works .
Is there any ways to use the date as condition in InnerJoin method? Please help me .
Thanks.
P/s : my dateTime column store values such as 2015-01-08 11:18:02

//get all rides from table rides
$result = mysql_query("SELECT first.ID, first.fullname, 
second.checkInDateTime FROM first INNER JOIN second ON first.ID = 
second.riderID WHERE second.ridesID = $rideID AND second.
CAST(checkInDateTime AS DATE) = $rideDate") or die(mysql_error());


//check for empty result
if(mysql_num_rows($result) > 0) {
    //loop all result and put into array riders
    $response["riders"] = array();

    while ($row = mysql_fetch_array($result)) {
        //temp array
        $rider = array();
        $rider["riderID"] = $row["ID"];
        $rider["riderName"] = $row["fullname"];
        $rider["timeCheckedIn"] = $row["checkInDateTime"];

        //push single ride into final response array
        array_push($response["riders"], $rider);
    }
    //success
    $response["success"] = 1;

    //print JSON response
    echo json_encode($response);
} else {
    //no rides found
    $response["success"] = 0;
    $response["message"] = "No riders found";

    //print JSON response
    echo json_encode($response);
}
查看全部
dougouqin0763
dougouqin0763
2015/01/08 04:08
  • php
  • mysql
  • 点赞
  • 收藏
  • 回答
    私信
满意答案
查看全部

1个回复