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

[shashib]

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 April 9, 2015 by shashib

[shashib]

any one ..please help me...

 

actually its combine of this : http://w3schools.invisionzone.com/index.php?showtopic=52843&hl=

 

i got solution on above link, thanks to davej ...but above question parent id wise result is not matching here ....

[justsomeguy]

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.

[Christopher.Burkhouse]

Why is this on the PHP subforum instead of the SQL subforum? Looks like you need help with your SQL query.

[shashib]

@ 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

[justsomeguy]

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?

[shashib]

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

[justsomeguy]

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.

[shashib]

thanks sir will try this