CODEWITHSUNDEEP
phpmysqli
Ojibix Creatives
Ojibix Creatives

Reputation: 117

mysql query when condition is date

i am doing query on mysql where condition is all the rows which has journeydate after 4 days from today. but my script is not working, in my datatable date format is DD/MM/YYYY and Column type is "varchar".

when i execute these query, i get rows with all dates, condition is not working.

here is the sql script

    <?php


include('../../connect.php');

$date = strtotime("+4 day", time());
$finaldate = date('d/m/Y', $date);
echo $finaldate;


//$sql = "SELECT * FROM tblapps WHERE pay_status <> 'paid' AND appstatus <> 'Not Interested' AND process = 'yes' AND journeydate >= '$finaldate'";
$sql = "SELECT * FROM tblapps WHERE journeydate >= '$finaldate'";
if($result = mysqli_query($connect, $sql)){
    if(mysqli_num_rows($result) > 0){
        echo "<table>";
            echo "<tr>";
                echo "<th>id</th>";
                echo "<th>Pay Status</th>";
                echo "<th>Journey date</th>";
                echo "<th>email</th>";
            echo "</tr>";
        while($row = mysqli_fetch_array($result)){
            echo "<tr>";
                echo "<td>" . $row['id'] . "</td>";
                echo "<td>" . $row['pay_status'] . "</td>";
                echo "<td>" . $row['journeydate'] . "</td>";
                echo "<td>" . $row['email'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
        // Free result set
        $rowcount=mysqli_num_rows($result);
        echo $rowcount;

        mysqli_free_result($result);        


    } else{
        echo "No records matching your query were found.";
    }
} else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}

// Close connection
mysqli_close($link);
?>

Upvotes: 0

Views: 586

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

Don't store your dates as text, store them as dates. For a temporary workaround, you may use STR_TO_DATE:

SELECT *
FROM tblapps
WHERE STR_TO_DATE(journeydate, '%d/%m/%Y') >= '2018-06-01';

Upvotes: 1

Related Questions