So I want to insert some data in my database that has timestamp in it, but when I insert the data it keeps telling me Unknown column 'created_at' in 'field list' although I don't have any variable , function or table column called created_at. This has been bugging me for several hours
Everything except that function that has timestamp is working fine
PurchaseOrderView :
<?= $this->extend('layout/template'); ?>
<?= $this->section('content'); ?>
<div class="container">
<div class="row">
<div class="col">
<h1>Purchase Order</h1>
<br>
<h4>Insert data Purchase Order</h4>
<form method='POST' action="/purchase">
<?= csrf_field(); //*csrf secure
?>
<div class="form-group">
<label for="suppid">Supplier id</label>
<input type="text" class="form-control" id="suppid" name='suppid' placeholder="Check Supplier ID on Supplier Product Page">
</div>
<div class="form-group">
<label for="prodid">Product Id</label>
<input type="text" class="form-control" id="prodid" name="prodid" placeholder="Check Product ID on Product Page">
<small id="prodidhelp" class="form-text text-muted">Empty if want to add new product</small>
</div>
<div class="form-group">
<label for="newprodname">New Product Name</label>
<input type="text" class="form-control" id="newprodname" name="newprodname">
<small id="newprodnamehelp" class="form-text text-muted">Input new product name if you want to add new product</small>
</div>
<div class="form-group">
<label for="purprice">Purchased Price</label>
<input type="text" class="form-control" id="purprice" name="purprice" placeholder="Check Product ID on Product Page">
</div>
<div class="form-group">
<label for="qty">qty</label>
<input type="text" class="form-control" id="qty" name="qty">
</div>
<div class="form-group">
<label for="discount">discount</label>
<input type="text" class="form-control" id="discount" name="discount">
</div>
<button type="submit" class="btn btn-primary">Submit</button>
</form>
</div>
<? print_r('data'); ?>
<div class="mx-auto">
<table class="table">
<thead>
<tr>
<th scope="col">id</th>
<th scope="col">supplier id</th>
<th scope="col">supplier name</th>
<th scope="col">product id</th>
<th scope="col">product name</th>
<th scope="col">qty</th>
<th scope="col">discount</th>
<th scope="col">dpp price</th>
<th scope="col">ppn price</th>
<th scope="col">total price</th>
</tr>
</thead>
<tbody>
<?php foreach ($data as $row) { ?>
<tr>
<td><?= $row['id'] ?></td>
<td><?= $row['name'] ?></td>
<td><?= $row['email'] ?></td>
<td><?= $row['address'] ?></td>
</tr>
<?php } ?>
</tbody>
</table>
</div>
</div>
</div>
<?= $this->endSection('content'); ?>
PurchaseOrderController :
<?php
namespace App\Controllers;
use App\Models\ProductModel;
use App\Models\PurchaseOrderModel;
use App\Models\SupplierModel;
class PurchaseOrderController extends BaseController
{
function __construct()
{
$this->PurchaseOrder = new PurchaseOrderModel();
}
public function index()
{
// $model = new PurchaseOrderModel();
// $dataProduct = $model->get();
// dd($dataProduct);
// return view('/pages/Purchase', ['data' => $dataProduct]);
return view('/pages/Purchase');
}
public function insert()
{
$product = new ProductModel();
$purchaseOrder = new PurchaseOrderModel();
$supplierId = $this->request->getVar('suppid');
$productId = $this->request->getVar('prodid');
$newProductName = $this->request->getVar('newprodname');
$purchasedPrice = $this->request->getVar('purprice');
$qty = $this->request->getVar('qty');
$discount = $this->request->getVar('discount');
if ($productId == null || $productId == 0) {
$price1 = $purchasedPrice * $qty;
$discountValue = $discount / 100;
$discountedCalc = $price1 * $discountValue;
$discountedPrice = $price1 - $discountedCalc;
$dppValue = 100 / 111;
$ppnValue = 11 / 100;
$dppPrice = $dppValue * $discountedPrice;
$ppnPrice = $ppnValue * $dppPrice;
$totalPrice = $discountedPrice - $ppnPrice;
$data = [
'supplier_id' => $supplierId,
'product_id' => $productId,
'product_name' => $newProductName,
'purchased_price' => $purchasedPrice,
'qty' => $qty,
'discount' => $discount,
'dpp' => $dppPrice,
'ppn' => $ppnPrice,
'total' => $totalPrice,
'purchased_at' => date("Y-m-d H:i:s"),
];
$purchaseOrder->newProduct($data);
$data2 = [
'supplier_id' => $supplierId,
'product_id' => $productId,
'purchased_price' => $purchasedPrice,
'qty' => $qty,
'discount' => $discount,
'dpp' => $dppPrice,
'ppn' => $ppnPrice,
'total' => $totalPrice,
'purchased_at' => date("Y-m-d H:i:s"),
];
// number_format((float)$dppPrice, 2, ',', ''),
$purchaseOrder->create($data2);
} else {
$priceItem = $product->getPriceItem($productId);
$price1 = $priceItem * $qty;
$discountValue = $discount / 100;
$discountedCalc = $price1 * $discountValue;
$discountedPrice = $price1 - $discountedCalc;
$dppValue = 100 / 111;
$ppnValue = 11 / 100;
$dppPrice = $dppValue * $discountedPrice;
$ppnPrice = $ppnValue * $dppPrice;
$totalPrice = $discountedPrice - $ppnPrice;
$data = [
'supplier_id' => $supplierId,
'product_id' => $productId,
'purchased_price' => $purchasedPrice,
'qty' => $qty,
'discount' => $discount,
'dpp' => $dppPrice,
'ppn' => $ppnPrice,
'total' => $totalPrice,
'purchased_at' => date("Y-m-d H:i:s"),
];
// number_format((float)$dppPrice, 2, ',', ''),
// dd($data);
$purchaseOrder->updateQty($data);
}
// dd($totalPrice);
}
}
PurchaseOrder Model :
<?php
namespace App\Models;
use APP\Models\SupplierProductModel;
use CodeIgniter\Model;
use App\Models\ProductModel;
use Exception;
class PurchaseOrderModel extends Model
{
protected $table = 'purchase_order';
protected $primaryKey = 'id';
protected $allowedFields = [
'supplier_id', 'product_id', 'product_name', 'purchased_price', 'qty', 'discount',
'dpp', 'ppn', 'total', 'purchased_at', 'name', 'price'
];
protected $useTimestamps = true;
public function get()
{
$model = new PurchaseOrderModel();
$data = $model->findAll();
return $data;
}
// public function getAll()
// {
// $builder = $this->db->table('purchase_order');
// $builder->join('supplier', 'supplier.id = purchase_order.supplier_id');
// $builder->join('product', 'product.id = supplier.product_id');
// $builder->select('product.name,purchase_order.qty,product');
// }
public function newProduct($dataInserted)
{
$modelProduct = new ProductModel();
$productId = $dataInserted['product_id'];
$builder = $modelProduct->table('product');
$data = $builder->where('id', $productId)->first();
if (!$data || $dataInserted['product_id'] == null) {
$modelProduct->table('product');
$id = $modelProduct->first();
// $newId = $id['id'] + 1;
// $dataInserted['product_id'] = $newId;
$newProduct = [
'supplier_id' => $dataInserted['supplier_id'],
'name' => $dataInserted['product_name'],
'qty' => $dataInserted['qty'],
'price' => $dataInserted['total']
];
// dd($newProduct);
$modelProduct->save($newProduct);
}
}
public function updateQty($dataInserted)
{
$modelPO = new PurchaseOrderModel();
$modelProduct = new ProductModel();
$builder = $modelProduct->table('product');
$data = [
'supplier_id' => $dataInserted['supplier_id'],
'name' => $dataInserted['name'],
'qty' => $dataInserted['qty'],
'price' => $dataInserted['total']
];
$check = $builder->where('id', $dataInserted['id'])->first();
if ($check) {
$qtyNew = $data['qty'] + $check['qty'];
$data['qty'] = $qtyNew;
$builder->set($data);
$builder->where('id', $dataInserted['id']);
$builder->update();
return $modelProduct->affectedRows();
}
}
public function create($dataInserted)
{
// dd($dataInserted);
$modelPO = new PurchaseOrderModel();
$modelProduct = new ProductModel();
$builder = $modelPO->table('purchase_order');
$builder->save($dataInserted);
}
}
Product Model :
<?php
namespace App\Models;
use CodeIgniter\Model;
class ProductModel extends Model
{
protected $table = 'product';
protected $primaryKey = 'id';
protected $allowedFields = ['supplier_id', 'name', 'qty', 'price'];
public function get()
{
$model = new ProductModel();
$data = $model->findAll();
return $data;
}
public function getPriceItem($suppProId)
{
// dd($suppProId);
$builder = $this->table('product');
$priceItem = $builder->where('id', $suppProId)->first();
if ($priceItem != null) {
return $priceItem['price'];
} else {
throw new PageNotFoundException('Product with id Suplier not found.');
}
}
// public function insertnewProduct($dataInserted)
// {
// $model = new ProductModel();
// $builder = $model->table('product');
// $data = $model->save($dataInserted);
// dd($data);
// }
}
the Unknown column 'created_at' in 'field list' error is at
$purchaseOrder->create($data2);
in the PurchaseOrderController
PurchaseOrderModel
$useTimestamps = true;
This boolean value determines whether the current date is automatically added to all inserts and updates. If
true, will set the current time in the format specified by$dateFormat. This requires that the table have columns namedcreated_at,updated_atanddeleted_atin the appropriate data type.
codeiniter documentation: https://codeigniter.com/user_guide/models/model.html?highlight=dateFormat#usetimestamps