I have some tables:
`table_customer` (
`id_customer` int(11) NOT NULL,
`fullname` varchar(255) NOT NULL,
`email` varchar(255) DEFAULT NULL,
`birthday` date DEFAULT NULL,
`note` text NOT NULL
)
`table_address` (
`id_address` int(11) NOT NULL,
`address` varchar(255) DEFAULT NULL,
`city` varchar(50) DEFAULT NULL,
`state` varchar(100) DEFAULT NULL,
`zipcode` varchar(12) DEFAULT NULL,
`phone` varchar(32) DEFAULT NULL
)
`table_customer_address` (
`id_customer_address` int(11) NOT NULL,
`id_customer` int(11) NOT NULL,
`id_address` int(11) NOT NULL,
`is_default_address` tinyint(1) UNSIGNED DEFAULT NULL,
`is_current_address` tinyint(1) UNSIGNED DEFAULT NULL
)
`table_service` (
`id_service` tinyint(3) UNSIGNED NOT NULL,
`service_name` varchar(255) NOT NULL
)
`table_customer_service` (
`id_customer_service` int(11) NOT NULL,
`id_customer` int(11) NOT NULL,
`id_service` int(11) NOT NULL
)
`table_user` (
`id_user` smallint(6) NOT NULL,
`username` varchar(50) NOT NULL,
`password` varchar(50) NOT NULL,
`usergroup` tinyint(4) NOT NULL
)
`table_user_customer` (
`id_user_customer` int(11) NOT NULL,
`id_user` int(11) NOT NULL,
`id_customer` int(11) DEFAULT NULL,
`date_add` datetime DEFAULT NULL
)
- 1 User can manage multi Customers
- 1 Customer have multi Address
- 1 Customer have multi Service
Now, I want to show all information of the customers (default address on homepage and all addresses, service in Editor) have a Manager. I have no problem with Select. I show fine as I want (as the picture)
But in the editor (eg: Edit Customer 1), it doesn't work:
- It show corrected selected service. But when I choose another service and click update, it delete all services of this customer
- It don't show any address of customers
This is my server script:
$editor = Editor::inst( $db, 'table_user_customer', 'id_user_customer' )
->fields(
Field::inst( 'table_user_customer.id_customer' )->set(false),
Field::inst( 'table_user_customer.id_user' )
->options( Options::inst()
->table( 'table_user' )
->value( 'id_user' )
->label( 'username' )
),
Field::inst( 'table_user.username' ),
Field::inst( 'table_customer.fullname' ),
Field::inst( 'table_customer.birthday' ),
Field::inst( 'table_customer.email' ),
Field::inst( 'default_address.id_address' ),
Field::inst( 'default_address.address' ),
Field::inst( 'default_address.city' ),
Field::inst( 'default_address.state' ),
Field::inst( 'default_address.zipcode' ),
Field::inst( 'default_address.phone' ),
Field::inst( 'table_customer.note' ),
)
->leftJoin( 'table_user', 'table_user.id_user', '=', 'table_user_customer.id_user' )
->leftJoin( 'table_customer', 'table_customer.id_customer', '=', 'table_user_customer.id_customer' )
->leftJoin(
'table_customer_address',
'table_customer_address.id_customer = table_customer.id_customer AND table_customer_address.is_default_address = 1'
)
->leftJoin(
'table_address as default_address',
'default_address.id_address = table_customer_address.id_address'
)
->join(
Mjoin::inst( 'table_service' )
->link( 'table_user_customer.id_customer', 'table_customer_service.id_customer' )
->link( 'table_service.id_service', 'table_customer_service.id_service' )
->fields(
Field::inst( 'id_service' )
->validator( Validate::required() )
->options( Options::inst()
->table( 'table_service' )
->value( 'id_service' )
->label( 'service_name' )
),
Field::inst( 'service_name' )
)
)
->join(
Mjoin::inst( 'table_address' )
->link( 'table_user_customer.id_customer', 'table_customer_address.id_customer' )
->link( 'table_address.id_address', 'table_customer_address.id_address' )
->fields(
Field::inst( 'id_address' ),
Field::inst( 'address' ),
Field::inst( 'city' ),
Field::inst( 'state' ),
Field::inst( 'zipcode' ),
Field::inst( 'phone' ),
)
)
->debug(true)
->process( $_POST )
->json();
And my js script is:
<script>
var editor; // use a global for the submit and return data rendering in the table_customers
$(document).ready(function() {
var addressEditor = new $.fn.dataTable.Editor( {
ajax: '../faq/controllers/fetch-history.php',
fields: [
{
"label": "Address 1",
"name": "table_address.address"
},
{
"label": "City",
"name": "table_address.city"
},
{
"label": "State",
"name": "table_address.state"
},
{
"label": "Zipcode",
"name": "table_address.zipcode"
},
{
"label": "Phone",
"name": "table_address.phone"
},
]
} );
var serviceEditor = new $.fn.dataTable.Editor( {
ajax: '../faq/controllers/fetch-history.php',
fields: [
{
"label": "Service Name",
"name": "table_service[].service_name"
}
]
} );
editor = new $.fn.dataTable.Editor( {
ajax: "../faq/controllers/fetch-history.php",
table: "#table_customer",
fields: [
{
"name": "table_user_customer.id_customer",
type: "hidden"
},
{
"name": "table_service.id_service",
type: "hidden"
},
{
"label": "Full Name",
"name": "table_customer.fullname"
},
{
"label": "DOB",
"name": "table_customer.birthday"
},
{
"label": "Note",
"name": "table_customer.note"
},
{
"label": "User",
"name": "table_user_customer.id_user",
type: "select"
},
{
label: 'Service',
name: 'table_service[].id_service',
type: 'datatable',
multiple: true
},
{
label: 'Addresses',
name: 'table_address[].id_address',
type: 'datatable',
config: {
buttons: [
{ extend: 'create', editor: addressEditor },
{ extend: 'edit', editor: addressEditor },
{ extend: 'remove', editor: addressEditor }
],
}
}
],
bootstrap: {
floatingLabels: true
}
} );
/*
// Activate an inline edit on click of a table cell
$('#table_customer').on( 'click', 'tbody td:not(:first-child)', function (e) {
editor.inline( this );
} );
*/
$('#table_customer').DataTable( {
columnDefs: [{
"defaultContent": "-",
"targets": "_all"
}],
//serverSide: true,
processing: true,
//lengthChange: false,
dom: "Bfrtip",
ajax: {
url: "../faq/controllers/fetch-history.php",
type: 'POST'
},
order: [[ 1, 'asc' ]],
columns: [
{
data: null,
defaultContent: '',
className: 'select-checkbox',
orderable: false
},
{ data: "table_user_customer.id_customer" },
{ data: "table_customer.fullname" },
{ data: "table_customer.birthday" },
{ data: "table_customer.email" },
{ data: "default_address.address"},
{ data: "default_address.city" },
{ data: "default_address.state" },
{ data: "default_address.zipcode" },
{ data: "default_address.phone" },
{ data: "table_customer.note" },
{ data: "table_service", render: "[, ].service_name" },
{ data: "table_address", render: "[, ].id_address" },
{ data: "table_user.username", editField: "table_user_customer.id_user" },
],
select: {
style: 'os',
selector: 'td:first-child'
},
buttons: [
{ extend: "create", editor: editor },
{ extend: "edit", editor: editor },
{ extend: "remove", editor: editor }
],
} );
} );
</script>
I want it to work as the picture:
