Unknown column
Unknown column
I receive this error when trying to update a row:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'staff.date' in 'field list'
I don't have any reference of 'staff.date' in my editor, but I do have 'staff.date' in my sql though:
PHP
<?php
// DataTables PHP library
include( "../DataTables/Editor-PHP-1.6.2/php/DataTables.php" );
// initialize vars
$date = "";
$region = "";
$store = "";
$user = "";
// Alias Editor classes so they are easy to use
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Mjoin,
DataTables\Editor\Options,
DataTables\Editor\Upload,
DataTables\Editor\Validate;
$date = isset($_POST["date"]) ? date("Y-m-d",strtotime($_POST['date'])): "";
$region = isset($_POST["region"]) ? $_POST['region']: "";
$store = isset($_POST["store"]) ? $_POST['store']: "";
$user = isset($_POST["user"]) ? $_POST["user"] : "";
// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, "staff" )
->fields(
Field::inst( "staff.store" ),
Field::inst( "staff.date" ),
Field::inst( "staff.empl_num" )
->options( Options:: inst()
->table( "empfile" )
->value( "emp_number" )
->label( array( "first_name", "last_name" ) )
->where( function ($q) use ($store) {
$q->where( "empfile.store", $store);
})
),
Field::inst( "empfile.first_name" ),
Field::inst( "empfile.last_name" ),
Field::inst( "staff.position" ),
Field::inst( "staff.time_in" ),
Field::inst( "staff.reg_hours" ),
Field::inst( "staff.reg_pay" ),
Field::inst( "staff.ovr_hours" ),
Field::inst( "staff.ovr_pay" ),
Field::inst( "staff.srvr_sales" ),
Field::inst( "staff.tips_decl" ),
Field::inst( "staff.tips_chrg" ),
Field::inst( "staff.tip" ),
Field::inst( "staff.meal_break_time" ),
Field::inst( "staff.time_before_break" ),
Field::inst( "staff.shift" ),
Field::inst( "staff.adjustment" ),
Field::inst( "staff.mb_docs_req" ),
Field::inst( "staff.mb_category" )
->options( Options:: inst()
->table( "writeups_category" )
->value( "id" )
->label( array( "name" ))
->where( function ($q) {
$q->where( "writeups_category.type", "MB");
})
),
Field::inst( "mb.name" ),
Field::inst( "mb.type" ),
Field::inst( "staff.tips_docs_req" ),
Field::inst( "staff.tips_category" )
->options( Options:: inst()
->table( "writeups_category" )
->value( "id" )
->label( array( "name" ))
->where( function ($q) {
$q->where( "writeups_category.type", "Tips");
})
),
Field::inst( "tips.name" ),
Field::inst( "tips.type" ),
Field::inst( "staff.user_id" ),
Field::inst( "staff.region_id" ),
Field::inst( "staff.created_date" ),
Field::inst( "staff.modified_date" )
)
->leftJoin( "writeups_category as mb", "mb.id", "=", "staff.mb_category")
->leftJoin( "writeups_category as tips", "tips.id", "=", "staff.tips_category")
->leftJoin( "empfile", "empfile.emp_number", "=", "staff.empl_num")
->where( "staff.store", $store )
->where( "staff.date", $date )
->where( "staff.shift", "AM" )
->where( "empfile.store", $store )
->process($_POST)
->json();
<?php
>
```
?>
EDITOR
```js
var staffAMEditor = new $.fn.dataTable.Editor({
ajax: "/functions/inputStaffAm.php",
table: "#staff-emp-am-records",
fields: [{
label: "MB Docs Required:",
name: "staff.mb_docs_req",
type: 'checkbox',
options: [
{ label: "Yes", value: "Yes" }
],
separator: '',
unselectedValue: "No"
}, {
label: "MB Category:",
name: "staff.mb_category",
type: 'select',
placeholderDisabled: false,
placeholder: '',
placeholderValue: '0'
}, {
label: "Tips Docs Required:",
name: "staff.tips_docs_req",
type: 'checkbox',
options: [
{ label: "Yes", value: "Yes" }
],
separator: '',
unselectedValue: "No"
}, {
label: "Tips Category:",
name: "staff.tips_category",
type: 'select',
placeholderDisabled: false,
placeholder: '',
placeholderValue: '0'
}, {
name: "staff.user_id",
type: "hidden",
def: user
}, {
name: "staff.region_id",
type: "hidden",
def: region
}
]
});
//Pass date, user, region, store to editor
staffAMEditor.on( 'initEdit', function ( e) {
staffAMEditor.val("staff.user_id", user);
staffAMEditor.val("staff.region_id", region);
});
// Reload DataTable
staffAMEditor.on( 'submitComplete', function ( e, json, data ) {
$('#staff-emp-am-records').DataTable().ajax.reload();
});
// Staff AM DataTable
$('#staff-emp-am-records').DataTable({
dom: "Bfrtip",
ajax: {
url: "/functions/inputStaffAm.php",
type: "post",
data: function(d){
d.date = $('#curr-date').val();
d.region = $('#curr-region').val();
d.store = $('#curr-store').val();
d.user = $('#curr-user').val();
}
},
columns: [
{
data: null,
defaultContent: '',
className: 'select-checkbox',
"orderable": false,
"width": "5%"
},
{data: "staff.position", "width": "10%"},
{
data: null, render: function(data, type, row){
// Combine the first and last names into a single table field
return data.empfile.first_name + ' ' + data.empfile.last_name;
},
"width": "15%"
},
{data: "staff.meal_break_time", "width": "8%"},
{data: "staff.time_before_break", "width": "8%"},
{
data: null, render: function(data, type, row){
if(data.staff.reg_hours >= 6){
return '<span class="negative">' + parseFloat(Math.round(data.staff.reg_hours * 100) / 100).toFixed(1) + '</span> / $' + parseFloat(Math.round(data.staff.reg_pay * 100) / 100).toFixed(2);
}else{
return parseFloat(Math.round(data.staff.reg_hours * 100) / 100).toFixed(1) + ' / $' + parseFloat(Math.round(data.staff.reg_pay * 100) / 100).toFixed(2);
}
},
"width": "10%"
},
{
data: null, render: function(data, type, row){
if(data.staff.ovr_hours != null){
return '<span class="negative">' + parseFloat(Math.round(data.staff.ovr_hours * 100) / 100).toFixed(1) + '</span> / $' + parseFloat(Math.round(data.staff.ovr_pay * 100) / 100).toFixed(2);
}else{
return parseFloat(Math.round(data.staff.ovr_hours * 100) / 100).toFixed(1) + ' / $' + parseFloat(Math.round(data.staff.ovr_pay * 100) / 100).toFixed(2);
}
},
"width": "10%"
},
{data: "staff.srvr_sales",
"width": "7%",
render: $.fn.dataTable.render.number( ',', '.', 0, '$' )
},
{
data: null, render: function(data, type, row){
var chrg = data.staff.tips_chrg;
var decl = data.staff.tips_decl;
var tip = data.staff.tip;
if(chrg == null){chrg = "0"};
if(decl == null){decl = "0"};
if(tip == null){tip = "0"};
if(tip > 0 && tip < 10){
return '$' + parseFloat(Math.round(chrg * 100) / 100).toFixed(2) + ' / $' + parseFloat(Math.round(decl * 100) / 100).toFixed(2) + ' / <span class="negative">' + tip + '%</span>';
}else{
return '$' + parseFloat(Math.round(chrg * 100) / 100).toFixed(2) + ' / $' + parseFloat(Math.round(decl * 100) / 100).toFixed(2) + ' / ' + tip + '%';
}
},
"width": "15%"
},
{data: "staff.adjustment", "width": "15%"},
{data: "staff.mb_docs_req", "width": "5%"},
{data: "mb.name", "width": "10%"},
{data: "staff.tips_docs_req", "width": "5%"},
{data: "tips.name", "width": "10%"}
],
columnDefs: [
{
"targets": 3,
"createdCell": function (td, cellData, rowData, row, col) {
if(cellData != null){
if ( timeToSeconds(cellData) < timeToSeconds('00:30:00') ) {
$(td).addClass('negative');
}
}
}
},
{
"targets": 4,
"createdCell": function (td, cellData, rowData, row, col) {
if(cellData != null){
if ( timeToSeconds(cellData) > timeToSeconds('05:00:00') ) {
$(td).addClass('negative');
console.log(timeToSeconds(cellData));
}
}
}
}
],
keys: {
columns: ':not(:first-child)',
editor: staffAMEditor
},
select: {
style: 'os',
selector: 'td:first-child',
blurable: true
},
buttons: [
{extend: "edit", editor: staffAMEditor}
]
});
//parse time
function timeToSeconds(time) {
time = time.split(/:/);
return time[0] * 3600 + time[1] * 60 + time[2] * 1;
}
This question has an accepted answers - jump to answer
This discussion has been closed.
Answers
I recreated the table and it fixed it. Not sure what happened.
Thanks for posting back - good to hear you've got it fixed.
Allan