dougouqin0763
dougouqin0763
2015-01-08 04:08

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);
}
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • doupu1957 doupu1957 6年前

    I'm guessing the (DATE)checkInDateTime is an attempt at typecasting, and you're getting the sql error because that's not the right syntax... try CAST(checkInDateTime AS DATE) instead.

    点赞 评论 复制链接分享

相关推荐