Fetching data from 2 mysql tables

singleb

Hello,

I just started using datatables and i'm learning a lot from the examples
However i can't figure out how to combine 2 mysql tables in a datatables.
I have 2 tables, one listing material, and the other one listing rooms.
I would like to display the material table with the name of rooms instead of their id.
Hope you can help to solve this.

Here are my 2 files

<?php

//fetch.php

include('../../traitements/database.php');

$column = array("id", "ref", "type", "marque", "salle_Id", "dispo");


$query = "SELECT * FROM clavier";

if(isset($_POST["search"]["value"]))
{
    $query .= '
 WHERE ref LIKE "%'.$_POST["search"]["value"].'%" 
 OR type LIKE "%'.$_POST["search"]["value"].'%" 
 OR marque LIKE "%'.$_POST["search"]["value"].'%" 
 OR salle_Id LIKE "%'.$_POST["search"]["value"].'%" 
 OR dispo LIKE "%'.$_POST["search"]["value"].'%" 
 ';
}

if(isset($_POST["order"]))
{
    $query .= 'ORDER BY '.$column[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].' ';
}
else
{
    $query .= 'ORDER BY id DESC ';
}
$query1 = '';

if($_POST["length"] != -1)
{
    $query1 = 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}

$statement = $connect->prepare($query);
$statement->execute();
$number_filter_row = $statement->rowCount();
$statement = $connect->prepare($query . $query1);
$statement->execute();
$result = $statement->fetchAll();
$data = array();

foreach($result as $row)
{
    $sub_array = array();
    $sub_array[] = $row['id'];
    $sub_array[] = $row['ref'];
    $sub_array[] = $row['type'];
    $sub_array[] = $row['marque'];
    $sub_array[] = $row['salle_Id'];
    $sub_array[] = $row['dispo'];
    $data[] = $sub_array;
}

function count_all_data($connect)
{
    $query = "SELECT * FROM clavier";
    $statement = $connect->prepare($query);
    $statement->execute();
    return $statement->rowCount();
}

$output = array(
    'draw'   => intval($_POST['draw']),
    'recordsTotal' => count_all_data($connect),
    'recordsFiltered' => $number_filter_row,
    'data'   => $data
);

echo json_encode($output);

---------------------------->

//read.php

<script type="text/javascript" language="javascript" >
        $(document).ready(function(){

            var dataTable = $('#sample_data').DataTable({
                "processing" : true,
                "serverSide" : true,
                "order" : [],
                "ajax" : {
                    url:"fetch.php",
                    type:"POST"
                }
            });

            $('#sample_data').on('draw.dt', function(){
                $('#sample_data').Tabledit({
                    url:'http://localhost:8888/Admin/pages/clavier/action.php',
                    dataType:'json',
                    buttons: {
                        edit: {
                            class: 'btn btn-sm btn-success',
                            html: '<svg width="1em" height="1em" viewBox="0 0 16 16" class="bi bi-pencil" fill="currentColor" xmlns="http://www.w3.org/2000/svg">\n' +
                                '  <path fill-rule="evenodd" d="M12.146.146a.5.5 0 0 1 .708 0l3 3a.5.5 0 0 1 0 .708l-10 10a.5.5 0 0 1-.168.11l-5 2a.5.5 0 0 1-.65-.65l2-5a.5.5 0 0 1 .11-.168l10-10zM11.207 2.5L13.5 4.793 14.793 3.5 12.5 1.207 11.207 2.5zm1.586 3L10.5 3.207 4 9.707V10h.5a.5.5 0 0 1 .5.5v.5h.5a.5.5 0 0 1 .5.5v.5h.293l6.5-6.5zm-9.761 5.175l-.106.106-1.528 3.821 3.821-1.528.106-.106A.5.5 0 0 1 5 12.5V12h-.5a.5.5 0 0 1-.5-.5V11h-.5a.5.5 0 0 1-.468-.325z"/>\n' +
                                '</svg>&nbsp;',
                            action: 'edit'
                        },
                        delete: {
                            class: 'btn btn-sm btn-danger',
                            html: '<svg width="1em" height="1em" viewBox="0 0 16 16" class="bi bi-trash" fill="currentColor" xmlns="http://www.w3.org/2000/svg">\n' +
                                '  <path d="M5.5 5.5A.5.5 0 0 1 6 6v6a.5.5 0 0 1-1 0V6a.5.5 0 0 1 .5-.5zm2.5 0a.5.5 0 0 1 .5.5v6a.5.5 0 0 1-1 0V6a.5.5 0 0 1 .5-.5zm3 .5a.5.5 0 0 0-1 0v6a.5.5 0 0 0 1 0V6z"/>\n' +
                                '  <path fill-rule="evenodd" d="M14.5 3a1 1 0 0 1-1 1H13v9a2 2 0 0 1-2 2H5a2 2 0 0 1-2-2V4h-.5a1 1 0 0 1-1-1V2a1 1 0 0 1 1-1H6a1 1 0 0 1 1-1h2a1 1 0 0 1 1 1h3.5a1 1 0 0 1 1 1v1zM4.118 4L4 4.059V13a1 1 0 0 0 1 1h6a1 1 0 0 0 1-1V4.059L11.882 4H4.118zM2.5 3V2h11v1h-11z"/>\n' +
                                '</svg>&nbsp;',
                            action: 'delete'
                        },
                        save: {
                            class: 'btn btn-sm btn-success',
                            html: 'Save'
                        },
                        confirm: {
                            class: 'btn btn-sm btn-danger',
                            html: 'Confirm'
                        }
                    },
                    columns:{
                        identifier : [0, 'id'],
                        editable:[[1, 'ref'], [2, 'type'], [3, 'marque'], [4, 'salle_Id'], [5, 'dispo']]
                    },
                    restoreButton:false,
                    onSuccess:function(data, textStatus, jqXHR)
                    {
                        if(data.action == 'delete')
                        {
                            $('#' + data.id).remove();
                            $('#sample_data').DataTable().ajax.reload();
                        }
                    }
                });
            });

        });
    </script>

colin

The best way would be to use the Editor libraries. This blog post goes over what needs to be done, and there's an example for a join in there too,

Colin