All geek questions in one place

Select and view categories and subcategories stored in separate tables

I am looking for a solution to browse and view categories and subcategories and their entries (ads) when you save category levels in separate tables. I used to work with the adjacency model, but now I have to stick with this database setup, and it is new to me. I am using php and mysql.

The site is a classified advertising site structured in the usual way: it has a list of the main categories on its home page, when you click one of the links in a category, then only its subcategories and ads belonging to this category are listed, and therefore at each level.

I am a little confused in the following areas:

  • How do you build a category link when viewing a category so that the script knows which table it should select the categories in if I look at the mysql structure below? Do I need separate parameters for each level of access I have for example, "mysite.com/?cat2=4" when accessing the category "4" in cat2 tables and "mysite.com/?cat3=9" when accessing the category "9" in cat3 table to categorize levels? If a separate parameter is not necessary, then how php and mysql tell me which table you should select categories from?

  • And most important in this case, what is the best way to build SEO friendly links? And how will mysql know which table to select categories from? I would like to use the most possibly simple solution like: mysite.com/electronics/television/sony. As far as I know, I should include at least cat_id somewhere in the link ... where will I put it? and should I include the number of levels? to complicate it more with the category of names in a foreign language with accented characters (although I created a function that changes accented characters to Latin on the fly when creating a link category), so I think it's best to select them by their identifiers.

  • What does mysql select look like, how does it select a child category of a certain category?

  • How can I build a breading navigation?

MYSQL STRUCTURE:

  • "cat1" ( ):

    cat1_id | cat1_name

  • "cat2" ():

    cat2_id | cat1_id | cat2_name

  • "cat3" (subsubcategory):

    cat3_id | cat2_id | cat3_name

  • "ads":

    ad_id | cat1_id | cat2_id | cat3_id | ad_title | ad_description

.

+3
1

URL- ( ):

http://mysite.com/articles/brains+zombies+legs+frogs

+ ( SEO). , , .

SQL, 2 - :

Categories (id, name, description)

CategoryRelationships (catID, thingID)

CategoryRelationships. :

SELECT * FROM Things t
JOIN CategoryRelationships ON thingID = t.ID
JOIN Categories c ON catID = c.CatID

, .

, . :

  • .

, , . URL- foreach :

http://mysite.com/people/zombies/brains/brains

URI :

people, zombies, brains, brains

.

+1

Source: https://habr.com/ru/post/1786472/

More articles:


All Articles