10 Replies Latest reply on Nov 30, 2007 4:13 PM by (Adam_S_Bell)

    Newbie help on Queries

    Level 1
      I just set up my first query page using two tables (categories and properties) and I set up a simplistic page here just to see if the data loads:

      http://209.62.4.98/~parishpr/query1.php

      It does, but it loads all the entries of the properties table. I just want it to display one of the three categories (One Bedroom, Two Bedroom, Three Bedroom) and I thought just adding ?id=1 would do something like that but it didn't change anything. I set up a query with a join using ADDT connecting the category row from the properties table to the id row of the categories table. So why am I getting all the data instead of just one of the rows from the categories table? Can anyone help an ADDT/PHP newbie?
        • 1. Re: Newbie help on Queries
          Günter Schenk Level 4
          Hi Adam,

          let´s assume your property table´s bedrooms column is named, well, "bedrooms", and this column is numeric and holds values like "1", "2", "3", you´ll rather need to filter against this table/column when passing an URL value like e.g. ?bedrooms=1

          Cheers,
          Günter Schenk
          Adobe Community Expert, Dreamweaver
          • 2. Re: Newbie help on Queries
            Level 1
            That's what I thought. Actually the row with the numbers is called 'category' but if I use:

            http://209.62.4.98/~parishpr/query1.php?category=3

            Nothing changes and all three category rows show up. I tried id as well instead of category but no change.
            • 3. Re: Newbie help on Queries
              Günter Schenk Level 4
              maybe there´s something within the "join" part of your query that´s messing things up -- could you please post the complete query here ?

              Cheers,
              Günter Schenk
              Adobe Community Expert, Dreamweaver
              • 4. Re: Newbie help on Queries
                Level 1
                Go here:

                http://209.62.4.98/~parishpr/query1.txt
                • 5. Re: Newbie help on Queries
                  Günter Schenk Level 4
                  Hi Adam,

                  none of the queries in there provide any "hook" for checking against e.g. against an URL variable -- they´re all "stand-alone" and currently can´t detect any external parameter, and that´s why all records are displayed regardless what you´re passing to this page.

                  As far as I can see, it´s the "detail2properties" query which needs to be modified to make "WHERE category" equal an URL variable

                  Cheers,
                  Günter Schenk
                  Adobe Community Expert, Dreamweaver
                  • 6. Re: Newbie help on Queries
                    Level 1
                    So how do you do that? As I mentioned, I'm not a programmer by trade. I usually do front-end.
                    • 7. Re: Newbie help on Queries
                      Günter Schenk Level 4
                      How did you assemble all those queries -- with ADDT´s Nested Repeat Region wizard I guess ?

                      However, here´s a quick & dirty hack which should get you going without having to dig into that matter too much -- if this very page will *always* be receiving the "category" URL parameter, I´m sure it´s safe to manually replace the existing "detail2properties" query...

                      SELECT * FROM properties WHERE category=123456789 ORDER BY property

                      ...with...

                      SELECT * FROM properties WHERE category=123456789 AND category = ".$_GET['category']." ORDER BY property

                      That´s definitely not the most "Dreamweaver compatible" method, and it´s not the most professional one, because this query should normally be checking the existance of the URL parameter "property" -- however, *if* you´ll always be passing e.g. "?category=3" to this page, it´s supposed to work without that extra check

                      Cheers,
                      Günter Schenk
                      Adobe Community Expert, Dreamweaver
                      • 8. Re: Newbie help on Queries
                        Level 1
                        Yes, I did use Nested Repeat Region and then added some tweaks so I could add more data fields in the table row. Should I have done something differently? I used one of the demos in the Developers Center as a basis to do this.

                        I changed the line of code to SELECT * FROM properties WHERE category=123456789 AND category = ".$_GET['category']." ORDER BY property but when I went to test it in DW, I got an error and a prompt that said:

                        An error occurred while requesting the document from the testing server:

                        You have an error in your SQL Syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY property' at line 1.

                        BTW, I had to retype all that by memory. You think in future versions of Dreamweaver that text should be selectable?
                        • 9. Re: Newbie help on Queries
                          Günter Schenk Level 4
                          Oops, sorry,

                          I forgot to enclose my addition in single apostrophes -- that should be:

                          SELECT * FROM properties WHERE category=123456789 AND category = '".$_GET['category']."' ORDER BY property

                          ------
                          Should I have done something differently?
                          ------

                          yes, the "detail2properties" query should have rather become somewhat enhanced to filter against a certain URL variable, but this requirement probably wasn´t clear at the time you implemented the Nested Repeat Region wizard.

                          The demo you were using apparently didn´t cover this particular scenario -- but when trying to create a "real world" application, the specific requirements are usually different from any tutorial.

                          However, ADDT actually provides two Nested Repeat Region "flavors" -- a) the Wizard that´s doing everything for you without having to provide the recordsets, and b) the Nested Repeat Region *server behavior* which can be applied to existing recordsets. I´m not sure which one you actually used, but I guess it´s the Wizard, which can be a little inflexible when it comes to the generated recordsets.

                          ------
                          You think in future versions of Dreamweaver that text should be selectable?
                          ------

                          no clue, I´m not involved in developing DW ;-) However, please feel free to file a feature request.

                          Cheers,
                          Günter Schenk
                          Adobe Community Expert, Dreamweaver
                          • 10. Re: Newbie help on Queries
                            Level 1
                            Yep, that seems to work. If I add the ?category=1 it only displays entries in the one bedroom department.

                            Yea, I did use the Wizard. Probably because I thought it would be faster and there isn't that much information on how to use the various widgets properly. This is all new to me (meaning I plan to post here a lot while I learn) so you can imagine there's a lot of stuff I don't get a grasp of. I think I'll try another version with just the server behavior instead.