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

问题描述：

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.

enter image description here

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?

我正在尝试将一些网站链接保存到数据库表中，然后使用PHP将它们打印到屏幕上。 p >

我需要帮助的部分是一些链接记录将作为parent code>记录，然后 child code>记录将在其下; s parent。 p>

目标是生成类似下图的内容，但是使用PHP从MySQL数据库中提取链接记录。 p>


我不确定 如何最好地为此构建数据库。 作为一个开始，我添加了一个名为 parent  code>的列，其中我认为每个链接记录可以具有 Parent  code>记录的 ID  code>。  p> 
 
 
父记录将充当文件夹/目录。 p> 
 
 

  p> 
 
 
我的Databse结构现在...  p> 
 
 
  id 
title \  ndescription 
url 
permission 
notes 
active 
sort_order 
parent（将具有此链接的父文件夹/链接的ID号）
  code>  pre> 
 
 基于此， 我如何用PHP拉取记录并确保链接保留在图像中的父链接记录下？ p> 
 
 
我的数据库是否需要更改才能执行此操作？ p  > 
  div>

答

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

答

Assuming that the parent has a smaller id than any of its children and that the default value of the parent column is null, could you order them this way:

(select ...., concat(concat(id,'_'),id) as parent_id from website_links where parent IS NULL
union
select  ...., concat(concat(parent,'_'),id) as parent_id from website_links where parent IS NOT NULL) 
order by parent_id;

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

相关推荐