6 Replies Latest reply on Jul 31, 2008 10:21 PM by mohnkhan

    QUB help.... displaying product category

    mrcharis2003@yahoo.c Level 1
      Hi there everyone

      I am just creating my first database with joined tables... I just got QUB to work and it is really great.... but I am having some trouble figuring out how to...... I have spent all day on the net trying to find out how do do this and still cant do it...

      Well first......
      I am trying to make a site that has products... I did have it all up and running but I figured out I was going about it all wrong... so back to the drawing board....I originally had a add product form that I could enter all the product info manually.... description, category, color ect....
      Then I learned a little more about how databases work and I figured that I needed more than one table to store my product info... so that I don't need to re-enter the same info over and over again..... So to start with I just want to have a product table and a category table....... when I get that to work will move on to manufacturer table etc etc .....

      So.... I am not sure what fields I need in the category table.... at the moment I have just cat_id cat_name and dateadd_cat
      So I created a form that can insert info into the fields... and it works so I put in five categories.... just to test..... say..
      Cat_id name
      1 cars
      2 boats
      3 Trains
      4 planes
      5 rockets

      So so far so good..... but here is where I get lost....and I don't know how it all works....

      I also have a product table. with all the usual fields and a category field.... witch I want to link to the category table..

      So I am not sure how to define what category the product is in so I figured it may work like this......

      I made a form to add new products and I put a drop down menu with all the categories.... cars... boats.... etc.... I populated the menu from the category table with values from the cat_id field..... I really not sure if that is what I have to do....

      So...... before all this I opened QUB and joined the two tables..... from the category field in the product table to the primary key field in the category table...... I added recordsets to all the pages I needed and ran the qub query I made in qub.....not sure again if this is what I have to do......

      So ... my category table has all the categories and my product table is working my forms all work the dropdown menu in the add product form
      has all the categories from the category table ... I added about ten products to test if it all works...
      BUT when I go to the list products page.... a simple dynamic table it displays all the information about the products I added but in the category field it only displays the corresponding number.... which is how I set it ... but I wanted that number to somehow grab the category name from the category table...... NOW my head is spinning I have no idea how to get this to work......

      I am sooo confused I cant even think of the right questions to ask
      Am I on the right track? What I need to know is..... how to get it all working...... basically....
      what fields do I need in the category table?
      How does the whole thing work?

      Any help would be great....
        • 1. Re: QUB help.... displaying product category
          mohnkhan Level 1
          Hello,
          i feel it can be like this...

          make a table for categories..
          categoryID, ParentCategoryID,CategoryName, Description...... so on.

          Parts table..

          PartID, CategoryId, PartName, PartDescription, bla bla bla....

          then have one single menu Create Part.

          in this landing form called Create a new Part.. u should just have one dropdown to which category it belongs tooo..

          CategoryId and parentcategoryID is for self linked table..

          means..

          for example..
          Parentecategory is Electronics
          inside it u can have Mp3player, mobiles, All in ones, video player..

          hope u can get this point..
          if u still cannot post back here..

          mohnkhan
          http://www.mohitech.com
          • 2. Re: QUB help.... displaying product category
            mrcharis2003@yahoo.c Level 1
            Hi there
            thanks for the reply

            at the moment I have a product and a category table

            the category table only has
            cat_id
            cat_name
            cat_date

            the product table has
            pro_id
            pro_name
            pro_cat
            pro_color
            pro_desc

            I used qub to join the pro_cat field to the cat_id field

            I will try to explain what I have done a little better...

            First this is just a test site to get joined tables to work..... that means I have no other pages that can interrupt me or give strange errors... I am also hosting this locally to test easilly.... I use WAMP server... never had any problems with it

            So I did the following
            I created the two tables in phpmyadmin

            I deinfed a new site in DW and a DB conection all no problem

            I created two form pages, one to insert into the product and one to insert into the category table.... the forms work fine

            I then inserted five records into the category table..
            1 car
            2 boat

            Then I created a dynamic list to display
            • 3. Re: QUB help.... displaying product category
              mrcharis2003@yahoo.c Level 1
              Hi there
              thanks for the reply

              at the moment I have a product and a category table

              the category table only has
              cat_id
              cat_name
              cat_date

              the product table has
              pro_id
              pro_name
              pro_cat
              pro_color
              pro_desc

              I used qub to join the pro_cat field to the cat_id field

              This is the query.

              SELECT products.*, categories.*
              FROM (products LEFT JOIN categories ON categories.cat_id=products.cate_pro)

              I will try to explain what I have done a little better...

              First this is just a test site to get joined tables to work..... that means I have no other pages that can interrupt me or give strange errors... I am also hosting this locally to test easilly.... I use WAMP server... never had any problems with it

              So I did the following
              I created the two tables in phpmyadmin

              I deinfed a new site in DW and a DB conection all no problem

              I created a form page to insert into the category table.... the form work fine

              I then inserted five records into the category table..
              ID Name
              1 car
              2 boat
              3 bus
              4 plane
              5 rocket

              Then I created an insert product form with a field for product category.... witch has a drop down menu to choose what category in the menu properties dialog box it has two fields label and value
              I chose to populate from database using the cat_name field as the label and cat_id as the value This is where I get lost.....

              I create a dynamic list as an admin page to check that all the record s have been inserted correctly and to add delete edit etc and all the records are inserted ok But in the product category field there is the number value I assigned to the label when I configured the dropdown menu... not the category name....

              So then I create a list product page with a normal dynamic table and have a advanced record set that uses the query I created in QUB and then I test and get the same thing only the cat_id in the category field......

              So after hours and hours of gooling every possible combination of words to figure out how I retrieve the category name from the category table..... some call master / detail table ... parent /child tables and I still no closer to understanding how it is done.

              my understanding is that only values that are the same in both fields will be displayed so that if the pro_cat value and the cat_id value are the same it will display the data in the cat_desc
              So that if I chose say 'car' as the category in the drop down menu in the insert product page that would insert the number '1' as the product category field in the product table (as I described earlier when I configured the menu properties so that the lable "car" has a value of "1") and then that field pro_cat is joined to the primary key of the category table cat_id.... so that the number "1" in the pro_cat field in the product table is the same as the number "1" in the cat_id in the category table ("1 "being the id and "car" being the description) so I think that it should show the cat_des instead of the cat_id when I reference the product table in the recordset in my list product page .....but it does not ..... ..... well I think that is how it works.... but I know I am wrong.......

              Am I correct or have I got it all wrong...... what I am trying to do is simple ... but I don't know how...

              So does that all sound ok like it will work...... because it does not work and I cant figure it out

              So you mentioned having a parent_id field in the category table ... is that necessary to display the cat_name.... how does that parent_id fiend join to the product table?

              Sorry to make a short msg long but I hope someone has a better idea of what I am trying to do and what I have done....

              I really just need someone to explain how when I create a list product page what recordset do I need to use do I need a simple recordset that only retrieves data from the product table (thinking that the product table is now linked to the category table.... or is that only linked after it is joined in qub and added as a new query in an advanced recordset)??

              What fields do I need to join in the product table and category table to display the category name from the cat_name field in the cartegory table in the pro_cat field in the product table category?//?????

              Now I am really lost..... going to sleep trying to figure this out is giving me nightmares.....

              Any help would be great

              Have a nice day

              Oh ...... would be a great tutorial for adobe to have..... joining tables with product, category and even sub category explained would be great.... I went through all the tutorials on the adobe site and the interakt site and found some great stuff but not exactly what I need

              If I can figure it out I will post how its done...

              takiteasy
              • 4. Re: QUB help.... displaying product category
                mohnkhan Level 1
                Hi,
                It will work and u r mostly on the right track.
                using QUB is as easy as cracking a joke. But u need to keep trying..
                this time sure i will post u a schema with ur input and a query for dynamic list form. which joins categories.

                U need to join the foreign key fields only and all u need to do is drag and drop the fields.

                What OS are you working on ?
                windows ?
                MAC?
                If you are on windows
                then i suggest u do something
                go download one of the following.
                1. MySQL Maestro 8.3....... or latest version..
                2. Database workbench pro 3.x
                all these trial version will do they will teach u to do some easy database design..

                PLease read some books on database design

                however for ur questions

                i have put down everything u need here.

                i am expecting u r using Mysql

                then run these scripts

                ______________________________________________

                CREATE TABLE `category` (
                `cat_id` int AUTO_INCREMENT NOT NULL,
                `cat_name` varchar(20) NOT NULL,
                `cat_date` date NOT NULL,
                /* Keys */
                PRIMARY KEY (`cat_id`)
                ) ENGINE = InnoDB
                CHECKSUM = 1
                COMMENT = 'This is category table';

                _________________________

                CREATE TABLE `product` (
                `prod_id` int AUTO_INCREMENT NOT NULL,
                `cat_id` int NOT NULL,
                `pro_name` varchar(20) NOT NULL,
                `pro_color` varchar(20) NOT NULL,
                `pro_desc` varchar(20),
                /* Keys */
                PRIMARY KEY (`prod_id`),
                /* Foreign keys */
                CONSTRAINT `fkeyproductcategory01`
                FOREIGN KEY (`cat_id`)
                REFERENCES `category`(`cat_id`)
                ON DELETE NO ACTION
                ON UPDATE NO ACTION
                ) ENGINE = InnoDB
                CHECKSUM = 1
                COMMENT = 'This is products only';

                CREATE INDEX `fkeyproductcategory01`
                ON `product`
                (`cat_id`);

                ___________________________________

                now ur joins for these two tables..
                ____________________________________________________________
                SELECT *
                FROM
                product
                INNER JOIN category ON (product.cat_id=category.cat_id)

                ___________________________________________________________

                PLEASE note:
                this is a inner join..

                u can use left join right join inner outer.... ahhhh

                there are some differences..
                please read some tutorials.. to understand them..

                there are many on internet.

                simply speaking
                replace

                INNER JOIN to LEFT JOIN
                to get all items which event have a category..

                if u dont plan to force foreignkeys...

                also note.. i have made most fields not null..

                hope this helps u to get started.

                mohnkhan
                http://www.mohitech.com
                • 5. Re: QUB help.... displaying product category
                  mrcharis2003@yahoo.c Level 1
                  Thanks for your reply

                  I am using a windows computer with mysql phpmyadmin wamp testing server

                  It took me ages to figure it all out.... but I did get it working....

                  I was having trouble understanding what happpens when you join two tables..... now I understand a little better...

                  I will explain how I got it to work....

                  I created two pages as before a dynamic list and form
                  I created the recordset using a query built in QUB joining the pro_cat field to cat_id.... all the same as before

                  So then instead of trying to fugure out how to display the category name in the dynamic list I tried a normal dynamic table

                  So I created a recordset using the same query (join pro_cat to cat_id and inserted a dynamic table and tested to see what happened......

                  And WOW..... in the browser window... low and behold the tables were joined in the dynamic table.... the table contained all the fields from the product and the category table side by side..... JOINED..... WOW and I noticed that the pro_cat number was in line with the correct cat_name and was displaying the correct name.... so
                  my join had worked and it was only returning equal values.... that answered many questions for me......

                  So then all I did was...... delete the dynamic field pro_cat from under the "Category' colum in the dynamic table and replace it with the cat_name dynamic field...... and deleted all the other unnecessary filds.... and tested it and it all worked perfectly.....
                  The table now displayed the Name of the product and the category name insterad ot the number...... So my form all worked fine and the finished list worked great as well

                  I can now insert new products and choose the category from a drop down menu and then display all the products in a dynamic list with the correct category name.....

                  Now all I have to do is figure out how to filter a recordset to display only products from one category..... that will be my next chalenge...... I will try to figure out for myself.... but will post if I get toooo stuck...

                  I looked at the tables you gave my in your reply..... I don't understand some parts... these parts

                  CONSTRAINT `fkeyproductcategory01`

                  REFERENCES `category`(`cat_id`)
                  ON DELETE NO ACTION
                  ON UPDATE NO ACTION

                  CHECKSUM = 1

                  COMMENT = 'This is products only';

                  CREATE INDEX `fkeyproductcategory01`
                  ON `product`

                  But I will try the tables out and see what happens and google what I don't understand....

                  Thanks again for you reply...... I went to your site and all the other sites you have blogs and stuff But I could not find anywhere to send you a msg

                  have a great day
                  thanks again....
                  • 6. Re: QUB help.... displaying product category
                    mohnkhan Level 1
                    Well my friend, I was forced to remove all personal information.. from my website.. bcos of some bad people trying to do some social engineering.

                    Now about what that foreignkey is... u can learn more about it by trying to create tables with my given script..

                    then try using a category to create a product. and then delete the category,
                    it wont allow if the constraint is set to restrict.

                    For this u can learn by reading more about foreign keys..

                    That all is MySQL specific keywords..
                    please use a GUI mysql GUI tools which are free from mysql.com
                    else u will waste lot of type correcting spelling mistakes/typos in ur command.

                    now to filter and show only products from a single category..
                    u can do it in many ways,.

                    way 1:
                    left hand side u have list of all categories..
                    person clicks a category and on centre of page section u see all products in that category...
                    for this u need to add a filter by URL parameter to the ListRecordset of ur dynamic list.

                    way 2:
                    there is something called Filter in dynamic list.. just use it..
                    and in old interakt site there is a mxkollection3.chm file download it. It has a how to section, to always display the filter use it....
                    this way when a person selects a category the list is filtered by that category.

                    u can message me directly at mohnkhan< sign of the at here >mohitech.com or mohnkhan(again at signhere ) yahoo.com
                    or gmail.com

                    these are all personal emails i dont use company emails anywhere,, bcos they are monitored.