10 Replies Latest reply on Jul 26, 2008 1:53 AM by mrcharis2003@yahoo.c

    How do I add a most recently added product page... using products from more than one database table?

    mrcharis2003@yahoo.c
      Hi there
      Long time no posts..... I have been busy

      Just a quick question

      I am using ADDT and mysql phpmyadmin

      I am making a site that , an online store that sells different types of fashion accessories

      My site is coming along very well.
      I have most of the pages I need up and running.
      I have an admin area where I can add new products and edit and delete product information.

      At the beginning I only had one product table but I quickly figured out I needed maaaany tables to store information about different types of product

      I want to have a 'new products' page which displays all the most reently added products..... from different categories/pages in my site

      I also want to add a 'on sale' page which uses the same principles

      I know already how to filter a recordest by date added ascending/decending
      BUt I can't figure out how to get different product info,from different tables into one recordset

      at the moment I have these tables as an example

      necklaces
      handbags
      belts
      purses
      new products
      on sale

      I am not sure if I am going about this the right way or not.

      Any ideas or help would be much appreciated

      have a nice day
        • 1. Re: How do I add a most recently added product page... using products from more than one database table?
          Günter Schenk Level 4
          Hi charis,

          as such an advanced recordset will require "joining" all those tables, I suggest assembling the query using ADDT´s Query Builder.

          Cheers,
          Günter Schenk
          Adobe Community Expert, Dreamweaver
          • 2. Re: How do I add a most recently added product page... using products from more than one database table?
            mrcharis2003@yahoo.c Level 1
            Hi thanks for you advice

            I have seen many pictures of tables made using QUB, I think that is addt query buider, but I have no idea of how to get it to open or how to use it and I can't find anything on the web about it

            could you please point me in the right direction ?

            tell me how I can get started?

            any help at all would be great

            have a nice day
            • 3. Re: How do I add a most recently added product page... using products from more than one database table?
              Level 1
              charis,

              Open the Recordset Wizard in Dreamweaver. Look at the options on the right. One is QuB and that is the Query Builder. After you click on that, you have to click on New Query to open the full Query Builder.

              The Query Builder will open and run in a separate browser window.

              Shane
              • 4. Re: How do I add a most recently added product page... using products from more than one database table?
                mrcharis2003@yahoo.c Level 1
                Ok thanks a lot

                I got it to open

                But I have absolutely no idea what any of it means and how to get started
                I clicked on help and got next to nothing from the addt help center

                and I cant find any tutorials or any help online

                any advise about how to get started?

                any help would be great
                • 5. Re: How do I add a most recently added product page... using products from more than one database table?
                  Günter Schenk Level 4
                  Hi Charis,

                  the ADDT helpfile has a section named "Retrieve Dynamic Data", and in here there´s a chapter "Retrieve data from databases" which explains the Query Builder in a rather detailed manner.

                  Cheers,
                  Günter Schenk
                  Adobe Community Expert, Dreamweaver
                  • 6. Re: How do I add a most recently added product page... using products from more than one database table?
                    Günter Schenk Level 4
                    Hi Charis,

                    there´s one pretty helpful video tutorial about the Query Builder available on the Interakt website: http://www.interaktonline.com/Products/Bundles/MXKollection/Documentation/

                    Scroll down to "Instructions" and open the movie "Link Tables with JOINS" -- I consider it helpful especially in your case, because that´s exactly what you´re going to do when creating relations between tables.

                    I think it will (by nature, because it´s a complex beast) take some time until you´ll able to fully understand how the Query Builder works respectively what you do with it, so you´ll certainly have to experiment a little until you´re getting the expected results -- and in addition it would be helpful to understand what "Joins" are, and you´ll find lots of tutorials out there when googling for "sql join tutorial".

                    Cheers,
                    Günter Schenk
                    Adobe Community Expert, Dreamweaver
                    • 7. Re: How do I add a most recently added product page... using products from more than one database table?
                      mrcharis2003@yahoo.c Level 1
                      Thanks Gunter

                      I have looked at the link you showed me and it is really good. I wish I had known about that page a long time ago.

                      I have been trying my hardest to figure out how to best design my database.

                      It is all way over my head.
                      I am slowly figuring out about relational databases linking tables together etc....

                      DO you know of any other really useful links or books that could help me? any forums that are about databases?

                      Thanks for all your help

                      Have a good day
                      • 8. Re: How do I add a most recently added product page... using products from more than one database table?
                        mohnkhan Level 1
                        Hello..
                        First of all sorry if anyone feels i am trying to hijack this discussion No I am not.

                        method 1.
                        probably the easiest way.
                        1.u can do create a view, which is union of all these product tables.

                        2. then create a simple query which runs on this view.
                        remember view for u is just like a virtually existing table..

                        Method 2.

                        Use the query Build and create joins..
                        else
                        go download MySQL Maestro 8.3.bla bla bla trial and try ur idea out.
                        in its GUI sql query builder.
                        (this will work only if u have windows )

                        I am no expert but I believe both ways should work..
                        • 9. Re: How do I add a most recently added product page... using products from more than one database table?
                          If you're looking at learning a bit more about database design (looking at your description of what you've done in creating a separate table for each category of item sold sounds like you're approaching it as though it was a flat-file rather than a relational database), there's a few books that I could direct you to:

                          Beginning Database Design (ISBN 978-1590597699)
                          Database Design for Mere Mortals (ISBN 978-0201694710)
                          Databases Demystified (ISBN 978-0072253641)
                          Absolute Beginner's Guide to Databases (ISBN 978-0789725691)

                          I've also got an older book that's always sitting by my desk called Dreamweaver MX Databases (ISBN 978-0782141481), which I still reference regularly - chapter 3 on coding practices and chapter 5 on database design provide very good overviews of the subject.

                          There's also a Database Development for Dummies, but I'm not a huge fan of the series where they're tackling technical subjects.

                          Without knowing the data you're modeling, it's quite difficult to make generalisations, but I'd probably be looking at a single table called 'Product' (which contains all the product records), and which had in it a boolean for 'new item' and for 'on sale' (you could then use SQL to filter out new and/or sale items when you create your recordset). I'd then have a look up table called 'Categories' which held the values 'necklace', 'handbag', 'belt', and 'purse' which the Product table could then reference to identify.

                          Breaking your data down into a logical structure like this is called 'normalisation' (see http://en.wikipedia.org/wiki/Database_normalization) - although it's a bit technical. There's a simpler practical explanation of a similar query to yours at TechRepublic (http://articles.techrepublic.com.com/5100-10878_11-5288500.html), which uses MS Access, but don't worry about that as it's teaching a concept which is applicable no matter what the database server.
                          • 10. Re: How do I add a most recently added product page... using products from more than one database table?
                            mrcharis2003@yahoo.c Level 1
                            Hi there

                            thanks for the reply I have a few books on database design will try to get some of the books you mentioned

                            at the moment my site has different tables for all the different categories which was fine at first but as you pointed out it is much easier to display the most recently added item if all the items are in the same table so I am trying to get it all working now......

                            My problem is....... I can create the two tables product and category but I not sure how to link them...... do I put a field in the product category and have a forein key on it?.....I am lost trying to get this to work..... do I actually have to type a query into QUB to connect the two tables or can I just do in my phpmyadmin by using foreign key?

                            As you can see I am lost.....

                            and How do I define what product is in what category? Do I create form to upload new product info with a field to choose product category from a dropdown menu and have each category value a number.....
                            neclaces 1
                            rings 2
                            belts 3
                            etc etc
                            so that that number is stored in the product table and that number refers to the corresponding table?

                            I have been looking at the interact website about how to join tables and they talk about inner join left join etc etc is really confusing for me

                            So........ in layman's terms .... how do I join the two tables product and category ?
                            And how do I insert and store the product category info?

                            Am I going about this the right way or is there a better way?

                            SOrry to post so many questions about this .... I really do exhaust every other avenue.... mostly googling stuff for hours....... before I post anything here... but I am lost and maybe someone here can help .....

                            Any help would be great.....

                            Have a nice day