DataTable Search Panel

hadjieff

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 row of the table and the employee information on the next row?
For exam./ pic. Search result 7700 , but in this case only employee information is shown.

Code

<?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 '<style>';
        echo 'a.email-link { color: black; text-decoration: none; }';  // Email linkləri üçün stil
        echo '</style>';
        
        echo '<div class="table-responsive">';
        echo '<table id="employeeTable_' . $tabId . '" class="table table-hover table-bordered table-sortable table-float">';
        echo '<thead>';
        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>';

        // İşçi məlumatlarını göstərin və onların filial məlumatlarını əlavə edin
        $previousBranchId = null;

        foreach ($branchData as $branchId => $branch) {
            // İşçi məlumatlarını göstər
            if (!empty($branch['employees'])) {
                foreach ($branch['employees'] as $employee) {
                    // Filial məlumatlarını birinci dəfə göstərmək üçün
                    if ($previousBranchId !== $branchId) {
                        echo '<tr class="searchable" data-id="' . $branchId . '" data-index="' . $branch['branch_name'] . ' ' . $branch['branch_address'] . ' ' . mb_strtolower($branch['branch_name'] . ' ' . $branch['branch_address'] . ' ' . $branch['branch_email']) . '">';
                        echo '<td class="name"><strong>' . htmlspecialchars($branch['branch_name']) . '</strong></td>';
                        echo '<td class="position"><strong>' . htmlspecialchars($branch['branch_address']) . '</strong></td>';
                        echo '<td></td>';
                        echo '<td></td>';
                        echo '<td></td>';
                        echo '<td class="email"><strong><a class="email-link" href="mailto:' . htmlspecialchars($branch['branch_email']) . '">' . htmlspecialchars($branch['branch_email']) . '</a></strong></td>';
                        echo '</tr>';
                        $previousBranchId = $branchId;
                    }

                    echo '<tr class="searchable" data-id="' . $employee['employee_name'] . '" data-index="' . $employee['employee_name'] . ' ' . $employee['position'] . ' ' . $employee['company_name'] . ' ' . $employee['internal_phone'] . ' ' . $employee['mobile_phone'] . ' ' . mb_strtolower($employee['employee_name'] . ' ' . $employee['position'] . ' ' . $employee['company_name'] . ' ' . $employee['internal_phone'] . ' ' . $employee['mobile_phone'] . ' ' . $employee['email']) . '">';
                    echo '<td class="name">' . htmlspecialchars($employee['employee_name']) . '</td>';
                    echo '<td class="position">' . htmlspecialchars($employee['company_name']) . '</td>';
                    echo '<td class="position">' . htmlspecialchars($employee['position']) . '</td>';
                    echo '<td class="mobile">' . htmlspecialchars($employee['internal_phone']) . '</td>';
                    echo '<td class="mobile">' . htmlspecialchars($employee['mobile_phone']) . '</td>';
                    echo '<td class="email"><a class="email-link" href="mailto:' . htmlspecialchars($employee['email']) . '">' . htmlspecialchars($employee['email']) . '</a></td>';
                    echo '</tr>';
                }
            } else {
                // Heç bir işçi tapılmadı mesajını göstər
                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>';
}

<?php
>
?>

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

I'm not actually sure what you are looking for I'm afraid - could you elaborate further? I'm also not seeing any DataTables code in your code block above? If it is general HTML / SQL / PHP help you need, you'd be better asking on StackOverflow.

Allan