CODEWITHSUNDEEP
phpmysql
sarah
sarah

Reputation: 7

MySQL INNER JOIN with date as condition

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);
}

Upvotes: 0

Views: 652

Answers (1)

Joel Cox
Joel Cox

Reputation: 3469

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.

Upvotes: 1

Related Questions