return nodays interest are null

Question

It's returning null on both nodays and interest at first, but I realize it was not assigned to where the calculation to be put into so I added AS. I am trying to calculate DATEDIFF so it will insert into nodays and interest column. I have tried assigning DATEDIFF($mdate,$pdate) AS nodays and (DATEDIFF($mdate,$pdate) * $amt / 365 * 0.1) AS interest, yet it's still giving me syntax error. What exactly am I doing wrong here?

$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);

Error message:

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS nodays, (DATEDIFF(2014-12-31,2014-07-24) * 50000 / 365 * 0.1) as interest F' at line 6

Fixed Full Coding (Reference):

// 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);
?>

Could it be, that at least one of your string variables contains a *'* ? — schlonzo, Jul 24 '14 at 06:22
`Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS nodays, (DATEDIFF(2014-12-31,2014-07-24) * 50000 / 365 * 0.1) as interest F' at line 6` — Alex, Jul 24 '14 at 06:25
remove the `AS`. MySQL doesn't care how you name your variables on INSERT. — Gerald Schneider, Jul 24 '14 at 06:25

Mohit S · Accepted Answer · 2014-07-24T07:02:10.603

3

This might be a better way to insert this

$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 function DATEDIFF() cannot have PHP calculations.

EDIT

Secured way to insert with prevention to SQL-injection in PHP

$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);

edited Jul 24 '14 at 07:02

answered Jul 24 '14 at 06:31

Mohit S

13,723
6
34
69

I wonder who voted this up, since its wide open to injection attacks; of course assuming the data isn't escaped. – Daedalus Jul 24 '14 at 06:31
@BugFreeSolution `' DROP ALL;`. I suggest you give [this a good read](http://stackoverflow.com/q/60174/785241), before you further upvote answers like this. – Daedalus Jul 24 '14 at 06:36
@Daedalus - this answer is what OP is looking for .. OP is not looking for security here. So better look at the question. – Jul 24 '14 at 06:40
@BugFreeSolution If you think its alright to expose a new user to bad habits, you have some re-thinking to do(its never alright. The user already stated they're new to mysql/sql, and giving them bad advice is setting them up for failure, if not something worse). – Daedalus Jul 24 '14 at 06:40
BugFreeSolution Thanks for supporting me. @Daedalus I'll take care of it from next time. Thanks for your comments – Mohit S Jul 24 '14 at 06:43
@MohitShrivastava If you fix your answer, I can go ahead and remove the downvote, and replace it with an upvote. – Daedalus Jul 24 '14 at 06:44
Sure @Daedalus let me try – Mohit S Jul 24 '14 at 06:46
@Daedalus I think I did what is expected. – Mohit S Jul 24 '14 at 07:02

score 2 · Answer 2 · edited May 23 '17 at 11:52

2

Your parameters to DATEDIFF should be quoted strings since those are PHP values, not MySQL fields

DATEDIFF($mdate,$pdate)

Should be

DATEDIFF('$mdate','$pdate')

Why take all that hassle of string manipulation and run an exploitable setup? See

How can I prevent SQL injection in PHP?

edited May 23 '17 at 11:52

Community

1
1

answered Jul 24 '14 at 06:21

Hanky Panky

46,730
8
72
95

So I should do this in 2 queries? Does query placement needs to be specific order? – Alex Jul 24 '14 at 06:28
It's giving me `Incorrect parameter count in the call to native function 'DATEDIFF'` – Alex Jul 24 '14 at 06:33
@Daedalus I am new to MySQL, will probably on once I get this solved. Thanks for reminding :) – Alex Jul 24 '14 at 06:36
That means there are some extra quotes in your strings. Do this: before execution of your query do an `echo $sql;` and paste the result in your question. – Hanky Panky Jul 24 '14 at 06:42
Fixed, missed '$amt' quotations. – Alex Jul 24 '14 at 07:22

user3462418 · Answer 3 · 2014-07-25T01:58:02.023

0

You should try this... EDIT: I tried this, it's working in my end.

$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);

edited Jul 25 '14 at 01:58

answered Jul 24 '14 at 06:33

user3462418

59
5

Its also bad advice. I suggest you give [this a good read](http://stackoverflow.com/q/60174/785241). – Daedalus Jul 24 '14 at 06:49

return nodays interest are null

Fixed Full Coding (Reference):

3 Answers3

EDIT