0

I am working on a project using php and am trying to insert some data into a MySQL database. When I run the code online and try to insert the data into the database I am getting a fatal error. From the error message I think it may be to do with the fact the database is using a timestamp. I have tried to use NOW() to get insert the current date and time but it hasn't worked. If anyone could point me in the right direction it would be greatly appreciated.

The image is of the structure of the table (frs_Payment). I am trying to insert the data into this table

This is the error message

Fatal error: Uncaught exception 'Exception' with message 'Database Error [1064] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '12:41:28, NIN012, 12, 1, 1)' at line 1' in E:\xampp\htdocs\CIT2318\Relational database and web integration\models\DAO.php:23 Stack trace: #0 E:\xampp\htdocs\CIT2318\Relational database and web integration\models\LoanModel.php(15): DAO->query('INSERT INTO frs...') #1 E:\xampp\htdocs\CIT2318\Relational database and web integration\controllers\LoanController.php(24): LoanModel->insertRental('104', '10.50', '2015-05-22 12:4...', 'NIN012', '12', '1', '1') #2 {main} thrown in E:\xampp\htdocs\CIT2318\Relational database and web integration\models\DAO.php on line 23

Update, New Error Message

INSERT INTO frs_Payment (payid, amount, paydatetime, empnin, custid, pstatusid, ptid) VALUES (104, 10.50, '2015-05-22 12:41:28', 'NIN012', 1, 1, 1)
Fatal error: Call to a member function fetch_assoc() on boolean in E:\xampp\htdocs\CIT2318\Relational database and web integration\controllers\LoanController.php on line 25

I am now able to insert data into the database but now this new error message has appeared. Any ideas as to how I can resolve this.

This is the loan controller

<?php

session_start();
require_once("../models/LoanModel.php");
require_once("../views/LoanView.php");

$error = "";

if (isset($_POST["Submit"])) {
$payid=($_POST["payid"]);
$amount=($_POST["amount"]);
$paydatetime=($_POST["paydatetime"]);
$empnin=($_POST["empnin"]);
$custid=($_POST["custid"]);
$pstatusid=($_POST["pstatusid"]);
$ptid=($_POST["ptid"]);

if (empty($payid)) {
    //header('Location: ../views/LoanView.php?error=1');
    $error = "Payment ID is required";
} else {

$lgi = new LoanModel;
$result = $lgi->insertRental($payid, $amount, $paydatetime, $empnin,     $custid, $pstatusid, $ptid);
$row = $result->fetch_assoc();
$error = "";
$_SESSION['payid'] = $row["payid"];
$_SESSION['amount'] = $row["amount"];
$_SESSION['paydate'] = $row["paydatetime"];
$_SESSION['employeeid'] = $row["empnin"];
$_SESSION['customerid'] = $row["custid"];
$_SESSION['pstatus'] = $row["pstatusid"];
$_SESSION['ptype'] = $row["ptid"];

 header('Location: ../views/MenuView.php');
}
 echo "<hr>" . $error;
}

?>

This is the Loan Model UPDATED

<?php
 require_once('DAO.php');

 class LoanModel extends DAO{

protected $target = "frs_Payment";

public function __construct(){
  parent::__construct();
}

public function insertRental($payid, $amount, $paydatetime, $employeeid, $customerid, $pstatusid, $ptid){
  $sql = "INSERT INTO frs_Payment (payid, amount, paydatetime, empnin, custid, pstatusid, ptid) VALUES ($payid, $amount, '$paydatetime', '$employeeid', $customerid, $pstatusid, $ptid)";
echo $sql;
  return parent::query($sql);
}
}
?>

This is the loan view

<html>
<head>
</head>
<body>
<h1>Add a new rental</h1>
<form action="../controllers/LoanController.php" method="POST">

  <label for="payid">Payment ID</label>
  <input type="text" id="payid" name="payid">
    <p> </p>
    <label for="amount">Amount</label>
    <input type="text" id="amount" name="amount">
    <p> </p>
    <label for="paydatetime">Payment date/time</label>
    <input type="text" id="paydatetime" name="paydatetime">
    <p> </p>
    <label for="empnin">Empnin</label>
    <input type="text" id="empnin" name="empnin">
    <p> </p>
    <label for="custid">Customer ID</label>
    <input type="text" id="custid" name="custid">
    <p> </p>
    <label for="pstatusid">Payment Status</label>
    <input type="text" id="pstatusid" name="pstatusid">
    <p> </p>
    <label for="ptid">Payment Type</label>
    <input type="text" id="ptid" name="ptid">
    <p> </p>
  <input type="submit" name="Submit" value="Add Rental"> 
    <br></br>
    <a href="../views/MenuView.php">Menu Page</a>
    <br></br>
</form>

Matthew
  • 23
  • 8
  • 1
    One thing I always to do verify my queries is do a `var_dump` or `echo` of the actual SQL and variables in `$lgi->insertRental` and then manually try to execute it in phpMyAdmin. That will help you narrow down if it's a syntax error in SQL or a parameter count mismatch, etc. – WOUNDEDStevenJones Apr 12 '17 at 13:55
  • 1
    Also there's a chance that you're trying to insert a `datetime` into a `timestamp` field. Datetime is in the format `YYYY-MM-DD HH:ii:ss` versus a timestamp which is an integer (number of seconds since unix epoch). See http://stackoverflow.com/questions/5362874/how-to-convert-timestamp-to-datetime-in-mysql for more info. – WOUNDEDStevenJones Apr 12 '17 at 13:57
  • What @WOUNDEDStevenJones said. Echo out the actual SQL being sent to the database and the syntax error should be obvious. – Goose Apr 12 '17 at 13:57
  • Also your code is vulnerable to SQL injection. See http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php for more information. – WOUNDEDStevenJones Apr 12 '17 at 14:30
  • In my DAO I am using a mysqli_real_escape_string. In the model I am calling the "require_once('DAO.php');". Using mysqli_real_escape_string, does this prevent a SQL injection or is this bad practice. – Matthew Apr 12 '17 at 15:21

1 Answers1

0

You need to wrap you date variable with apostrophe.

$sql = "INSERT INTO frs_Payment (payid, amount, paydatetime, empnin, custid, pstatusid, ptid) VALUES ($payid, $amount, '$paydatetime', $employeeid, $customerid, $pstatusid, $ptid)";

And make sure the format is right: try $paydatetime = date("Y-m-d H:i:s");

Vladut
  • 121
  • 8