根据数据库中的日期选择数据

Question

My below PHP is fetching the data from database for present data if exist in the the Sql table using the Time Zone Date and the id to select date for present date.

But;

I want it to select present week data in the sense if today is Sunday it should fetch the data from Monday to today if data exist in the table against that id

My database table is as follows

        Date                Id                Data to be fetched

2014-08-29 00:00:00       12345                       ABC
2014-08-29 00:00:00       12345                       ABC
2014-08-30 00:00:00       12345                       ABC
2014-08-31 00:00:00       12345                       ABC
2014-08-31 00:00:00       12345                       ABC
2014-08-02 00:00:00       12345                       ABC
2014-08-02 00:00:00       12345                       ABC
2014-08-02 00:00:00       12345                       ABC
2014-08-03 00:00:00       12345                       ABC
2014-08-03 00:00:00       12345                       ABC
2014-08-04 00:00:00       12345                       ABC
2014-08-04 00:00:00       12345                       ABC

PHP:code

<?php

  // This is used to select Time Zone
date_default_timezone_set("Indian/Chagos");
        $date= date("y-m-d 00:00:00");

        //Databse Connection
                $mysqli=mysqli_connect('localhost','root','password','db');
    //Data From Ajax For Id to select data against that id

            $id= $_POST['storedValue'];
                //Sql Query
                                                                //Select data against date and PK Id
        $query ="SELECT * FROM master WHERE  StudentRegID='$id' And AttendanceDate='$date' ";
$result = mysqli_query($mysqli,$query)or die(mysqli_error());
$num_row = mysqli_num_rows($result);
            while($row=mysqli_fetch_array($result))
    {
            //Echo result
      echo "{ y: ".$row[9]." ,label: '".$row[6]."'},";  
    }
    mysqli_close($mysqli);

?>

It is not working:

Can you identify where I am going wrong?

    $id= $_POST['storedValue'];
        $query ="SELECT * FROM master WHERE  StudentRegID='$id' AND DATEDIFF(NOW(), Date) < 7 ";
$result = mysqli_query($mysqli,$query)or die(mysqli_error());
$num_row = mysqli_num_rows($result);
            while($row=mysqli_fetch_array($result))
    {



      echo "{ y: ".$row[9]." ,label: '".$row[6]."'},";




    }

Answer 1

You can use DATEDIFF between the column Date and your current date and retrieve only the lines where the difference is less than 7 like:

SELECT * [...]
WHERE  StudentRegID='$id' AND DATEDIFF(NOW(), Date) < 7
-- if you want only the lines where something happened today :
AND DATE(AttendanceDate) = DATE('$date') 
;