在DATEDIFF中输入其他计算

Question

My client is requesting the database to be able to calculate days from date1 to date2 and I have used DATEDIFF, but is it possible that I can add extra calculation into the coding?

For example:

(date1-date2)/365*0.1

Can this calculation be done?

This is the code I have using on my output.php

$result = mysqli_query($con,"SELECT *,DATEDIFF(mdate,pdate)/365*0.1
FROM contacts WHERE DATEDIFF(mdate,pdate)");



    echo 
    "<table border='1'>
    <tr>
    <th>ID</th>
    <th>Difference Date</th>
    <th>Purchased Date</th>
    <th>Maturity Date</th>
    <th>Amount</th>
    <th>First Name</th>
    <th>Last Name</th>
    <th>Passport/ID</th>
    <th>Contact No.</th>
    <th>Mailing Address</th>
    <th>Beneficiary Name</th>
    <th>Swift Code</th>
    <th>Bank Account</th>
    <th>Bank Name</th>
    <th>Bank Address</th>
    <th>Bank Contact No.</th>
    </tr>";

    while($row = mysqli_fetch_array($result)) {
      echo "<tr>";
      echo "<td>" . $row['id'] . "</td>";
      echo "<td>" . $row['DATEDIFF(mdate,pdate)'] . "</td>";
      //echo "<td>" . $row['ddate'] . "</td>";
      echo "<td>" . $row['pdate'] . "</td>";
      echo "<td>" . $row['mdate'] . "</td>";
      echo "<td>" . $row['amt'] . "</td>";
      echo "<td>" . $row['first'] . "</td>";
      echo "<td>" . $row['last'] . "</td>";
      echo "<td>" . $row['pid'] . "</td>";
      echo "<td>" . $row['cno'] . "</td>";
      echo "<td>" . $row['madd'] . "</td>";
      echo "<td>" . $row['bene'] . "</td>";
      echo "<td>" . $row['swc'] . "</td>";
      echo "<td>" . $row['bacc'] . "</td>";
      echo "<td>" . $row['bank'] . "</td>";
      echo "<td>" . $row['badd'] . "</td>";
      echo "<td>" . $row['bno'] . "</td>"; 
      echo "</tr>";
    }

    echo "</table>";

    mysqli_close($con);
    ?>

Answer 1

There is a typo at:

echo "<td>" . $row['DATEDIFF(mdate,pdate)'] . "</td>";

It should be

echo "<td>" . $row['DATEDIFF(mdate,pdate)/365*0.1'] . "</td>";

To avoid this, you can alias the calculated field in your query like this:

$result = mysqli_query($con,"SELECT *,DATEDIFF(mdate,pdate)/365*0.1 as difference 
                             FROM contacts WHERE DATEDIFF(mdate,pdate)");

And reference it like this:

echo "<td>" . $row['difference'] . "</td>";