Hello, I have a little problem , I am currently developing an application with PHP and MySQL , this application should show me more persons, each person must have one or more services and each service can be inversely associated with several people, for this I have 2 tables, one containing the data of the persons (personnel) and the other contains the names of services and a foreign key that references the id of the persons table (liste_service_log), below the image of two tables,
the fields etat is a boolean fields and default null, below a picture of what I want as result,
the fields ( etat ) is the type checkbox , if you check a box it is set to 1 in the database
and here what I actually got
Here are below the code of the page that loads to display my table ,
here the script join.html
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<link rel="shortcut icon" type="image/ico" href="http://www.datatables.net/favicon.ico">
<meta name="viewport" content="initial-scale=1.0, maximum-scale=2.0">
<title>Editor example - Join tables - working with multiple SQL tables</title>
<link rel="stylesheet" type="text/css" href="//cdn.datatables.net/1.10.6/css/jquery.dataTables.css">
<link rel="stylesheet" type="text/css" href="//cdn.datatables.net/tabletools/2.2.4/css/dataTables.tableTools.css">
<link rel="stylesheet" type="text/css" href="../../css/dataTables.editor.css">
<link rel="stylesheet" type="text/css" href="../resources/syntax/shCore.css">
<link rel="stylesheet" type="text/css" href="../resources/demo.css">
<style type="text/css" class="init">
</style>
<script type="text/javascript" language="javascript" src="//code.jquery.com/jquery-1.11.1.min.js"></script>
<script type="text/javascript" language="javascript" src="//cdn.datatables.net/1.10.6/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" language="javascript" src="//cdn.datatables.net/tabletools/2.2.4/js/dataTables.tableTools.min.js"></script>
<script type="text/javascript" language="javascript" src="../../js/dataTables.editor.js"></script>
<script type="text/javascript" language="javascript" src="../resources/syntax/shCore.js"></script>
<script type="text/javascript" language="javascript" src="../resources/demo.js"></script>
<script type="text/javascript" language="javascript" src="../resources/editor-demo.js"></script>
<script type="text/javascript" language="javascript" class="init">
var editor; // use a global for the submit and return data rendering in the examples
$(document).ready(function() {
editor = new $.fn.dataTable.Editor( {
ajax: "../php/join.php",
table: "#example",
fields: [ {
label: "nom_service:",
name: "liste_service_log.nom_service"
}, {
label: "etat:",
name: "liste_service_log.etat"
type: "checkbox",
separator: "|",
options: [
{ label: '', value: 1 }
]
}, {
label: "name:",
name: "personnel.name"
},{
label: "surname:",
name: "personnel.surname"
},
{
label: "grade:",
name: "personnel.grade"
},
]
} );
$('#example').dataTable( {
dom: "Tfrtip",
ajax: {
url: "../php/join.php",
type: 'POST'
},
columns: [
{ data: "liste_service_log.nom_service" },
{ data: "liste_service_log.etat" },
{ data: "personnel.name" },
{ data: "personnel.surname" },
{ data: "personnel.grade" }
],
tableTools: {
sRowSelect: "os",
aButtons: [
{ sExtends: "editor_create", editor: editor },
{ sExtends: "editor_edit", editor: editor },
{ sExtends: "editor_remove", editor: editor }
]
}
} );
} );
</script>
</head>
<body class="dt-example">
<div class="container">
<section>
<h1>Editor example <span>Join tables - working with multiple SQL tables</span></h1>
<table id="example" class="display" cellspacing="0" width="100%">
<thead>
<tr>
<th>nom_service</th>
<th>etat</th>
<th>id_personnel</th>
<th>name</th>
<th>surname</th>
<th>grade</th>
</tr>
</thead>
<tfoot>
<tr>
<th>nom_service</th>
<th>etat</th>
<th>id_personnel</th>
<th>name</th>
<th>surname</th>
<th>grade</th>
</tr>
</tfoot>
</table>
</section>
</div>
</body>
</html>
here the script join.php
<?php
// DataTables PHP library
include( "../../php/DataTables.php" );
// Alias Editor classes so they are easy to use
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Join,
DataTables\Editor\Upload,
DataTables\Editor\Validate;
/*
* Example PHP implementation used for the join.html example
*/
Editor::inst( $db, 'liste_service_log' )
->field(
Field::inst( 'liste_service_log.nom_service' ),
Field::inst( 'liste_service_log.etat' ),
Field::inst( 'liste_service_log.id_personnel' )
->options( 'personnel', 'id', 'id' ),
Field::inst( 'personnel.id' ),
Field::inst( 'personnel.name' ),
Field::inst( 'personnel.surname' ),
Field::inst( 'personnel.grade' )
)
->leftJoin( 'personnel', 'personnel.id', '=', 'liste_service_log.id_personnel' )
->process($_POST)
->json();
actually I used the plug-in Editor-PHP-1.4.2 and I proceeded like the example in the link below with some modifications
Click Here
What I want to know:
- is that the design of the database is correct to achieve the desired result
- why the data from the two tables do not appear
- how to correct my mistakes
Is the design of the database is correct to achieve the desired resulting output
Therefore, the data from both table is not displayed
how to correct my mistakes
