Explode inside SQL query?

Mr_Mako2

I'm working on a page where I plan on having "categories", with "articles" written under each category. This would be easy if there was only one level of articles... the problem is that I will end up having multiple levels of articles. For example, I may have this :

Geography
|-Countries
  |- Cites
Economies
|-Country Economics

The problem lay with the nested articles.

My plan was to have the articles, when created, have the ID's of each category and article they were a part of, and IMPLODE them separated by commas - like so :

Categories
id     Name
----------------------
1      Geography
2      Economies

Articles
id     Name            Category/Article ID
------------------------------------------
1      Countries           1,0
2      Cities              1,2
3      Country Economics   2,0

Then I could simply explode them after a query and list my articles under my categories.

However... the problem came when I started working on the SQL query to find which articles should go with the given category.

What I need to do is construct a query that will - using the example above - find the 1st number in the "Category/Article ID" field... something like this :

$sql = "SELECT * FROM articles WHERE category/articleID = '[1st number in field]'";

I don't know if this is possible... and frankly, I'm not even sure if this is the best method for creating this type of structure. I'd appreciate any help and suggestions as to how to resolve this.

Thanks.

tomhath

There's no reason to do that, and lots of reasons to not do it, including the problem you ran into.

Just create another table with two columns, articleId and categoryId. Insert a row for each combination as needed and join or search using normal sql queries.

Roger_Ramjet

Definitely not the way to do this. Here is what MySQL have to say about it, including all the queries you will need storing hierarchicaldata. Another article including the php http://articles.sitepoint.com/article/hierarchical-data-database

A discussion of altenatives http://carsonified.com/blog/dev/should-you-go-beyond-relational-databases/

And another http://www.phpriot.com/articles/nested-trees-1