How can hierarchical data be efficiently stored in a relational database using PHP?

Hierarchical data can be efficiently stored in a relational database using PHP by utilizing the adjacency list model or the nested set model. The adjacency list model involves storing each node with a reference to its parent node. The nested set model involves storing each node with additional left and right values to represent its position within the hierarchy. 

// Example of storing hierarchical data using the adjacency list model in a relational database

// Connect to the database
$pdo = new PDO('mysql:host=localhost;dbname=mydatabase', 'username', 'password');

// Insert a new node with parent_id
$parent_id = 1; // ID of the parent node
$node_name = 'New Node';
$stmt = $pdo->prepare("INSERT INTO nodes (parent_id, node_name) VALUES (:parent_id, :node_name)");
$stmt->bindParam(':parent_id', $parent_id);
$stmt->bindParam(':node_name', $node_name);
$stmt->execute();

// Retrieve all children of a node
$parent_id = 1; // ID of the parent node
$stmt = $pdo->prepare("SELECT * FROM nodes WHERE parent_id = :parent_id");
$stmt->bindParam(':parent_id', $parent_id);
$stmt->execute();
$children = $stmt->fetchAll(PDO::FETCH_ASSOC);

Keywords

hierarchical data relational database efficient storage PHP data structure

Related Questions