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,
jointure.png

the fields etat is a boolean fields and default null, below a picture of what I want as result,
join.png
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

res.png

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:

  1. is that the design of the database is correct to achieve the desired result
  2. why the data from the two tables do not appear
  3. 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

Since you are using a plugin, it has predefined functions upon which your application is built.

  1. Your database structure is correct to achieve this results.

  2. Why your results are not being displayed, I can't actually tell, because I don't know if the leftjoin and process methods used by your plugin are defined to output your information.

  3. Since this is a plugin you can read more about it on its forum or website. But if I can help in actual php codes This will be something like you select the fields you want to display from the two tables but make sure you use their, let me say compound name.

Example
table1.name, table1.contact, table2.service

FROM the first table or second (which depends on the main table you have)
LEFT JOIN the second or let me say slave table.
ON "here you provide you condition"

Example
saying table2's customer id is equal to table1's id.

table2.customerid = table1.id

Hope you understand everything.

I'am sorry i didn't inderstand how may i correct the codes php, i already provide my condition

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' )

but it doesn't work, if there is another solution other than the use of this plugin I'm ready

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.