5 Replies Latest reply: Sep 5, 2012 4:00 PM by David_Powers RSS

    Filter records

    Smitch1581 MeganK

      Hi,

       

      I have a page that is hooked up to a database using php. Its a simple listings page. You can see the page here http://www.animalhealthadvisor.co.uk/animal-health-jobs-recruitment-vacancies.php. All is working well, but i would really like to add a filter/search option on the left hand side of the page.

       

      The database in phpMyAdmin is called "jobs", and one of the fields in the database that i would like to add a filter on is called "Region" This is the name of the field in the database that i would like to be able to hide the other listings based on what they choose from a dropdown menu. You can see what i am trying to do here http://www.animalhealthadvisor.co.uk/animal-health-jobs-recruitment-vacanciesSEARCH.php. In the drop down menu i have dynamically populated its values from the database and have added a button to start the search

       

      I am really stuck here and am not sure how i can fix this. Any help would be greatly appreciated.

       

      I have realised thet the dropdown menu on the second link i supplied returns for Example ("London") more than once and think i have to create a relational database for this. But i was hoping to try and crack the search problem first.

       

      If you require any more information, please let me know if it wasn't clear enough, or if it is easier i could supply some zip files to have a look at..

       

      Thanx again in advance.

        • 1. Re: Filter records
          David_Powers CommunityMVP

          The way that you filter by region is by adding a WHERE clause to your SQL query.

           

          You need to set the form's method to GET. You can then capture the value of $_GET['region'] and use it in the SQL query. Because the value is coming from a URL, you need to make sure the value is sanitized before being used in the SQL query. If you're using Dreamweaver's server behaviors, Dreamweaver builds the code for you.

           

          In the Advanced Recordset dialog box, add "WHERE table_name.region = var1" (without the quotes) at the end of the SQL query, replacing "table_name" with the actual name of the table.

           

          Click the plus button next to Variables to open the Edit Variables dialog box.

           

          In the Name field, type var1.

          Set Type to Text.

          Set Default value to -1 or to the name of the default region you want to show

          In the Runtime value field, type $_GET['region']

          • 2. Re: Filter records
            Smitch1581 MeganK

            That is fantastic thank you so much. That worked a treat and you made it look so easy!

             

            When you say enter -1, this hides all of them, but you said change it to the name of the default region. Is there away to show all regions as default?

             

            Thank you so much again.

            • 3. Re: Filter records
              David_Powers CommunityMVP

              Unfortunately, Dreamweaver's server behaviors aren't that sophisticated. To show all regions, you need a different SQL query. It involves changing the code generated by Dreamweaver. Once you do so, the server behavior is no longer recognized by DW, but the code should be correctly interpreted by the web server.

               

              The basic code would look something like this:

               

              $var1_getVacancies = "-1";

              if (isset($_GET[region'])) {

                $var1_getVacancies = $_GET['region'];

              }

              mysql_select_db($database_connAdmin, $connAdmin);

               

              // If $_GET['region'] hasn't been set, get all results.

              // Otherwise use the WHERE clause

               

              if ($var1_getVacancies == -1) {

                  $query_getVacancies = "SELECT * FROM vacancies";

              } else {

                  $query_getVacancies = sprintf("SELECT * FROM vacancies  WHERE region = %s", GetSQLValueString($var1_getVacancies, "text"));

              }

              • 4. Re: Filter records
                Smitch1581 MeganK

                Thanks for confirming that for me. Could have spent hours trying!

                 

                With regards to the code you supplied. Do i enter that in the advanced recordset or i do i modify this in code view? I tried in the advanced section but it throws errors.

                 

                Where you have wrote 'vacancies' and 'getVacancies', is that the table name? Would i replace that with 'jobs'?

                 

                Thanks again for all your help so far.

                • 5. Re: Filter records
                  David_Powers CommunityMVP

                  You modify the code in Code view. Look at the existing code in your page and find similar patterns. Dreamweaver uses the name of the recordset to create variables. I have used "getVacancies" as an example of the name of the recordset.

                   

                  The values in the mysql_select_db() function will depend on the name of your connection.

                   

                  The if... else construction creates two different versions of the SQL for the recordset. Adapt them from your existing code.