To achieve the desired output, as shown in the image, where you have hierarchical data from three related tables, you will need to perform a series of SQL queries that join these tables based on their relationships. Then, you will have to loop through the results to construct an HTML table with the correct formatting.
Here’s a simplified approach to how you can do this in PHP, assuming you have the following table structure:
-
libraries (first column data)
-
collections (second column data)
-
sub_collections (third column data)
And each table has an id and parent_id column to establish the hierarchy, along with a name column.
Firstly, let’s create the SQL query. I will write a pseudo-SQL because the actual query may vary based on your exact database schema:
SELECT l.id as library_id, l.name as library_name,
c.id as collection_id, c.name as collection_name,
sc.id as sub_collection_id, sc.name as sub_collection_name
FROM libraries l
LEFT JOIN collections c ON l.id = c.parent_id
LEFT JOIN sub_collections sc ON c.id = sc.parent_id
WHERE l.name LIKE :search OR c.name LIKE :search OR sc.name LIKE :search
ORDER BY l.name, c.name, sc.name;
You would replace :search with the user’s search term, using a parameterized query to prevent SQL injection.
Next, you will need to execute this query and process the results in PHP. Here’s a basic structure for how you might loop through the results and build the HTML table:
// Assuming $results is the result set from the SQL query
$html = '<table>';
$currentLibraryId = null;
$currentCollectionId = null;
foreach ($results as $row) {
// Check if we're still within the same library
if ($currentLibraryId != $row['library_id']) {
$currentLibraryId = $row['library_id'];
$currentCollectionId = null; // Reset the current collection ID
$html .= '<tr>';
$html .= '<td>' . htmlspecialchars($row['library_name']) . '</td>';
$html .= '<td>' . htmlspecialchars($row['collection_name']) . '</td>';
$html .= '<td>' . htmlspecialchars($row['sub_collection_name']) . '</td>';
$html .= '</tr>';
} else {
// Check if we're still within the same collection
if ($currentCollectionId != $row['collection_id']) {
$currentCollectionId = $row['collection_id'];
$html .= '<tr>';
$html .= '<td></td>'; // Library name is the same, so leave this cell empty
$html .= '<td>' . htmlspecialchars($row['collection_name']) . '</td>';
$html .= '<td>' . htmlspecialchars($row['sub_collection_name']) . '</td>';
$html .= '</tr>';
} else {
// We're still within the same collection, so just the sub-collection changes
$html .= '<tr>';
$html .= '<td></td>'; // Library and Collection names are the same, so leave these cells empty
$html .= '<td></td>';
$html .= '<td>' . htmlspecialchars($row['sub_collection_name']) . '</td>';
$html .= '</tr>';
}
}
}
$html .= '</table>';
echo $html;
This script will create a new row each time the library or collection changes. If only the sub-collection changes, it will add the sub-collection to the same row.
Please note that this is a very basic example and assumes that $results is an array of results from the database with the structure indicated in the SQL query. Depending on your actual database structure and the libraries you use (e.g., PDO, mysqli), the actual PHP code may vary.
Lastly, for the search functionality, you would need to handle user input, sanitize it to prevent SQL injection, and pass it to the SQL query in place of :search. Make sure to use prepared statements for this to ensure the security of your application.
This approach also assumes that the ORDER BY clause in the SQL query will correctly sort the libraries, collections, and sub-collections in the desired hierarchical order. If the actual data is more complex, you might need to adjust the SQL query or the PHP processing accordingly.
