javascript - 添加两个 MySql 变量在一起，一个日期和一个数字与 php

Question

我想在我的网站上使用 php 添加两个 mysql 变量。我想获取“日期”并添加“条款”(这是一组天数::30,60,90)以生成“Due_Date”。

Home.php

  $query = mysql_query("select * from Book1");
    $query = mysql_query("SELECT id, rep, Date, Name, P_O, ADDDATE(Date, Terms) AS Due_Date, Terms, GREATEST(DATEDIFF(NOW(), Due_Date), 0) AS Aging, Open_Balance from Book1");

?>

<div id="wrapper">

    <!-- Sidebar -->
    <div id="sidebar-wrapper">
        <ul class="sidebar-nav">
            <li class="sidebar-brand">
                <a href="#">
                    Quickbooks Invoices
                </a>
            </li>
            <li>
                <a href="#">Home</a>
            </li>
            <li>
                <a href="addInvoice.php">Add Invoice</a>
            </li>
        </ul>
    </div>
    <!-- /#sidebar-wrapper -->

    <!-- Page Content -->
    <div id="page-content-wrapper">
        <div class="container-fluid">
            <div class="row">
                <div class="col-lg-12">
                    <h1>Quickbook Invoices</h1>
                    <a href="#menu-toggle" class="btn btn-default" id="menu-toggle">Toggle Menu</a>
                    <button class="btn btn-primary" id="MyTableBtn">Number of Invoices</button>
                </div>
            </div>
            <div class="row">
                <div class="col-md-12">
                    <table class="table table-bordered table-striped table-responsive sortable" id="MyTable">
                        <tr class="header">
                            <td>id</td>
                            <td>Rep</td>
                            <td>Date Services Rendered</td>
                            <td>Name</td>
                            <td>P_O</td>
                            <td>Due Date</td>
                            <td>Terms</td>
                            <td>Aging</td>
                            <td>Open Balance</td>
                            <td>remove</td>
                        </tr>
                        <?php 

                            while($row = mysql_fetch_array($query)) {


                                $className ="";
                                if ($row['Aging'] >= 45)
                                {
                                    $className="danger";
                                }
                                else if($row['Aging'] >= 25 && $row['Aging'] <= 44)
                                {
                                    $className="warning";
                                }

                                echo "<tr class='$className'>";
                                echo "<td>".$row['id']."</td>";
                                echo "<td>".$row['Rep']."</td>";
                                echo "<td>".$row['Date']."</td>";
                                echo "<td>".$row['Name']."</td>";
                                echo "<td>".$row['P_O']."</td>";
                                echo "<td>".$row['Due_Date']."</td>";
                                echo "<td>".$row['Terms']."</td>";
                                echo "<td>".$row['Aging']."</td>";
                                echo "<td>".$row['Open_Balance']."</td>";
                                echo "<td><a href='deletepage.php?id=" .$row['id']. "' value='" .$row['id']. "' class='btn btn-success'> Paid</a></td>";

                            }
                        ?>
                    </table>
                </div>
            </div>
        </div>
    </div>
    <!-- /#page-content-wrapper -->

</div>

因此，它必须根据条款集确定截止日期，即从他们的发票发出后 30=30 天=1 个月。所以 date= 10/23/2015 terms=30 Due_Date 将 = 11/23/2015 然后根据新计算的 due_date 确定老化。

addInvoice.php

//escape user inputs for security
$rep = mysqli_real_escape_string($link, $_POST['rep']);
$date = mysqli_real_escape_string($link, $_POST['Date']);
$name = mysqli_real_escape_string($link, $_POST['name']);
$P_O = mysqli_real_escape_string($link, $_POST['P_O']);
$due_date = mysqli_real_escape_string($link, $_POST['Due_Date']);
$terms = mysqli_real_escape_string($link, $_POST['terms']);
$aging = mysqli_real_escape_string($link, $_POST['aging']);
$open_balance = mysqli_real_escape_string($link, $_POST['open_balance']);

//attempt insert query execution
$sql = "INSERT INTO Book1 (Rep, Date, Name, P_O, Due_Date, Terms, Aging, Open_Balance)
VALUES ('$rep', '$date', '$name', '$P_O', 'ADDDATE(Date, Terms) AS $due_date', '$terms', 'GREATEST(DATEDIFF(NOW(), Due_Date), 0) AS $aging', '$open_balance')";
if(mysqli_query($link, $sql)){
    header("location: Home.php");
} else {
    echo "ERROR: Could not execute $sql." .mysqli_error($link);
}

//close connection
mysqli_close($link);

现在的问题是，当我添加新发票时，它会完美地计算到期日，但现在不会计算账龄。有帮助吗？

Answer 1

SELECT id,
       rep,
       Date,
       Name,
       P_O,
       ADDDATE(Date, Terms) AS DueDate,
       Terms,
       GREATEST(DATEDIFF(NOW(), ADDDATE(Date, Terms)), 0) AS Aging,
       Open_Balance
from   Book1