获取MySQL表中的各个元素

I'm attempting to add Breadcrumbs to my website using a MySQL table, and I'm having difficulty at the moment.

我正在尝试使用MySQL表向我的网站添加面包屑，目前我遇到了困难。

I have a table named 'includes' created that stores information about the category, page, subpage, the title, and the ref (url) of the page. Category, Page, and Subpage are all php parameters passed from the page the user is on

我创建了一个名为“include”的表，用于存储关于页面的类别、页面、子页面、标题和ref (url)的信息。类别、页面和子页面都是用户所在页面传递的php参数

My table is laid out like this:

我的桌子是这样摆放的:

|----------------------------------------------------------------|
| ID |  Category  |  Page   |  Subpage  |  Title           | Ref |
|----------------------------------------------------------------|
| 0  |            |         |           | Home             | ... |
| 1  | Software   |         |           | Software         | ... |
| 2  | Software   | Desktop |           | Desktop Software | ... |
| 3  | Software   | Mobile  |           | Mobile Software  | ... |
| 4  | Software   | Desktop | Blah      | Blah Blah        | ... |
| ...
|----------------------------------------------------------------|

What I'm trying to do is make a query that will return only the required steps back to home for the breadcrumbs.

我要做的是做一个查询，只返回需要的步骤返回到home的面包屑。

In other words, if the user is on "example.com/software/desktop/blah", the query will return rows 0,1,2, and 4. Or if I was on /software/mobile, it would only return rows 0,1, and 3.

换句话说，如果用户在example.com/software/desktop/blah"上，查询将返回0、1、2和4行。或者如果我是on /software/mobile，它只返回0、1和3行。

My current attempts have been things like the following:

我目前的尝试如下:

SELECT * FROM `includes` WHERE
`category` IS NULL     AND `page` IS NULL AND `subpage` IS NULL OR
`category`='$category' AND `page` IS NULL AND `subpage` IS NULL OR
`category`='$category' AND `page`='$page' AND `subpage` IS NULL OR
`category`='$category' AND `page`='$page' AND `subpage`='$subpage'

Which not only don't work, but also seem more complex than it should have to be.

这不仅不起作用，而且看起来比应该要复杂得多。

I'm probably overcomplicating this, or possibly just doing an entirely wrong method, which is why I've turned here.

我可能把它弄得太复杂了，或者可能是做了一个完全错误的方法，这就是我在这里转到这里的原因。

Does anyone have a possible solution to this? Should I be looking at a more complex query? (admittedly, SQL is not my forte) Or should I be looking at a new SQL table, or possibly an entirely different method?

有人有可能解决这个问题吗?我应该查看更复杂的查询吗?(必须承认，SQL不是我的强项)或者我应该研究一个新的SQL表，或者可能是一个完全不同的方法?

2 个解决方案

#1

What you have is a hierarchical structure. The data is set up with parent-child relationships. There is a good description on how to work with hierarchical data here: http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/

你拥有的是一个层次结构。数据是用父子关系建立的。这里有一个关于如何处理分层数据的很好的描述:http://explainextended.com/2009/03/17/hierarchical-queries-in mysql/

#2

You can make a self relation table like this

你可以这样做一个自我关系表

id | parent_it | title | Ref 

1  | 0         | Home  | ... 
2  | 1         | Software  | ... 
3  | 2         | Desktop  | ... 
4  | 2         | Mobile  | ... 
5  | 3         | Blah  | ...

So your query should get the last element SELECT * FROM includes WHERE tilte = 'Blah'

所以你的查询应该得到最后一个元素SELECT * FROM WHERE tilte = 'Blah'

And then get the parent ID title and so on , like this the table structure will be better from my point of view & experience

然后得到父ID名等等，像这样的表结构从我的角度和经验来看会更好

或

Generate your query based on the values you get , with simple loop count the arguments and based on that generate the query string then execute it

根据得到的值生成查询，使用简单的循环计数参数，并基于此生成查询字符串，然后执行它

I hope this can help :)

我希望这能有所帮助。

#1

#2