如何构建MySQL数据库以在父目录类型结构下显示PHP中的链接记录？

Question

I am trying to save some Website Links into a Database table and then print them to screen with PHP.

The part I need help with is that some of the Link records will act as parent records and then child records will be under it;s parent.

The goal is to generate something like the image below but pulling the link records from a MySQL Database with PHP.

I am not sure how best to structure the database for this. AS a start I have added a column called parent in which I figured that each link record could have the ID of the Parent record.

A Parent record will act as a Folder/Directory.

My Databse structure as of right now...

id
title
description
url
permission
notes
active
sort_order
parent (will have the ID number of the parent folder/link of this link)

Based on this, how could I pull the records with PHP and make sure that the links stay under the parent link records like in the image?

Does my Database need to be changed to do this?

Answer 1

Actually, you database is perfect, you'll only have to work on how retrieve those informations in PHP.

I can give you a little hint, you'll have to use a recursive function, like this one :

function arrayLinks($parent) {
  // Your SQL query for retrieving all links with $parent
  $query = query("SELECT * FROM link WHERE parent = ".$parent);

  // For each row, just return another arrayLinks function
  $arrayLinks = array();
  foreach($query as $row) {
    $arrayLinks[] = array(
       'row' => $row,
       'child' => arrayLinks($row['id'])
    );
  }
  return $arrayLinks;
}

$arrayLinks = arrayLinks(0);

This will give you an array like this one :

[{
  row: {
    id: 1,
    title: 'Link 1'
  },
  child: [{
   row: {
     id: 3,
     title: 'Link 1-1'
   },
   child: []
  }, {
   row: {
    id: 4,
    title: 'Link 1-2'
   }
  }]
}, {
  row: {
    id: 2,
    title: 'Link 2'
  },
  child: []
}]

In this exemple, you have those links :

• Link 1
  • Link 1-1
  • Link 1-2
• Link 2