15 Replies Latest reply on Sep 18, 2014 12:35 PM by bregent

    Database Record Filtering

    Max Resnikoff Level 1

      Hey,

       

      I was if there is a way to filter my repeat region by fields.

      Is there a way in which i can have the field titles (top of the records) act as a filtering system?

      E.g. If i click on Author, my recordset will filter by author ascending?

       

      Below is an image of my current inventory setup.

      Captures.JPG

       

      Also, is there a way which I can add a search bar which will only show records which have the same information as what was searched for?

       

      Any help would be very much appreciated!

      Thank you!

        • 1. Re: Database Record Filtering
          bregent Most Valuable Participant

          >E.g. If i click on Author, my recordset will filter by author ascending?


          Ascending? It sounds like you are asking about sorting, not filtering.


          >Also, is there a way which I can add a search bar which will only show

          >records which have the same information as what was searched for?


          Of course, are you coding this by hand? DW has (or had) a server behavior for this.

          1 person found this helpful
          • 2. Re: Database Record Filtering
            Max Resnikoff Level 1

            I am not coding by hand, Obviously editing the code which DW creates.

             

            How would I go about being able to sort the records just by clicking the headings, and how would I add a search bar which will only show the records with what was searched for?

            • 3. Re: Database Record Filtering
              bregent Most Valuable Participant

              >How would I go about being able to sort the records just by clicking the headings,

               

              Dreamweaver does not provide a facility for this. There are two basic methods

               

              1) Submit the item clicked back to the query which modifies the Order By clause which sorts the resultset.

              2) Use DOM table sorting techniques. I like this better as it is more responsive

               

              sorttable: Make all your tables sortable

               

              >how would I add a search bar which will only show the records with what was searched for?


              You could use DW's search and result page behaviors

              Dreamweaver Help | Building search and results pages


              But the sooner you move away from obsolete server behaviors and onto modern coding techniques the better.

              • 4. Re: Database Record Filtering
                Max Resnikoff Level 1

                Thank you for the first link.

                 

                Do you think you could just put in like steps how to do it? The tutorial skips a few bits like making the header click-able etc...

                • 5. Re: Database Record Filtering
                  Max Resnikoff Level 1

                  For the second link, Is there a way in which i can search for keywords? Because at the moment, I have to type in : Nexo Monitor Top, and what i want to search for is nexo. I dont want my users to have to enter the full name, just keywords.

                  • 6. Re: Database Record Filtering
                    bregent Most Valuable Participant

                    >Do you think you could just put in like steps how to do it?The tutorial skips a few bits like making the header click-able etc...


                    Nothing is missing. There are only 3 steps - the site tells you exactly what to do.


                    1) Download the Javascript library

                    2)Include the Javascript library, by putting a link to it in the HEAD of your page, like so:

                    <script src="sorttable.js"></script>

                    3) Mark your table as a sortable one by giving it a class of "sortable":

                    <table class="sortable">

                     

                    Note that the library's JavaScript file is called sorttable (two Ts), but the class you add to the table is sortable (one T).

                    • 7. Re: Database Record Filtering
                      Rob Hecker2 Adobe Community Professional & MVP

                      Bregent, really like the sorttable script you provided a link to.

                       

                      Max, if your column heads are in <th> tags, they will automatically become clickable.

                       

                      I don't think there are steps missing from the instructions. There just isn't much you need to do to make it work. Did you first add the script in a .js file called sorttable.js and then correctly link to it from your page? After that, all you need to do is add the class "sortable" to your table tag.

                      • 8. Re: Database Record Filtering
                        bregent Most Valuable Participant

                        >For the second link, Is there a way in which i can search for keywords?

                         

                        You need to use the LIKE keyword and wildcards in your SELECT statement. The DW server behavior for searching records is not a very robust tool for allowing users to search records. You are much better off building your own, rather than trying to modify their code.

                        • 9. Re: Database Record Filtering
                          bregent Most Valuable Participant

                          >Bregent, really like the sorttable script you provided a link to.

                           

                          Yeah, there's really no need to round-trip to the database to sort most tables. The link I provided has been around for years. Nowadays, there are also plenty of examples that use jquery.

                          • 10. Re: Database Record Filtering
                            Max Resnikoff Level 1

                            I got the sorting working but it only works for the first few headers. and im not sure why

                            • 11. Re: Database Record Filtering
                              Max Resnikoff Level 1

                              bregent wrote:

                               

                              >For the second link, Is there a way in which i can search for keywords?

                               

                              You need to use the LIKE keyword and wildcards in your SELECT statement. The DW server behavior for searching records is not a very robust tool for allowing users to search records. You are much better off building your own, rather than trying to modify their code.

                               

                              I would but I'm completely new to this, and I don't have time on my side at the moment :/

                              • 12. Re: Database Record Filtering
                                Rob Hecker2 Adobe Community Professional & MVP

                                ". . .plenty that use jQuery"

                                 

                                Sure. jQuery plug-ins are great as solutions of last resort. It's always nice to find a non-JQ solution that is light weight and just as easy to implement.

                                • 13. Re: Database Record Filtering
                                  Max Resnikoff Level 1

                                  I fixed the issue of some headers not being click-able and hence sorting the data.

                                   

                                  In the link give, there is a section where it says what to do if some don't work.

                                   

                                  Adding in the class manually to the table header in the code view:

                                   

                                            <tr>

                                              <th width="63" scope="col">Code</th>

                                              <th width="265" scope="col">Title</th>

                                              <th width="185" scope="col">Author</th>

                                              <th width="110" scope="col" class="sorttable_alpha">Description</th>

                                              <th width="273" scope="col" class="sorttable_alpha">Topic</th>

                                              <th width="141" scope="col" class="sorttable_alpha">Location</th>

                                            </tr>

                                  ------------------------------------------------------------------------------------------ ---------------

                                  As to the search function, I followed this tutorial:

                                  PHP MySQL Search using Dreamweaver CS 5.5 - YouTube

                                   

                                  Thank you for your help!

                                  • 14. Re: Database Record Filtering
                                    Rob Hecker2 Adobe Community Professional & MVP

                                    Bregent, just FYI,

                                     

                                    After applying the javascript sorter to a variety of table displays, I have discovered that it fails to work if the number of rows is around 1,000 or more. And there is a point where going back and letting MySQL do the resort is much faster. That doesn't surprise me because it's also generally faster to run a query through MySQL than to have PHP hunt through an array of the same data.

                                     

                                    But for small table sets, it's quite nice.

                                    • 15. Re: Database Record Filtering
                                      bregent Most Valuable Participant

                                      Thanks Rob, good to know and that makes sense. I've probably only used it on tables < 100 rows.