join tables
join tables
manelll
Posts: 1Questions: 0Answers: 0
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),
the table personnel(id, name, surname, grade, matricule)
th table liste_service_log(id, id_personnel, name_service, etat)
the fields etat is a boolean fields and default null, i want that the application display a table that contains (name,
surname,grade,name_service, etat) , and the fields ( etat ) is the type checkbox , if you check a box it is set to 1 in the database
so i used the example below with some modifications :
https://editor.datatables.net/examples/simple/join.html
and this the codes with modifications:
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>
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();
but in this case the data are not display
what i want to know is:
- am i using the right example
*if there are another example that could help to achieve the result that i want i'am ready
thanks