Resolve name by ID in from 2 tables in PHP / SQL in Datatable

I am currently working on my first datatable which I managed to get to work and I am quite happy with it, however I am having some difficulties in resolving client names. Here is my current code:

Tablefile.html:

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>JQuery Datatable</title>
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.3/css/jquery.dataTables.min.css">
    <script type="text/javascript" src="https://code.jquery.com/jquery-3.5.1.js"></script>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
    <script type="text/javascript" src="https://cdn.datatables.net/1.11.3/js/jquery.dataTables.min.js"></script>
    <script type="text/javascript">
        $(document).ready(function() {
            $('#jquery-datatable-ajax-php').DataTable({
                  'processing': true,
                  'serverSide': true,
                  'serverMethod': 'post',
                  'order': [[0, 'desc']],
                  'ajax': {
                      'url':'datatable.php'
                  },
                  'columns': [
                    { data: 'id', 'name': 'id', fnCreatedCell: function (nTd, sData, oData, iRow, iCol) {$(nTd).html("<a href='/quotes/view/"+oData.id+"'>"+oData.id+"</a>");}},
                    { data: 'client_id' },
                    { data: 'quote_number' },
                    { data: 'project' },
                    { data: 'quote_total', render: $.fn.dataTable.render.number(',', '.', 2, '$') }
                  ]
           });
        } );
    </script>
</head>
<body>
    <div class="container mt-5">
        <h2 style="margin-bottom: 30px;">jQuery Datatable</h2>
        <table id="jquery-datatable-ajax-php" class="display" style="width:100%">
            <thead>
                <tr>
                    <th>ID</th>
                    <th>Client Name</th>
                    <th>Quote Number</th>
                    <th>Project Name</th>
                    <th data-orderable="false">Price</th>
                </tr>
            </thead>
        </table>
    </div>
</body>
</html>

JavaScript
​x
 
<!DOCTYPE html><html><head>    <meta charset="utf-8">    <title>JQuery Datatable</title>    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.3/css/jquery.dataTables.min.css">    <script type="text/javascript" src="https://code.jquery.com/jquery-3.5.1.js"></script>    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">    <script type="text/javascript" src="https://cdn.datatables.net/1.11.3/js/jquery.dataTables.min.js"></script>    <script type="text/javascript">        $(document).ready(function() {            $('#jquery-datatable-ajax-php').DataTable({                  'processing': true,                  'serverSide': true,                  'serverMethod': 'post',                  'order': [[0, 'desc']],                  'ajax': {                      'url':'datatable.php'                  },                  'columns': [                    { data: 'id', 'name': 'id', fnCreatedCell: function (nTd, sData, oData, iRow, iCol) {$(nTd).html("<a href='/quotes/view/"+oData.id+"'>"+oData.id+"</a>");}},                    { data: 'client_id' },                    { data: 'quote_number' },                    { data: 'project' },                    { data: 'quote_total', render: $.fn.dataTable.render.number(',', '.', 2, '$') }                  ]           });        } );    </script></head><body>    <div class="container mt-5">        <h2 style="margin-bottom: 30px;">jQuery Datatable</h2>        <table id="jquery-datatable-ajax-php" class="display" style="width:100%">            <thead>                <tr>                    <th>ID</th>                    <th>Client Name</th>                    <th>Quote Number</th>                    <th>Project Name</th>                    <th data-orderable="false">Price</th>                </tr>            </thead>        </table>    </div></body></html>​

And here my datatable.php file:

<?php
   include 'connection.php';
   $draw = $_POST['draw'];
   $row = $_POST['start'];
   $rowperpage = $_POST['length']; // Rows display per page
   $columnIndex = $_POST['order'][0]['column']; // Column index
   $columnName = $_POST['columns'][$columnIndex]['data']; // Column name
   $columnSortOrder = $_POST['order'][0]['dir']; // asc or desc
   $searchValue = $_POST['search']['value']; // Search value
   $searchArray = array();
   // Search
   $searchQuery = " ";
   if($searchValue != ''){
      $searchQuery = " AND (quote_number LIKE :quote_number OR project LIKE :project OR quote_total LIKE :quote_total ) ";
      $searchArray = array( 
           'quote_number'=>"%$searchValue%",
           'project'=>"%$searchValue%",
           'quote_total'=>"%$searchValue%"
      );
   }
   // Total number of records without filtering
   $stmt = $conn->prepare("SELECT COUNT(*) AS allcount FROM quotes ");
   $stmt->execute();
   $records = $stmt->fetch();
   $totalRecords = $records['allcount'];
   // Total number of records with filtering
   $stmt = $conn->prepare("SELECT COUNT(*) AS allcount FROM quotes WHERE 1 ".$searchQuery);
   $stmt->execute($searchArray);
   $records = $stmt->fetch();
   $totalRecordwithFilter = $records['allcount'];
   // Fetch records
   $stmt = $conn->prepare("SELECT * FROM quotes WHERE 1 ".$searchQuery." ORDER BY ".$columnName." ".$columnSortOrder." LIMIT :limit,:offset");
   // Bind values
   foreach ($searchArray as $key=>$search) {
      $stmt->bindValue(':'.$key, $search,PDO::PARAM_STR);
   }
   $stmt->bindValue(':limit', (int)$row, PDO::PARAM_INT);
   $stmt->bindValue(':offset', (int)$rowperpage, PDO::PARAM_INT);
   $stmt->execute();
   $empRecords = $stmt->fetchAll();
   $data = array();
   foreach ($empRecords as $row) {
      $data[] = array(
         "id"=>$row['id'],
         "client_id"=>$row['client_id'],
         "quote_number"=>$row['quote_number'],
         "project"=>$row['project'],
         "quote_total"=>$row['quote_total']
      );
   }
   // Response
   $response = array(
      "draw" => intval($draw),
      "iTotalRecords" => $totalRecords,
      "iTotalDisplayRecords" => $totalRecordwithFilter,
      "aaData" => $data
   );
   echo json_encode($response);

JavaScript
 
<?php   include 'connection.php';   $draw = $_POST['draw'];   $row = $_POST['start'];   $rowperpage = $_POST['length']; // Rows display per page   $columnIndex = $_POST['order'][0]['column']; // Column index   $columnName = $_POST['columns'][$columnIndex]['data']; // Column name   $columnSortOrder = $_POST['order'][0]['dir']; // asc or desc   $searchValue = $_POST['search']['value']; // Search value   $searchArray = array();   // Search   $searchQuery = " ";   if($searchValue != ''){      $searchQuery = " AND (quote_number LIKE :quote_number OR project LIKE :project OR quote_total LIKE :quote_total ) ";      $searchArray = array(            'quote_number'=>"%$searchValue%",           'project'=>"%$searchValue%",           'quote_total'=>"%$searchValue%"      );   }   // Total number of records without filtering   $stmt = $conn->prepare("SELECT COUNT(*) AS allcount FROM quotes ");   $stmt->execute();   $records = $stmt->fetch();   $totalRecords = $records['allcount'];   // Total number of records with filtering   $stmt = $conn->prepare("SELECT COUNT(*) AS allcount FROM quotes WHERE 1 ".$searchQuery);   $stmt->execute($searchArray);   $records = $stmt->fetch();   $totalRecordwithFilter = $records['allcount'];   // Fetch records   $stmt = $conn->prepare("SELECT * FROM quotes WHERE 1 ".$searchQuery." ORDER BY ".$columnName." ".$columnSortOrder." LIMIT :limit,:offset");   // Bind values   foreach ($searchArray as $key=>$search) {      $stmt->bindValue(':'.$key, $search,PDO::PARAM_STR);   }   $stmt->bindValue(':limit', (int)$row, PDO::PARAM_INT);   $stmt->bindValue(':offset', (int)$rowperpage, PDO::PARAM_INT);   $stmt->execute();   $empRecords = $stmt->fetchAll();   $data = array();   foreach ($empRecords as $row) {      $data[] = array(         "id"=>$row['id'],         "client_id"=>$row['client_id'],         "quote_number"=>$row['quote_number'],         "project"=>$row['project'],         "quote_total"=>$row['quote_total']      );   }   // Response   $response = array(      "draw" => intval($draw),      "iTotalRecords" => $totalRecords,      "iTotalDisplayRecords" => $totalRecordwithFilter,      "aaData" => $data   );   echo json_encode($response);​

The table renders fine and works great, however client_id is obviously just outputting their related ID’s, e.g. 5. How could I convert those client_id’s to the actual client names? I understand that I am currently just fetching from a table called quotes, however the client names (client_name) associated to the client_id’s are in another table called clients and I can’t figure out how to have them combined, I’ve read something about LEFT JOIN but I am not too familiar with SQL commands.

Screenshot of clients table:

Some expert help would be greatly appreciated, thank you very much in advance.

EDIT:

I finally got this to work, thanks with the help of MR. @malik-ilman

    $stmt = $conn->prepare("SELECT quotes.*, clients.client_name FROM quotes INNER JOIN clients ON quotes.client_id = clients.id WHERE 1 ".$searchQuery." ORDER BY ".$columnName." ".$columnSortOrder." LIMIT :limit,:offset");

JavaScript
 
    $stmt = $conn->prepare("SELECT quotes.*, clients.client_name FROM quotes INNER JOIN clients ON quotes.client_id = clients.id WHERE 1 ".$searchQuery." ORDER BY ".$columnName." ".$columnSortOrder." LIMIT :limit,:offset");​

Answer

Create Query like this :

SELECT quotes.*, client.client_name FROM quotes INNER JOIN client ON quotes.client_id = client.client_id WHERE 1 ".$searchQuery." ORDER BY ".$columnName." ".$columnSortOrder." LIMIT :limit,:offset

JavaScript
 
SELECT quotes.*, client.client_name FROM quotes INNER JOIN client ON quotes.client_id = client.client_id WHERE 1 ".$searchQuery." ORDER BY ".$columnName." ".$columnSortOrder." LIMIT :limit,:offset​

and display it in array :

"Client_name"=>$row['client_name']

JavaScript
 
"Client_name"=>$row['client_name']​

Advertisement

Answer