droc60607 2015-06-07 09:23
浏览 81

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

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]."'},";




    }
  • 写回答

1条回答 默认 最新

  • dsnpjz6907 2015-06-07 09:40
    关注

    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') 
    ;
    
    评论

报告相同问题?

悬赏问题

  • ¥20 用vivado写数字逻辑实验报告撰写,FPGA实验
  • ¥15 为什么shp文件会有这种小方块?
  • ¥15 ecplise在连接数据库时显示加载驱动成功但是数据库连接失败
  • ¥15 visionmaster启动失败,提示为“机器不满足授权而被禁用”
  • ¥15 IDEA中圈复杂度如何具体设置
  • ¥50 labview采集不了数据
  • ¥15 Multisim红外倒车雷达仿真中距离问题
  • ¥15 请上面代码做什么处理或什么混淆
  • ¥15 英雄联盟自定义房间置顶
  • ¥15 W5500网线插上无反应