13 Replies Latest reply on Aug 7, 2008 9:52 AM by mohnkhan

    Filter record set to display only records of a certain category / color / manufacturer etc

    mrcharis2003@yahoo.c Level 1
      Hi there everyone

      First I am using windows and mysql

      I am building a site with many different categories of products......

      I would like to filter a record set to display records of a certain category or color or manufacturer etc....

      I have my product insert form and list working fine but I cannot figure out how to create a recordset that filters the records by (matching field values) product category....

      I tried choosing filter by pro_cat (my product category field) in the drop down menu in the recordset set up tab but I cannnot figure out how to get it to work......

      Any help would be great....

      This is the HISTORY of what I am trying to do........

      I was storing all my product information in separate tables.... one table for one category....so that I could create a recordset that would only display records about one product..... for example a necklace page that displayed only necklaces.... not any othere category of product.....

      This was all working fine until I decided I wanted to have a most recently added page that would display the latest products no matter what category was so I was told to join all the tables then I was advised and I decided to have only one product table with a category field joined to another category table with all the category names......

      So I finally got that all working fine.... now I can insert records and choose what category from a drop down menu in the insert form and display the product with correct category name (pulled from the category table) in a dynamic list or a dynamic table...... that all works fine but only using a recordset that shows all the records from the product table....

      So what I need to know is how to filter the records in my product table by product category...... not all categories only one category....

      I have looked at many sites and tried to do myself but I cannot find out how to do this.....

      Anyone got any ideas......

      Any help would be great
      Have a great day.....
        • 1. Re: Filter record set to display only records of a certain category / color / manufacturer etc
          mohnkhan Level 1
          Create a URL filter for recordset,

          one one page u want to show category
          with hyperlinks to CATEGORY={CatID}

          now on ur destination page..
          add..dynamic list.
          and for the recordset of the dynamic list which is called as list recordset
          put the filter
          WHERE cat_ID = Request("catID")

          try reading about how to add parameters to recordsets.
          • 2. Re: Filter record set to display only records of a certain category / color / manufacturer etc
            mrcharis2003@yahoo.c Level 1
            Hi there again thanks for the fast reply.....

            Sorry to bother you but I cant figure out what you mean.....

            I could write what I think you mean but I know I am wrong....

            Ok here goes...

            Create a URL filter for recordset,

            on one page u want to show category
            with hyperlinks to CATEGORY={CatID}

            DO YOU MEAN THAT I CREATE A PAGE THAT LISTS ALL MY CATEGORIES AND PUT A URL FILTER THE RECORDSET BY CAT_ID ON THAT PAGE....???

            now on ur destination page..
            add..dynamic list.
            and for the recordset of the dynamic list which is called as list recordset
            put the filter
            WHERE cat_ID = Request("catID")

            OK NOW SAYING THE DESTINATION PAGE YOU MEAN THE PAGE THAT OPENS WHEN YOU CLICK ON ONE OF THE CATEGORY NAMES?

            Ok.....hm...... and when I add a dynamic list.... click on the dynamic list wizard.... I cant fine where to put the filter WHERE cat_ID = Request("catID")???

            And when you say add a dynamic list on the destination page do you mean that I have to display all the products of the same category using a dynamic list? I would prefer to just use a dynamic table... it is cleaner.....

            So sorry to ask again ...... but could you go into a little more detail...... in very simple steps......

            I am not sure but I think you may mean that when you click on a category link on the category page it passes that category id to the destination page.......?? I think I don;t know and if that is the case how do I set which cat id?

            Anyway I appreciate all you help ..... I will read about adding parameters .... any links or books would be great... I have tried to find before but cannot find anything specific to do with ADDT....
            is the system used in addt the same as say sql or do I have to find mysql information.... I really have no idea...

            anyway thanks again

            have a great day
            • 3. Re: Filter record set to display only records of a certain category / color / manufacturer etc
              Günter Schenk Level 4
              Hi Charis,

              ---------
              Anyway I appreciate all you help ..... I will read about adding parameters .... any links or books would be great... I have tried to find before but cannot find anything specific to do with ADDT....
              is the system used in addt the same as say sql or do I have to find mysql information.... I really have no idea
              ---------

              did you already read the DW/PHP related tutorials on Adobe´s Dreamweaver Developer Center: http://www.adobe.com/devnet/dreamweaver/ ?

              Cheers,
              Günter Schenk
              Adobe Community Expert, Dreamweaver
              • 4. Re: Filter record set to display only records of a certain category / color / manufacturer etc
                mohnkhan Level 1
                Hi friend,
                Here we go....
                1.Yes you are on the right path..
                2. You can make a iframe to make it look good and without doing a whole page refresh.. I will guide u with that too..
                3. but before you jump to that step first have a dynamic list ready on a separate page, then...

                step by step how to add a URL query string based filter.
                ___________________________________________________________
                ... in ur dynamic list page look at the server behavior panel
                Next... u can see a List Recordset...
                double click it...
                a recordset editor of dreamweaver will open which

                now by default dreamweaver shows you simple.... view..
                there is a button called advanced...
                press it ..

                ahh what a sigh of relief.
                here in you can see....
                something called parameters.
                now...

                have a peek into ur SQL query there should be something like

                SELECT bla bablablabla.....
                ..........................
                WHERE NXTFilter
                ORDER BY NXTSort

                now change this too..

                WHERE NXTFilter AND MyCategoryID
                ORDER BY NXTSort

                on the parameters click the plus sign...

                bingo another dialog box opened..
                Now its asking for
                NAME:
                Type:
                Value:
                Default Value:

                Name: type in here MyCategoryID
                type:numeric
                Value:Request("Cat_ID")
                DefaultValue: 0
                <-- this default value is zero not letter o

                press ok..

                bingo ur work is done...
                now

                from the source page u need to make sure.. u passes
                myproductlist.asp?Cat_ID={ur category id from recordset}


                ___________________________________________________________

                Hurray ur dynamic list with category filter works like a charm.

                now if u want to show by default the first category...

                U will have to do little more work on it...

                and thats next time.....
                after you can accomplish this simple way..

                Apologies for my spelling/grammar mistakes if any i dont ever check them:) all i intend is the meaning / concept is delivered

                Regards
                mohnkhan
                http://www.mohitech.com
                • 5. Re: Filter record set to display only records of a certain category / color / manufacturer etc
                  mohnkhan Level 1
                  I forgot to point out one thing in this...
                  I have written request(" ") this is ASP specific..
                  there should be something similar in php..
                  as i am not a php developer i dont have a clue..
                  however
                  i think you should read this..
                  http://www.w3schools.com/PHP/php_get.asp

                  The $_GET Variable
                  • 6. Re: Filter record set to display only records of a certain category / color / manufacturer etc
                    mrcharis2003@yahoo.c Level 1
                    Hi there

                    Thanks a lot for all your help

                    Sorry I have not been online to say thanks I have been busy ........ and still trying to get what you told me to do to work.....

                    I still cant get it working......

                    But I am getting closer.

                    Is it ok if I ask some more questions?

                    First....... Do I have to use a dynamic list to display records by category? I would like to just use a simple dynamic table..... I would like to use a dynamic list in my admin section but in my public product galleries I am going to use hhorizontal looper to display products not in a list like in the dynamic list.... But I still want to get the dynamic list working filtered by one category.... but only for the admin ..not public gallery ... so will a normal dyamic table work with the same style of advanced recordset you mentioned?

                    Anyway back to my problems.....

                    I have followed what you said and have just about got it working accept that when I click on a link to a specific category I get the list page for that category but still full of all categories of product......

                    So I now have two test pages.... one admin page with a list of all my categories and one specific dynamic list page

                    To the best of my understanding so far I need to have a list of different categories on one page that link to corresponding detail pages .... one detail page for each category......

                    So Far So Good......

                    So in order to filter the records being sent to the detail page I need to pass a "variable" which is unique for each category ..... the cat_id number (similar to if you pass the product_id from master to detail pages .... or using the kt_login to display only the logged in uses info on the detail page........ I think )

                    Correct?

                    So if I only have five categories then there is only five cat_id numbers
                    Necklaces = 2
                    Rings = 3
                    Bracelets = 4
                    Earings = 5

                    So that unique cat_id number is the number that needs to be passed to the detail page........... ?

                    So next I have to create a link from the necklace category to the necklace detail page.... that passes the cat_id number..... the number '2'

                    Am I on the right path

                    So one of the categories is "necklaces", this is the category I want to link to the list necklaces page..... so when you say....

                    from the source page u need to make sure.. u passes
                    myproductlist.asp?Cat_ID={ur category id from recordset}

                    DO you mean that I should create a link from the necklace category and define a url parameter the same as what you wrote..... in my case
                    url listneck.php?cat_id=2
                    2 being the category number for necklaces

                    SO as to pass the category id number "2" to the list page ....
                    Is that correct or do I need to put something else after the '?"
                    Like url listneck.php?______________________(I don't know)

                    When you said

                    "myproductlist.asp?Cat_ID={ur category id from recordset}

                    {ur category id from recordset}=??? does this mean I should type in the category id number that is the same as the name of the link?
                    Or something different?......

                    So that is the master / category list or as you say the 'source' page
                    done ..... I think..... Please correct me if I am wrong.....

                    So then I have to create a detail page with a dynamic list... to display all neckaces (not other products)

                    So I create a dynamic list... configure it to get records from a recordset not a table.... a recordset that retrieves records from all the fields in my product table.....

                    So I test the list and it all works .... but does not filter the records but does work

                    So then I follow your instructions and open the listrecordset and go to advanced.... then in the SQL query box I see this....

                    SELECT *
                    FROM products
                    WHERE {$NXTFilter_Recordset1}
                    ORDER BY {$NXTSort_Recordset1}

                    So then I insert AND mycategoryID to become

                    SELECT *
                    FROM products
                    WHERE {$NXTFilter_Recordset1} AND mycategoryID
                    ORDER BY {$NXTSort_Recordset1}

                    Does that look correct to you?

                    Then I go to the + sign under variables (you said parameters... maybe ASP is different..... or you meant variables.....?)

                    And I get another window open.....

                    So I write

                    Name mycategoryID
                    TYPE numeric
                    default value 0
                    runtime value $_REQUEST["cat_id"]

                    now at this point I am not sure weather to use $_REQUEST or $_GET
                    I have tried both .....

                    So am I right in saying that the $_REQUEST["cat_id"] is requesting the variable from my source page the value "2" when I passed the listneck.php?cat_id=2 URL variable

                    Is that correct......

                    I have tried both GET and REQUEST and still cannot filter the records to display only record that are in the necklace category.... number 2

                    So I think I am close to getting it all working ...... but it still does not work.......

                    Anyone got any ideas......

                    Any help would be great........

                    have a great day
                    • 7. Re: Filter record set to display only records of a certain category / color / manufacturer etc
                      mohnkhan Level 1
                      Hello Friend,

                      you are almost 99% on the right track..

                      I will reply back to you with a sample application..

                      i can even make a working page and paste it for you here..
                      no big deal about it..

                      in php.. all i need is few minutes, of free out of office hours..

                      sure.. there is help coming on the way.. after few more mins...

                      Regards
                      mohnkhan
                      • 8. Re: Filter record set to display only records of a certain category / color / manufacturer etc
                        mohnkhan Level 1
                        Hello about Request /POST
                        Google it out

                        __________________________________________
                        okay, return to your topic.
                        $_REQUEST default is: 'gpc'
                        that is:
                        $_REQUEST = $_COOKIE + $_POST + $_GET;
                        same as:
                        $_REQUEST = array_merge($_GET, $_POST, $_COOKIE);
                        $_COOKIE will have higher priority.
                        if u mess up cookie/form names, your $_REQUEST will be messed up.

                        ___________________________________________

                        I got the above lines from this link

                        http://www.webmasterworld.com/forum88/2066.htm

                        try this too

                        http://www.google.com/search?q=%24_REQUEST&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-US:of ficial&client=firefox-a

                        This teaches you the GET POST REQUEST concepts..
                        • 9. Re: Filter record set to display only records of a certain category / color / manufacturer etc
                          mohnkhan Level 1
                          This should be ur category page looking..

                          _____________
                          <a class="KT_edit_link" href="CategoryEdit.php?cat_id=<?php echo $row_rscategory1['cat_id']; ?>&amp;KT_back=1"><?php echo NXT_getResource("edit_one"); ?></a><a class="KT_edit_link" href="ProductsListPerCategory.php?cat_id=<?php echo $row_rscategory1['cat_id']; ?>&amp;KT_back=1"><?php echo NXT_getResource("show products"); ?></a> <a class="KT_delete_link" href="#delete"><?php echo NXT_getResource("delete_one"); ?></a>
                          ____________________
                          • 10. Re: Filter record set to display only records of a certain category / color / manufacturer etc
                            mohnkhan Level 1
                            about passing URL values into recordset i dont know how to do it in php..

                            possibly its $_GET['urlparametername']

                            but you still need to find out..
                            for this u need to see dreamweaver
                            record set help.

                            possibly ur php is ten times better than mine..
                            • 11. Re: Filter record set to display only records of a certain category / color / manufacturer etc
                              mrcharis2003@yahoo.c Level 1
                              Hi there Thanks for You relpy and all your help

                              I did google about GET and REQUEST but I did ont really understand it ... but your links helped a lot... I will figure it out

                              When you said......

                              This should be ur category page looking..

                              _____________


                              You really lost me.

                              But I understand a little and I will figure it out...

                              Anyway after much googling I found a good tutorial on the old interakt website ...... It seems to me to do a lot of the same things I want

                              It is a site which has different topics that you can post comment and articles about ...... and it describes how to pass variables and retrieve only topics about one specific article....

                              The link is http://www.interaktonline.com/Products/Free-Products/MXTreeMenu/Documentation/Articles/(U PDATED)+Building+a+Blog+%232:+Building+the+Administration+Section-Build+a+master-detail+se t.html?id_art=33&id_asc=286

                              Check it out if you have time..... tell me what you think.... whether it can help me....

                              I still can't figure it all out ... that is my mission now.....

                              Another tutorial is at DMXZONE
                              http://www.dmxzone.com/showDetail.asp?TypeId=2&NewsId=3652&LinkFile=page6.htm

                              It describes how to do what I want nearly exactly....

                              anyway ..... thanks for writing and posting that code .... I will try to get it working

                              Take a look at those links if you can ... would be great

                              Have a great day
                              thanks again...
                              • 12. Re: Filter record set to display only records of a certain category / color / manufacturer etc
                                mrcharis2003@yahoo.c Level 1
                                Ok hi there everybody........

                                wooooohooooo

                                I got it to work....

                                NOt exactly how I wanted but it works

                                I can now display products of only one category on a detail page.....

                                I read through a lot of stuff but I could not get my detail page to retrieve the correct category id from the master page.....

                                anyway
                                I will get it working better

                                The way I got it to work is in the advanced recordset SQL query box
                                I pu in .....

                                SELECT products.pro_name, products.pro_price, products.pro_desc, products.pro_pic1, products.pro_featured, products.pro_sale
                                FROM products
                                WHERE products.pro_category=1

                                The key was the =1 I did try to put in =category and then + variable and name it category default value -1 and runtime value of $_GET['pro_category'] and $_REQUEST['pro_category'] so as to retrieve the category id as passed from the detail page ..... but it didn't work so I just put in =1 1 being the category id for necklaces and then tested it and walaaaa it only returened records from the necklace table...... coooooool

                                I still now sure why or how it all works ....just so long as it does

                                SO finally after days and days of stuffing around now it all works

                                I will put a detailed description of how I did it asap and post it here

                                Thanks again for all your help

                                Oh I got a lot of ideas off the DMXZONE tutorial but it was all ASP but the ideas were the same

                                Thanks again

                                Have a great day

                                Oh .... and I will try to get it working better

                                But for now it is ok....... on to the next task.........

                                Bi for now

                                Thanks again

                                Have a great day
                                • 13. Re: Filter record set to display only records of a certain category / color / manufacturer etc
                                  mohnkhan Level 1
                                  Hi.
                                  Good to know you got it to work..

                                  congrats on ur success.

                                  and about php i know some basics of it by now..
                                  will read a little about php variables and what inbuilt classes it has

                                  it should get me to speed then.

                                  And will let you know.. too..

                                  Enjoy!
                                  mohnkhan