php - Mysql query which returns category tree -


I am working on category management. Where I have parents with each line categories can be n-level I can call the php function again, which will return to the tree tree array.

Enter image details here

Now the problem is: admin In the panel, I want the category list page as listed below.

  • Products> Products 1 (edit) (delete)
  • Products (edit) (delete)

    • / li>
    • Products > Product 2 (edit) (delete)
    • Products> Products 2> Products 2 1 (edit) (delete)
    • Products> Product 2> Product 2 2 (edit (Delete)
    • Contact us (edit) (delete)

    I would like mysql query results in the same order as shown above. I'm not sure how I can achieve it.

    SELECT * FROM tbl_categories ORDER BY ???

    Please guide.

    • Any is not a single SQL query which will bring you the results the way From you are expected on the basis of this table structure.

    There are two ways to solve this problem:

    1. Use external application logic to create recursive calls (outside DB) Find children and make the tree in the application.

    2. Use an algorithm for storage of trees In a relational database, the data is called an algorithm or just MPTT.

    Let's say that we use the column lft and rgt in the left / transcendent right indexed, when you enter a new Enter the category, then you will need:

    1. Get the basic category information from the ID: SELECT lft, rgt FROM tbl_categories WHERE categoryId = 5 example For one case, assume that in the parent category, lft = 7 and rgt = 10 (in this case it has a baby already)

    2. Make room for a new entry - Change all records to 2 (1 for 1 and 1 for RGT):

      UPDATE tbl_categories SET rgt = rgt + 2 WHERE rgt & gt; = 10 RGT DESC by order

      UPDATE tbl_categories SET lft = lft + 2 WHERE lft & gt; <10> LFT DESC by ORDER

    Note here ORDER as descending lft and Rgt is considered unique, they are advised to have a UNIQUE bound on them, and then the descending order in the update is required to prevent duplicate key errors.

    1. Set lft = & lt; Formerly original RGT & gt; and rgt = & lt; Formerly original RGT + 1> Enter and enter a new record ...

      INSERT SET categoryName = "new child" in TST category, parentCategoryId = 5, lft = 11, rgt = 12, .. .

      2. If you search for MPTT PHP MySQL , then you can find a more detailed example with code. There are some tutorials on this topic.


    Comments

    Post a Comment

    Popular posts from this blog

    Python SQLAlchemy：AttributeError: Neither 'Column' object nor 'Comparator' object has an attribute 'schema' -

    I tried to create a new database in my project, but when I run the script, I get this error, I have another project, this type of definition worked before, but now it gets a single error. I am using Python 2.7.8 and the version of SQLAlchemy module is 0.9.8. By the way, using a project flask- SQLAlchemy, it works well, I'm confused. Traceback information is as follows: Traceback (most recent call final): File "D: / Projects / IOM / db_create.py", line 4, & lt; Module & gt; From the models the base file "D: \ Projects \ OO-IM \ models.py", in line 15, in the & lt; Module & gt; Column ( 'followed_id', int (), ForeignKey ( 'user.id')) file "C: \ Python27 \ lib \ site-packages \ SQLAlchemy \ SQL \ schema.py", line 369, __new__ schema = metadata. Schema file "C: \ Python27 \ lib \ site-packages \ SQLAlchemy \ SQL \ elements.py", line 662, AttributeError in __getattr__ key): Neither 'column' object nor ...
    Read more

    java - How not to audit a join table and related entities using Hibernate Envers? -

    I use Eners to be hibernate to audit my institutions. I have an audit entity, Foo , which includes a list & lt; Bars & gt; However, I do not want to audit the bar institutions, in the form of properties, I have written that: @inti @edicated public class Foo {@JoinTable (name = "T_FOO_BAR", joinColumns = @JoinColumn (name = "FOO_ID"), inverseJoinColumns = @JoinColumn (Name = "BAR_ID")) @ManyToMany (Cascade = Persist) @ Indited (targetAuditMode = RelationTargetAuditMode.NOT_AUDITED ) Public listing & lt; Bars & gt; GetBars () {Return bars; }} Now, I want to retrieve an amendment of foo : AuditReader reader = AuditReaderFactory.get (GetEntityManager ()); FU modification = (FU) reader.CreativeTeA () .entensEtrevision (Foo Class, 42) .getSingleResult (); Unfortunately, when I want to retrieve all the data (i.e. when it loads lazy times ), error me ORA-00942: table or view is not present , because Tried to query this: Sel...
    Read more

    mongodb - CakePHP paginator ignoring order, but only for certain values -

    While pagging my user model in kppp, I can sort with some values, but not others. For example, I can order results from created or email , for example, but username or reputation seems to come back order by arbitrary command for example with a list of users: $ this-> paginate = array ('condition' => array ( 'User.is_active' = & gt; true), 'border' => 24, 'order' => array ('User.created' => DESC)); Works as expected, but $ this - & gt; Paged = array ('conditions' = & gt; array (' user.isIactive '= & gt; true),' border '= & gt; 24,' command '= & gt; array (' user repeats' = ' & Gt; DESC ')); No, no. I first thought that this could be a database issue, but when I search the database directly, sorted as expected. Note: I am using Mangodebi with the Mongo DB plugin of Ichikawa for the KPPHP; Pagnetizing users are used to work properl...
    Read more