Search DataTable

PHP
1

Hi,
Please help me for searching.

If the search result pertains to employee information, what can I do to display the branch information they belong to on the first column of the table and the employee information on the next column?

<?php
require_once '../includes/db_connection.php';

if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_POST['tab_id'])) {
    $tabId = intval($_POST['tab_id']);
    $searchText = isset($_POST['search_text']) ? $_POST['search_text'] : '';

    // Filial məlumatlarını və işçiləri veritabanından gətirin
    $sql = "SELECT b.id AS branch_id, b.branch_name, b.address AS branch_address, b.email AS branch_email, 
                   e.employee_name, e.position, c.company_name, e.internal_phone, e.mobile_phone, e.email 
            FROM branches b
            LEFT JOIN branch_employees e ON b.id = e.branch_id
            LEFT JOIN companies c ON e.company_id = c.id
            WHERE e.tab_id = $tabId 
              AND (b.branch_name LIKE '%$searchText%' OR b.address LIKE '%$searchText%' OR b.email LIKE '%$searchText%'
                   OR e.employee_name LIKE '%$searchText%' OR e.position LIKE '%$searchText%' OR c.company_name LIKE '%$searchText%')
            ORDER BY b.id, e.employee_name";
    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
        $branchData = [];

        // Məlumatları qruplaşdırın
        while ($row = $result->fetch_assoc()) {
            $branchId = $row['branch_id'];
            if (!isset($branchData[$branchId])) {
                $branchData[$branchId] = [
                    'branch_name' => $row['branch_name'],
                    'branch_address' => $row['branch_address'],
                    'branch_email' => $row['branch_email'],
                    'employees' => []
                ];
            }

            if ($row['employee_name']) {
                $branchData[$branchId]['employees'][] = [
                    'employee_name' => $row['employee_name'],
                    'position' => $row['position'],
                    'company_name' => $row['company_name'],
                    'internal_phone' => $row['internal_phone'],
                    'mobile_phone' => $row['mobile_phone'],
                    'email' => $row['email']
                ];
            }
        }

        // Cədvəl quruluşunu göstərin
        echo '<div class="table-responsive">';
        echo '<table id="employeeTable_' . $tabId . '" class="table table-striped table-bordered">';
        echo '<thead class="thead-dark">';
        echo '<tr>';
        echo '<th>Ad</th>';
        echo '<th>Ünvan / Şirkət</th>';
        echo '<th>Vəzifə</th>';
        echo '<th>Daxili Telefon</th>';
        echo '<th>Mobil Telefon</th>';
        echo '<th>Email</th>';
        echo '</tr>';
        echo '</thead>';
        echo '<tbody>';

        // Filial məlumatlarını və alt qrup işçiləri göstərin
        foreach ($branchData as $branchId => $branch) {
            if (!empty($branch['employees'])) {
                foreach ($branch['employees'] as $employee) {
                    // Filial məlumatları
                    echo '<tr>';
                    echo '<td><strong>' . htmlspecialchars($branch['branch_name']) . '</strong></td>';
                    echo '<td><strong>' . htmlspecialchars($branch['branch_address']) . '</strong></td>';
                    echo '<td></td>';
                    echo '<td></td>';
                    echo '<td></td>';
                    echo '<td><strong>' . htmlspecialchars($branch['branch_email']) . '</strong></td>';
                    echo '</tr>';

                    // İşçi məlumatları
                    echo '<tr>';
                    echo '<td>' . htmlspecialchars($employee['employee_name']) . '</td>';
                    echo '<td>' . htmlspecialchars($employee['company_name']) . '</td>';
                    echo '<td>' . htmlspecialchars($employee['position']) . '</td>';
                    echo '<td>' . htmlspecialchars($employee['internal_phone']) . '</td>';
                    echo '<td>' . htmlspecialchars($employee['mobile_phone']) . '</td>';
                    echo '<td>' . htmlspecialchars($employee['email']) . '</td>';
                    echo '</tr>';
                }
            } else {
                echo '<tr>';
                echo '<td colspan="6" class="text-muted">Heç bir işçi tapılmadı.</td>';
                echo '</tr>';
            }
        }

        echo '</tbody>';
        echo '</table>';
        echo '</div>';
    } else {
        echo '<p class="text-muted">Heç bir məlumat tapılmadı.</p>';
    }

    $conn->close();
} else {
    echo '<p class="text-muted">Xəta: POST məlumatları alınmadı.</p>';
}
?>
2

You would need to show some example data and show what result you want from that data.

Edit: also, when there are no employees found at a branch, what result do you want?

3

Also some definitional cleanup… “display the branch information they belong to on the first column of the table and the employee information on the next column”

Presumably you mean branch name in the first column, however “employee information on the next column”… the table you’ve given us has 6 columns, all of which currently contain employee information…

4

Wouldn’t be something like this assuming it’s ONE column for all branch info, then employees in the next 6 columns.? I don’t have data to test.

echo '<div class="table-responsive">';
	echo '<table id="employeeTable_' . $tabId . '" class="table table-striped table-bordered">';
		echo '<thead class="thead-dark">';
			echo '<tr>';
				echo '<th>Branch</th>';
				echo '<th>Ad</th>';
				echo '<th>Ünvan / &#350;irk&#601;t</th>';
				echo '<th>V&#601;zif&#601;</th>';
				echo '<th>Daxili Telefon</th>';
				echo '<th>Mobil Telefon</th>';
				echo '<th>Email</th>';
			echo '</tr>';
		echo '</thead>';
		echo '<tbody>';
		
			// Filial m&#601;lumatlar&#305;n&#305; v&#601; alt qrup i&#351;çil&#601;ri göst&#601;rin
			foreach ($branchData as $branchId => $branch) {
				if (!empty($branch['employees'])) {	
					$employeecnt = count($branch['employees']);
					foreach ($branch['employees'] as $k => $employee) {	
						if($k == 0){
							echo '<tr>';
								echo '<td rowspan="'.$employeecnt.'"><strong>
								' . htmlspecialchars($branch['branch_name']) . '<br />
								' . htmlspecialchars($branch['branch_address']) . '<br />
								' . htmlspecialchars($branch['branch_email']) . '
								</strong></td>';
								
								echo '<td>' . htmlspecialchars($employee['employee_name']) . '</td>';
								echo '<td>' . htmlspecialchars($employee['company_name']) . '</td>';
								echo '<td>' . htmlspecialchars($employee['position']) . '</td>';
								echo '<td>' . htmlspecialchars($employee['internal_phone']) . '</td>';
								echo '<td>' . htmlspecialchars($employee['mobile_phone']) . '</td>';
								echo '<td>' . htmlspecialchars($employee['email']) . '</td>';
							echo '</tr>';
						}else{
							echo '<tr>';
								echo '<td>' . htmlspecialchars($employee['employee_name']) . '</td>';
								echo '<td>' . htmlspecialchars($employee['company_name']) . '</td>';
								echo '<td>' . htmlspecialchars($employee['position']) . '</td>';
								echo '<td>' . htmlspecialchars($employee['internal_phone']) . '</td>';
								echo '<td>' . htmlspecialchars($employee['mobile_phone']) . '</td>';
								echo '<td>' . htmlspecialchars($employee['email']) . '</td>';
							echo '</tr>';
						}
					}
				} else {
					echo '<tr>';
						echo '<td colspan="6" class="text-muted">Heç bir i&#351;çi tap&#305;lmad&#305;.</td>';
					echo '</tr>';
				}
			}
		
		echo '</tbody>';
	echo '</table>';
echo '</div>';