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

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/[email protected]/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 >
        <h2 style="margin-bottom: 30px;">jQuery Datatable</h2>
        <table id="jquery-datatable-ajax-php"  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);

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");

CodePudding user response：

Hope this helps :)

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

It will get the all values of clients and quotes table. if you want to get some fields of client and quotes you can use Select quotes.<field_name>,clients.<field_name> .....

CodePudding user response：

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

and display it in array :

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