php - return nodays interest为null

一开始它在nodays和interest上都返回空值，但我意识到它没有分配到要放入计算的位置，所以我添加了AS。我正在尝试计算DATEDIFF，因此它将插入nodays和interest列。我试过分配DATEDIFF($mdate,$pdate) AS nodays和(DATEDIFF($mdate,$pdate) * $amt / 365 * 0.1) AS interest，但它仍然给我带来语法错误。我到底做错什么了？

$sql="INSERT INTO contacts (
  nodays, interest, pdate, mdate, amt, first, last,
  pid, cno, madd, bene, swc, bacc, bank, badd, bno
)
VALUES (
  DATEDIFF($mdate,$pdate) AS nodays,
  (DATEDIFF($mdate,$pdate) * $amt / 365 * 0.1) AS interest,
  '$pdate','$mdate','$amt','$first','$last',
  '$pid','$cno','$madd','$bene','$swc','$bacc','$bank','$badd','$bno'
)";

if (!mysqli_query($con,$sql)) {
  die('Error: ' . mysqli_error($con));
}
echo "Client record has been added to the database!";

mysqli_close($con);

错误消息：
错误：您的SQL语法有错误；请检查与MySQL服务器版本相对应的手册，以获取正确的语法，以便在第6行使用“as nodays”（DateDiff（2014-12-312014-07-24）*50000/365*0.1）as interest f
固定完整编码（参考）：

// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// escape variables for security
$pdate = mysqli_real_escape_string($con, $_POST['pdate']);
$mdate = mysqli_real_escape_string($con, $_POST['mdate']);
$amt = mysqli_real_escape_string($con, $_POST['amt']);
$first = mysqli_real_escape_string($con, $_POST['first']);
$last = mysqli_real_escape_string($con, $_POST['last']);
$pid = mysqli_real_escape_string($con, $_POST['pid']);
$cno = mysqli_real_escape_string($con, $_POST['cno']);
$madd = mysqli_real_escape_string($con, $_POST['madd']);
$bene = mysqli_real_escape_string($con, $_POST['bene']);
$swc = mysqli_real_escape_string($con, $_POST['swc']);
$bacc = mysqli_real_escape_string($con, $_POST['bacc']);
$bank = mysqli_real_escape_string($con, $_POST['bank']);
$badd = mysqli_real_escape_string($con, $_POST['badd']);
$bno = mysqli_real_escape_string($con, $_POST['bno']);

$sql="INSERT INTO contacts (
  nodays, interest, pdate, mdate, amt, first, last,
  pid, cno, madd, bene, swc, bacc, bank, badd, bno
)
VALUES (
  DATEDIFF('$mdate','$pdate'),
  (DATEDIFF('$mdate','$pdate') * '$amt' / 365 * 0.1),
  '$pdate','$mdate','$amt','$first','$last',
  '$pid','$cno','$madd','$bene','$swc','$bacc','$bank','$badd','$bno'
)";


if (!mysqli_query($con,$sql)) {
  die('Error: ' . mysqli_error($con));
}
echo "Client record has been added to the database!";

mysqli_close($con);
?>

最佳答案

这可能是插入这个的更好方法

$nodays= date_diff( $mdate, $pdate, true);
$intrst=$nodays * $amt / 365 * 0.1;

$sql="INSERT INTO contacts (
  nodays, interest, pdate, mdate, amt, first, last,
  pid, cno, madd, bene, swc, bacc, bank, badd, bno
)
VALUES (
  $nodays,
  $intrst,
  '$pdate','$mdate','$amt','$first','$last',
  '$pid','$cno','$madd','$bene','$swc','$bacc','$bank','$badd','$bno'
)";

if (!mysqli_query($con,$sql)) {
  die('Error: ' . mysqli_error($con));
}
echo "Client record has been added to the database!";

mysqli_close($con);

mysql函数datediff（）不能有php计算。
编辑
php中防止sql注入的安全插入方法

$nodays= date_diff( $mdate, $pdate, true);
$intrst=$nodays * $amt / 365 * 0.1;
$stmt = mysqli_prepare($link, "INSERT INTO contacts (
    nodays, interest, pdate, mdate, amt, first, last, pid, cno, madd, bene, swc, bacc, bank, badd, bno)
    VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
mysqli_stmt_bind_param($stmt, "iissssssssssssss", $nodays, $intrst, $pdate,$mdate,$amt,$first,$last,$pid,$cno,$madd,$bene,$swc,$bacc,$bank,$badd,$bno);
mysqli_stmt_execute($stmt);
echo "Client record has been added to the database!";
mysqli_stmt_close($stmt);