获取日期差异 [英] Get Date Difference

问题描述

我想在查询中得到两个日期之间的差额.我尝试使用多个select语句，但是它不起作用

I want to get the difference between two date in my query. I tried using multiple select statement but it's not working

这是我的代码

$tbl_name = "myTable";
$setDay = "10";

$stmt = $pdo->query("SELECT * (
              SELECT due_date,
                     date_paid,
                     DATEDIFF(due_date, date_paid) as date_interval) 
              FROM $tbl_name 
              WHERE DATEDIFF(due_date, date_paid) <= $setDay 
              ORDER BY trans_id DESC LIMIT $start, $limit");
$stmt->execute();

谢谢

推荐答案

重要的是开发您的MySQL查询并首先在PHP代码的上下文之外完善它们，然后在使查询按所需方式工作后将其集成在MySQL客户端应用程序中的 ，例如MySQL Workbench，PHPMyAdmin等.

It is important to develop you MySQL queries and perfect them outside the context of PHP code first, then integrate the query once you have it working the way you need it to in a MySQL client application like MySQL Workbench, PHPMyAdmin, etc.

在您的查询中，不需要外部SELECT，内部查询本身看起来几乎是正确的，但这是您尝试使用PDO执行该错误的方式.

In your query, the outer SELECT is not needed, and the inner query itself looks almost correct, but it is the way you attempt to execute it with PDO that is faulty.

SELECT
  due_date,
  date_paid,
  DATEDIFF(due_date, date_paid) as date_interval
FROM $tbl_name
WHERE
  DATEDIFF(due_date, date_paid) <= $setDay
ORDER BY trans_id DESC
LIMIT $start, $limit

现在要在PDO中执行它，您应该使用 prepare()/bindParam()/execute() 创建一个准备好的语句，绑定参数，并使用这些参数执行它(但是您不能绑定表名-必须保留一个变量).在当前代码中，您将用于简单静态查询的PDO::query()方法和用于执行已准备好的语句的PDOStatement::execute()方法混合在一起.您应该使用prepared statement方法，而不是query().

Now to execute it in PDO, you should be using prepare()/bindParam()/execute() to create a prepared statement, bind in parameters, and execute it with those parameters (you cannot bind the table name though - that must remain a variable). In your current code, you have a mixup of the the PDO::query() method used for simple static queries and the PDOStatement::execute() method which is used to execute a prepared statement. You should be using the prepared statement method, rather than query().

// Setup the statement with named parameters like :setDay
$sql = "
    SELECT
      due_date,
      date_paid,
      DATEDIFF(due_date, date_paid) as date_interval
    FROM $tbl_name
    WHERE
      DATEDIFF(due_date, date_paid) <= :setDay
    ORDER BY trans_id DESC
    LIMIT :start, :limit
";
// Make PDO throw useful errors on failure
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Prepare the statement
$stmt = $pdo->prepare($sql);

// Bind your 3 parameters and execute it
$stmt->bindParam(':setDay', $setDay, PDO::PARAM_INT);
$stmt->bindParam(':start', $start, PDO::PARAM_INT);
$stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
$stmt->execute();

// Fetch your rows returned from the query
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
// Do something with them
print_r($rows);

我总是建议您花时间与此面向MySQL开发人员的PDO教程，以便将PDO的使用置于上下文中您可能已经熟悉的旧版mysql_*() API.

I always recommend spending time with this PDO tutorial for MySQL developers which places PDO's usage in context of the old mysql_*() API you may already be familiar with.

这篇关于获取日期差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！