11 Replies Latest reply: Sep 10, 2007 12:31 PM by Newsgroup_User RSS

    Advanced Search Form - PART II

    oicram Community Member
      In the insert page: People introduce the birth date of the dog.

      In the search page:
      I have a pulldown menu with the values "Baby"; "Young"; "Adult" and finally, "Oldies".
      For each of this values I will need to have a range:

      Baby - until 6 months.
      Young - From 6 Months until 3 years.
      Adult - From more then 3 until 8 years.
      Oldies - more then 8 years.

      I have a SQL script that gives me the Age in years:
      SELECT CURDATE(), (YEAR(CURDATE())-YEAR(data_nasc_an)) - (RIGHT(CURDATE(),5)<RIGHT(data_nasc_an,5)) AS Age

      The problem is that if the little dog as less then 12 months he will return 0. This will be not a big issue, but dogs grow a lot in the first year, so we cannot put a dog with 2 months and a dog with 11 months like being the same.

      1) How can we show the age in a way that the first year can be show in months?

      2) How can we, for this case of Age, get a range of values associated with the combo box?

      Any feedback on this please?

      Thanks in advance
        • 1. Re: Advanced Search Form - PART II
          Newsgroup_User Community Member
          oicram wrote:
          > In the search page:
          > I have a pulldown menu with the values "Baby"; "Young"; "Adult" and finally,
          > "Oldies".
          > For each of this values I will need to have a range:
          >
          > Baby - until 6 months.
          > Young - From 6 Months until 3 years.
          > Adult - From more then 3 until 8 years.
          > Oldies - more then 8 years.

          This is one of those cases where you will need to create a control
          structure to choose the SQL query depending on the value of your
          dropdown. You could do something like this:

          switch($_POST['age']) {
          case 'Baby':
          $sql = 'SELECT * FROM dogs WHERE data_nasc_an < SUBDATE(NOW(),
          INTERVAL 6 MONTH)';
          break;
          case 'Young':
          $sql = 'SELECT * FROM dogs WHERE data_nasc_an > SUBDATE(NOW(),
          INTERVAL 6 MONTH) AND data_nasc_an < SUBDATE(NOW(),
          INTERVAL 3 YEAR)';
          break;
          case 'Adult':
          $sql = 'SELECT * FROM dogs WHERE data_nasc_an > SUBDATE(NOW(),
          INTERVAL 3 YEAR) AND data_nasc_an < SUBDATE(NOW(),
          INTERVAL 8 YEAR)';
          break;
          case 'Oldies':
          $sql = 'SELECT * FROM dogs WHERE data_nasc_an > SUBDATE(NOW(),
          INTERVAL 8 YEAR)';
          }

          --
          David Powers, Adobe Community Expert
          Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
          Author, "PHP Solutions" (friends of ED)
          http://foundationphp.com/
          • 2. Advanced Search Form - PART II
            oicram Community Member
            Thanks. Its very usefull, also for possible future situations where we need to control different querys.
            But, where should we put this code? In the head and then we put the value of the pull-down equal to the case name here?

            And... my query is right now a little bit more complicated then Select * From and it looks like this right now:

            SELECT CURDATE(), (YEAR(CURDATE())-YEAR(data_nasc_an)) - (RIGHT(CURDATE(),5)<RIGHT(data_nasc_an,5)) AS idade, id_an, tipo_an, nome_pt_an, sexo_pt_an, est_animal, alt_cm_an
            FROM animal
            WHERE alt_cm_an BETWEEN varaltura_min AND varaltura_max AND tipo_an LIKE vartipo AND sexo_pt_an LIKE varsexo AND est_animal LIKE varestado

            Should I need to put this query on every 'case' showed in your last post?

            Thanks... again... :)
            • 3. Re: Advanced Search Form - PART II
              Newsgroup_User Community Member
              oicram wrote:
              > But, where should we put this code? In the head and then we put the
              value of
              > the pull-down equal to the case name here?

              Yes.

              --
              David Powers, Adobe Community Expert
              Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
              Author, "PHP Solutions" (friends of ED)
              http://foundationphp.com/
              • 4. Re: Advanced Search Form - PART II
                oicram Community Member
                I will test this right away.

                But since this is a multiple search option, how can this drop-down with age values be related with the other query already made?

                Thanks.
                • 5. Advanced Search Form - PART II
                  oicram Community Member
                  I'm getting the following error:

                  Notice: Undefined index: idade in /home/cantinho/www/www/admin/cantinho/encontrar_animal.php on line 86

                  And this line have:
                  switch($_POST['idade']) {


                  I have tried to use if (isset($_POST['idade'])) to try to avoid this error, like this:

                  <?php
                  if (isset($_POST['idade'])) {
                  switch($_POST['idade']) {
                  case 'bebe':
                  $sql = 'SELECT * FROM dogs WHERE data_nasc_an < SUBDATE(NOW(),
                  INTERVAL 6 MONTH)';
                  break;
                  case 'jovem':
                  $sql = 'SELECT * FROM dogs WHERE data_nasc_an > SUBDATE(NOW(),
                  INTERVAL 6 MONTH) AND data_nasc_an < SUBDATE(NOW(),
                  INTERVAL 3 YEAR)';
                  break;
                  case 'adulto':
                  $sql = 'SELECT * FROM dogs WHERE data_nasc_an > SUBDATE(NOW(),
                  INTERVAL 3 YEAR) AND data_nasc_an < SUBDATE(NOW(),
                  INTERVAL 8 YEAR)';
                  break;
                  case 'velhotes':
                  $sql = 'SELECT * FROM dogs WHERE data_nasc_an > SUBDATE(NOW(),
                  INTERVAL 8 YEAR)';
                  }
                  }
                  else {
                  echo '$_POST[idade] not valid or something the point is, I have no idea what to put here :(';
                  }

                  but no luck either.


                  My drop-down menu are defined like this:

                  <select name="idade" id="idade">
                  <option value="%" <?php if(isset($_POST['idade']) && $_POST['idade'] == "%") echo 'selected="selected"'; ?>>Qualquer</option>
                  <option value="bebe" <?php if(isset($_POST['idade']) && $_POST['idade'] == "bebe") echo 'selected="selected"'; ?>>Bebé</option>
                  <option value="jovem" <?php if(isset($_POST['idade']) && $_POST['idade'] == "jovem") echo 'selected="selected"'; ?>>Jovem</option>
                  <option value="adulto"<?php if(isset($_POST['idade']) && $_POST['idade'] == "adulto") echo 'selected="selected"'; ?>>Adulto</option>
                  <option value="velhote" <?php if(isset($_POST['idade']) && $_POST['idade'] == "velhote") echo 'selected="selected"'; ?>>Velhote</option>

                  So it should not give this error.

                  This will not be a big issue since its only a notice, but the query does not work either - When I press Submit he gives me all always all ages - so maybe its related somehow?


                  Thanks again.


                  ADD-ONS to this post :

                  I have test the querys in a new f"aketest" recordset but they are not accurate.
                  I have change the querys for this:
                  Select CURDATE(), (YEAR(CURDATE())-YEAR(data_nasc_an)) - (RIGHT(CURDATE(),5)<RIGHT(data_nasc_an,5)) as Idade FROM animal HAVING Idade < 3

                  It does not solve the months problem, but It was only for testing proposes, and this query worked ok in the fakerecorset BUT the form stills ignore the value of the drop-down because no filter was made.

                  So, before que mysql querys, the drop-down in the form must be connected with the values of the database, and that's not the case right now.

                  Anyone please?...
                  • 6. Re: Advanced Search Form - PART II
                    Newsgroup_User Community Member
                    oicram wrote:
                    > But since this is a multiple search option, how can this drop-down with age values be related with the other query already made?

                    If you have many options in your query, you need to create a series of
                    conditional statements to build the whole query. In simplified terms:

                    $sql = 'SELECT * FROM DOGS WHERE ';
                    if (isset($_POST['age'])) {
                    switch($_POST['age]) {
                    case 'baby':
                    $sql .= 'age < SUBDATE(NOW(), INTERVAL 6 MONTH) ';
                    break;
                    case 'young':
                    $sql .= 'age > SUBDATE(NOW(), INTERVAL 6 MONTH)
                    AND age < SUBDATE(NOW(), INTERVAL 2 YEAR) '
                    break;
                    case etc....
                    }
                    }
                    else {
                    $sql .= 'age < NOW() ';
                    }
                    if (next condition) {
                    $sql .= ' continue building the sql here';
                    }


                    --
                    David Powers, Adobe Community Expert
                    Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
                    Author, "PHP Solutions" (friends of ED)
                    http://foundationphp.com/
                    • 7. Re: Advanced Search Form - PART II
                      oicram Community Member
                      So that's why I'm getting the error?

                      • 8. Re: Advanced Search Form - PART II
                        oicram Community Member
                        I have all my recorset defined with variables and a lot of your help here:

                        http://www.cantinho.org/admin/cantinho/encontrar_animal_v2.php

                        ::::::::::::here is the query already defined:::::::::::::::::::::::
                        mysql_select_db($database_conn_db_cantinho, $conn_db_cantinho);
                        $query_rs_procurar_animais = sprintf("SELECT CURDATE(), (YEAR(CURDATE())-YEAR(data_nasc_an)) - (RIGHT(CURDATE(),5)<RIGHT(data_nasc_an,5)) AS idade, id_an, tipo_an, nome_pt_an, sexo_pt_an, est_animal, alt_cm_an FROM animal WHERE alt_cm_an BETWEEN %s AND %s AND tipo_an LIKE %s AND sexo_pt_an LIKE %s AND est_animal LIKE %s", GetSQLValueString($varaltura_min_rs_procurar_animais, "int"),GetSQLValueString($varaltura_max_rs_procurar_animais, "int"),GetSQLValueString($vartipo_rs_procurar_animais, "text"),GetSQLValueString($varsexo_rs_procurar_animais, "text"),GetSQLValueString($varestado_rs_procurar_animais, "text"));
                        :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: ::::::::::::::::::::::::::::::::::::::::::::::::::::


                        The LAST option in my search is the age, but as you can see, the query is defined with a lot of variables and that $max and $min and everthing, and It will be a big problem IF I must delete all that to make a new splited query.

                        :S

                        To make the age work do we really need to split all that is made until now?!!

                        If I change the value of the mysql function to calculate age so she can give me in days an not in years, something like this:

                        select datediff(curdate(), data_nasc_an) as age

                        Then can I use the same between solution for an interval in days... ?

                        I'm sorry for all my questions, but I'm developing this alone, so I have no one to ask about it, otherwise I will not ask all things to you . I'm really sorry, and I really hope that you can help me out.
                        • 9. Re: Advanced Search Form - PART II
                          Newsgroup_User Community Member
                          oicram wrote:
                          > ::::::::::::here is the query already defined:::::::::::::::::::::::

                          The problem with your existing query is that it selects the age of all
                          dogs that match the size, type, sex, and whatever est_animal represents.
                          There is no way to select the animal on the basis of its age.

                          > The LAST option in my search is the age, but as you can see, the query is
                          > defined with a lot of variables and that $max and $min and everthing, and It
                          > will be a big problem IF I must delete all that to make a new splited query.

                          Very often you find that you need to change your approach, and splitting
                          the query can be the most effective way of doing it. However, since you
                          want to select dogs on the basis of age, why don't you do it the same
                          way as with height?

                          > If I change the value of the mysql function to calculate age so she can give
                          > me in days an not in years, something like this:
                          >
                          > select datediff(curdate(), data_nasc_an) as age

                          Yes, that's a reasonable approach, but not quite the way you are
                          suggesting.

                          Build the drop-down menu in the same way as before:

                          <select name="idade" id="idade">
                          <option value="0,183">Baby</option>
                          <option value="184,1068">Young</option>
                          <option value="1069,2920">Adult</option>
                          <option value="2921,50000">Oldies</option>
                          </select>

                          Get the min and max as before:

                          if (isset($_POST['idade'])) {
                          $temp_age = explode(',', $_POST['idade']);
                          $min_age = $temp_age[0];
                          $max_age = $temp_age[1];
                          }

                          You can then use another BETWEEN AND condition in your WHERE clause.

                          > I'm sorry for all my questions, but I'm developing this alone, so I have no
                          > one to ask about it, otherwise I will not ask all things to you .

                          I work on my own, too. As you develop more websites and databases, you
                          will gain experience in solving problems. You'll often find there are
                          several ways of doing the same thing. The secret is to experiment, and
                          find which solutions give you the best results. I find I constantly
                          change and improve the way I do things.

                          --
                          David Powers, Adobe Community Expert
                          Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
                          Author, "PHP Solutions" (friends of ED)
                          http://foundationphp.com/
                          • 10. Re: Advanced Search Form - PART II
                            oicram Community Member
                            Yupiiii!!! Doing doing doing!!! --> Signs of happyness. :)

                            Sir David Powers,

                            I have applied the same method that we have made for size to age.
                            Before reading your last post. :)

                            I have add in the SQL Query:

                            datediff(curdate(), data_nasc_an) AS age_in_days

                            and,

                            two new variables - maxage and minage with the Run-Time Values $maxage and $minage respectivly.

                            and add also at the end:

                            HAVING idade_dias BETWEEN varidade_min AND varidade_max
                            (if I put WHERE I get an error because I'm using idade_dias, so I put HAVING)

                            Then add a new IF like the one you do:

                            if (isset($_POST['age'])) {
                            $temp = explode(',', $_POST['age']);
                            $minage= $temp[0];
                            $maxage= $temp[1];
                            }

                            I thinks this divides a string or something? I'd would love to know...

                            And at the end the select values with the respective way to preserve the option in the search:
                            <option value="2912,40000" <?php if(isset($_POST['age']) && $_POST['age'] == "2912,40000") echo 'selected="selected"'; ?>


                            Once again, thanks a lot for your help.
                            • 11. Re: Advanced Search Form - PART II
                              Newsgroup_User Community Member
                              oicram wrote:
                              > if (isset($_POST['age'])) {
                              > $temp = explode(',', $_POST['age']);
                              > $minage= $temp[0];
                              > $maxage= $temp[1];
                              > }
                              >
                              > I thinks this divides a string or something? I'd would love to know...

                              It converts a string into an array:

                              http://www.php.net/manual/en/function.explode.php

                              --
                              David Powers, Adobe Community Expert
                              Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
                              Author, "PHP Solutions" (friends of ED)
                              http://foundationphp.com/