How to select from database according to current time

Question

I am making a school portal system and right now I am making the page for students to view homework. I want their homework to be highlighted in red, or not shown at all if the current date is past the due date. How can I do this? My code for the page

<?php
//including the database connection file
include_once("connection.php");
    $id= $_GET['id'];


//fetching data in descending order (lastest entry first)
$result = mysqli_query($conn, "SELECT * FROM homework where class_id= '$id'"); 

?>

<html>
<head>    
    <title>View IS</title>
</head>

<body>

    <table width='80%' border=0>
        <tr bgcolor='#CCCCCC'>
            <td>Task</td>
            <td>Date Set </td>
            <td>Date Due </td>
        </tr>
        <?php 
        //while($res = mysql_fetch_array($result)) 
        while($res = mysqli_fetch_array($result)) {         
            echo "<tr>";
            echo "<td>".$res['description']."</td>";
            echo "<td>".$res['dateset']."</td>";
            echo "<td>".$res['datedue']."</td>";

        }
        ?>
    </table>
</body>

Database:

fahad patel · Answer 1 · 2020-01-10T08:38:25.770

You should use parameterized prepared statements instead of manually building your queries.

I have used the date function to compare the dates if the date is greater then I've put some style while in else there is no style. I have gave you the idea now you can modify accordingly.

    <table width='80%' border=0>
            <tr bgcolor='#CCCCCC'>
                <td>Task</td>
                <td>Date Set </td>
                <td>Date Due </td>
            </tr>
            <?php 
            //while($res = mysql_fetch_array($result)) 
    $current_date=date("Y-m-d");
            while($res = mysqli_fetch_array($result)) { 


            if($current_date > $res['datedue'] ){

?>
<tr style='color:red;'>;
<?php

    }else {
    <tr>
    }

                echo "<td>".$res['description']."</td>";
                echo "<td>".$res['dateset']."</td>";
                echo "<td>".$res['datedue']."</td>";
    </tr>
            }
            ?>
        </table>

Reference

You can't compare dates using strings. Need to convert them to timestamps or use DateTime object. — Triby, Jan 07 '20 at 16:13

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

Instead of a query, use a prepared statement with bind_param which is much safer. Then just compare the dates to check if $res['datedue'] passed or not. This should be it:

<?php
    include_once("connection.php"); //including the database connection file
    date_default_timezone_set('America/Los_Angeles');    //set the default time zone to your time zone (this is just an example)
    $result = $conn->prepare("SELECT * FROM homework WHERE class_id=?");
    $result->bind_param("i", (int)$_GET['id']);
    $result->execute();
    $result2 = $result->get_result();
?>
<html>
<head>    
    <title>View IS</title>
</head>
<body>
    <table width='80%' border=0>
        <tr bgcolor='#CCCCCC'>
            <td>Task</td>
            <td>Date Set </td>
            <td>Date Due </td>
        </tr>
        <?php
            while($res = $result2->fetch_array(MYSQLI_ASSOC)) {   
                if (date("Y-m-d") > $res['datedue']) {
                    echo "<tr style=\"color: red;\">";
                        echo "<td>".$res['description']."</td>";
                        echo "<td>".$res['dateset']."</td>";
                        echo "<td>".$res['datedue']."</td>";
                    echo "</tr>";
                } else {
                    echo "<tr>";
                        echo "<td>".$res['description']."</td>";
                        echo "<td>".$res['dateset']."</td>";
                        echo "<td>".$res['datedue']."</td>";
                    echo "</tr>";
                }
            }
        ?>
    </table>
</body>

You could also use $time = new DateTime; and then $time->format("Y-m-d") instead of date("Y-m-d").

More about time zones.

I usually do it with `strtotime` but looks like it is unnecessary here, thanks for the tip. — Leon Kunštek, Jan 07 '20 at 18:15
@Dharman Thanks again, I've updated my answer. I don't deal with date comparisons that often. — Leon Kunštek, Jan 07 '20 at 18:18
Hi, thank you very much, could you pleas explain this line: $result->bind_param("i", (int)$_GET['id']); — Omar, Jan 07 '20 at 20:14
`$result->bind_param("i", (int)$_GET['id']);` - Here we insert the data into the prepared statement (`$result`). `"i"` is the type of data we're inserting, in this case an `integer` (a number), and here `(int)$_GET['id']` were casting `$_GET['id']` into an `integer` (a number). Casting is converting a data type into another data type. You can learn more about casting here: https://www.php.net/manual/en/internals2.opcodes.cast.php. — Leon Kunštek, Jan 07 '20 at 20:32

score 1 · Answer 3 · edited Jun 20 '20 at 09:12

Don`t use concated raw SQL.

Use variable binding to prevent SQL injections.

<?php
$stmt = $mysqli->prepare("SELECT * FROM homework where class_id= ?");
$stmt->bind_param('i', (int)$_GET['id']); // bind vairable and cast it to integer (it is a good practice when you get data from outside)
$stmt->execute();
$result = $stmt->get_result();
while($res = $result->fetch()){
  // Your code here
  var_dump($res);
}

I will suggest using PDO instead of mysqli. You can read more about SQL Injections here: How can I prevent SQL injection in PHP?

Don't use `select *` (wildcard)

SELECT description, dateset, datedue, datedue < NOW() AS is_expired FROM homework where class_id= ?

Check the value of is_expired to see which results you will mark in red

I haven't tried the code but I guess it will work as expected

This is a comment not an answer. – Dharman Jan 07 '20 at 17:39 — Dharman, Jan 07 '20 at 17:39
You are mixing APIs. MySQLi and PDO are different. – Dharman Jan 07 '20 at 18:06 — Dharman, Jan 07 '20 at 18:06

How to select from database according to current time

3 Answers3

Don`t use concated raw SQL.

Don't use select * (wildcard)

Don't use `select *` (wildcard)