Jump to content

Get all details of child with relation of its parents in php mysql


shashib
 Share

Recommended Posts

Cat and sub category Table (cat_tbl) :

id | cat_n | parent_id1  | cat   | 02  | dog   | 03  | tiger | 24  | lion  | 05  | abc   | 06  | bcd   | 3

Now i have a product table as below (prod_tbl) :

id | pwght | cid | cpid10 | 1.2   | 1   | 011 | 2.4   | 2   | 012 | 3.4   | 2   | 013 | 4.5   | 6   | 3

and user final weight product table is below (userprod_tbl)

id | pwght | cid | cpid | prod_id ( is above prod_tbl primary id )1 | 1.1    | 1   | 0    | 102 | 2.3    | 2   | 0    | 113 | 3.1    | 3   | 2    | 124 | 4.0    | 6   | 3    | 13

**RESULT : ( OUTPUT WHICH I WANT ) IS comparison of prod_tbl with userprod_tbl as below :**

Prod tbl                                   Userprod tblcat 1.2                                    cat          1.1dog 2.4                                    dog -- --    2.3dog 3.4                                    dog tiger -- 3.1dog 4.5                                    dog tiger bcd 4.0

Hence in above result 2.4,3.4,4.5 are belong to Parent 2**But i am getting as below**

Prod tbl                    Userprod tblcat 1.2                      cat 1.1dog 2.4                      dog -- -- 2.3dog 3.4                      dog tiger -- 3.1

here i am not getting 4.5 value as 4.5 has 6,3 relation from above prod table but its parent of 2**Below is my query which i have return:**

SELECT pt.pwght , upt.pwght ,ct.cat_n,uct.cat_n,umct.cat_nFROM prod_tbl AS ptLEFT JOIN userprod_tbl AS upt ON (pt.id = upt.prod_id)LEFT JOIN cat_tbl AS ct ON pt.packet_id = ct.idLEFT JOIN cat_tbl AS uct ON upt.packet_id = uct.idLEFT JOIN cat_tbl AS umct ON upt.parent_packet_id = umct.id

Please let me know what is missingThanks

Edited by shashib
Link to comment
Share on other sites

You shouldn't store the parent ID in more than one table, it should only be in the category table. Don't duplicate data like that, if you change the parent ID you only want to change it in 1 place. The product tables should only have the category for the product, they shouldn't list the parent also. You can get the parent from the other table.

Link to comment
Share on other sites

@ justsomeguy sir, can you tell me with example with above as a example .

 

i need to store cat id in product and user product table as i need to manitain the forigen relation with cat and prod,userprod table .... hence i have put cat id in both tables .... please guide me so that in my next phase of my s/w i can improve this better to get result ...

 

as now if above query i am get only parent and its single child ..... not n level childs

 

pls help me

Link to comment
Share on other sites

It's fine to store the category ID as a foreign key. What you should avoid doing is storing both the category ID and the parent category ID in more than one table. You already have that relationship defined in the categories table, don't duplicate it in the products table. If category 6 is a child of category 3 then only store that in the categories table, the products table doesn't need to know the parent category, only the main category.

as now if above query i am get only parent and its single child ..... not n level childs

With that kind of database design you need to use a recursive function to get each level. Have you used recursive functions before?
Link to comment
Share on other sites

Have you used recursive functions before?

 

 

NO SIR not used that ... actauly i am storing that main cat id and its parent id cat in product table ..becuase i have to manage that record with some inbuild functionality i know its bit complicated ...but to get fast result as per 6,3 ...

 

but geting all result as per 3 is making more complex ..i know i need to go with recurisve function ..but yes i havent used that before ...can you please guide me on same .. thanks

Link to comment
Share on other sites

It's a fairly common thing, there should be several discussions about it:https://www.google.com/search?client=opera&q=php+recursive+function+to+generate+a+parent%2Fchild+tree&sourceid=opera&ie=UTF-8&oe=UTF-8Basically, you write a function where you pass a category ID, and the function should return an array of child categories, where each child also has children. To do that the function calls itself to get each child ID, that's what makes it recursive. By the time the top level function returns it will have the whole structure.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...