31 Replies Latest reply: Mar 14, 2012 10:54 AM by Jonathan Fortis RSS

    advanced search recordset HELP

    Jonathan Fortis Community Member

      I have tried following the tutorial here http://help.adobe.com/en_US/dreamweaver/cs/using/WScbb6b82af5544594822510a94ae8d65-78aaa.h tml

       

      the simple one works fine but i try and follow the advanced and it doesnt show how to make the correct SQL statement using cs5 in php

       

      i am trying to search 3 different criteria from 3 text feilds and display either all or one of the seatch results.

       

      the search page form is

       

      <form id="Jobtitle" name="Jobtitle" method="get" action="../job-description.php">

                <p>

                  <input name="JobTitle" type="text" class="textfeilds" value="Job Title" size="32" />

                  <label for="Location"></label>

                  <input name="Location" type="text" value="Location" size="32" />

                  <label for="Salary"></label>

                  <input name="Salary" type="text" class="textfeilds" value="Salary" size="32" />

                </p>

                <p align="right">

                  <input type="image" src="../images/jobsearch-button.png" width="120" height="17" border="0" value="Submit" alt="submit" usemap="#Map" />

                  <map name="Map" id="Map">

                    <area shape="rect" coords="1,0,67,16" href="#" />

                  </map>

                </p>

            </form>

       

      the results page is

       

      SELECT think_jobsearch.tk_job_title, think_jobsearch.tk_job_location, think_jobsearch.tk_job_salary

      FROM think_jobsearch

       

       

      then i dont know where to go from there? with regards to varialble

       

       

      can anyone help?

        • 1. Re: advanced search recordset HELP
          Jonathan Fortis Community Member

          i have since tried this

           

          SELECT think_jobsearch.tk_job_title, think_jobsearch.tk_job_location, think_jobsearch.tk_job_salary

          FROM think_jobsearch

          WHERE think_jobsearch.tk_job_title = JT AND think_jobsearch.tk_job_location = JL

           

          and the variables are

           

          NAME; JT

          TYPE: Text

          Default Value: %

          Runtime Value: $_REQUEST["think_jobsearch.tk_job_title"]

           

          Name JL:

          Type: Text

          Default Value: %

          Runtime Value: $_REQUEST["think_jobsearch.tk_job_location"]

           

           

          THis doesnt work. was just trying in hope

          • 2. Re: advanced search recordset HELP
            bregent MVP

            >SELECT think_jobsearch.tk_job_title, think_jobsearch.tk_job_location, think_jobsearch.tk_job_salary

            >FROM think_jobsearch

            >WHERE think_jobsearch.tk_job_title = JT AND think_jobsearch.tk_job_location = JL

             

            That won't work. You can't insert variables (JT and JL) directly into a text string. SQL can't evaluate those. You need to use a method using placeholder values that get replace using sprintf() or use concatenation.

             

            >Runtime Value: $_REQUEST["think_jobsearch.tk_job_title"]

             

            You have no REQUEST object variable with that name. That's the name of a database column. You need to use the name of the appropriate form field. Also, I believe you should not even be using _REQUEST anymore. Use $_GET instead.

             

            >i am trying to search 3 different criteria from 3 text

            >feilds and display either all or one of the seatch results.

             

            Are all fields optional or required?

             

            >THis doesnt work. was just trying in hope

             

            You need to have a fundamental understanding of SQL and PHP. Trial and error just doesn't work.

            • 3. Re: advanced search recordset HELP
              Jonathan Fortis Community Member

              all three feilds are optional.

               

              i was trying to follow the tutorial but it didnt show how using php,

              • 4. Re: advanced search recordset HELP
                bregent MVP

                >all three feilds are optional.

                 

                OK. But now, how to you want to combine the logic?  If a user enters 'Mananger' for job title, and 'Houston' for location, do you want to return all jobs in Houston and all jobs for Manager?  Or, only Manager jobs in Houston?

                 

                >i was trying to follow the tutorial but it didnt show how using php,

                 

                As I have said before, DW behaviors will only get you so far. At some point, you need to learn the language you are working with. Learning even just the fundamentals would have saved you so much time by now. Why not stop development for a bit and pick up one of David Powers PHP books. Investing even just a few weeks will make you much more productive.

                • 5. Re: advanced search recordset HELP
                  osgood_ MVP

                  bregent wrote:

                   

                  Why not stop development for a bit and pick up one of David Powers PHP books. Investing even just a few weeks will make you much more productive.

                   

                   

                  I'm not aware that David covers advanced searches in any of his books bregent? Maybe I missed the section somewhere or havent got the one he does cover them in.

                  • 6. Re: advanced search recordset HELP
                    bregent MVP

                    You misunderstood Osgood. I'm not talking about how to learn to use DW advanced searches - I'm talking about learning the fundamentals of the programming language you are working with. If you want to be able to write or edit scripts, you need to be able to al least look at a script and understand what it is doing. Until you understand the syntax, function use, loops and branching, working with any scripting language will be a struggle. Having said that, I'm not sure which if any of Davids books are appropriate. But certainly a beginners book/tutorial for PHP is in order.

                    • 7. Re: advanced search recordset HELP
                      Jonathan Fortis Community Member

                      well maybe i should do that. I will have to look at what book to get to learn the fundumentals. thanks

                      • 8. Re: advanced search recordset HELP
                        Jonathan Fortis Community Member

                        i am looking online for sql fundumentals but my first question was i was trying to follow the online tutorial on the Adobe website but it was not very helpful. This is surely a common search practice now for website so thought that Adobe would have made it a bit easier.

                        • 9. Re: advanced search recordset HELP
                          osgood_ MVP

                          have you tried this combination:

                           

                          SELECT tk_job_title, tk_job_location, tk_job_salary

                          FROM think_jobsearch

                          WHERE tk_job_title = %s AND job_location = %s

                           

                          Plus what are your variable setup?

                           

                          If I go by the tutorial you are reffering to I get something like below:

                           

                          $varJobTitle_conJobResults = "%";

                          if (isset($_GET["JobTitle"])) {

                            $varJobTitle_conJobResults = $_GET["JobTitle"];

                          }

                          $varLocation_conJobResults = "%";

                          if (isset($_GET["Location"])) {

                            $varLocation_conJobResults = $_GET["Location"];

                          }

                           

                          in the above my form field names are 'JobTitle' and 'Location' and the variables are 'varJobTitle' and 'varLocation'

                           

                           

                          Admittedly I too have been looking for an 'advanced search' tutorial' just to do for the exercise and I'm afraid I have never come across one or come across one in any of the phps books I've got. I'm with you on this one.

                           

                          Also I'd keep the name of the form fields the same as the name of your database columns. Less confusion

                          • 10. Re: advanced search recordset HELP
                            Jonathan Fortis Community Member

                            my variables are whats confusing me i have set up these settings in dreamweaver advanced

                             

                            NAME; JT

                            TYPE: Text

                            Default Value: %

                            Runtime Value: $_REQUEST["think_jobsearch.tk_job_title"]

                             

                            Name JL:

                            Type: Text

                            Default Value: %

                            Runtime Value: $_REQUEST["think_jobsearch.tk_job_location"]

                             

                            thanks very much, i will try what you have suggested

                            • 11. Re: advanced search recordset HELP
                              bregent MVP

                              >Default Value: %

                               

                              The default value is the value supplied if the user does not enter anything. The wildcard character only works with the SQL like predicate.

                               

                              >Runtime Value: $_REQUEST["think_jobsearch.tk_job_title"]

                               

                              Runtime Value tells DW where to get the value to use in the SQL WHERE clause at runtime. As mentioned earlier, this should be the name of the form field where the data is coming from, not the database columns.

                               

                               

                              And you still haven't answered my earlier question below which is critical if you want to get the right results:

                               

                              "OK. But now, how to you want to combine the logic?  If a user enters 'Mananger' for job title, and 'Houston' for location, do you want to return all jobs in Houston and all jobs for Manager?  Or, only Manager jobs in Houston?"

                              • 12. Re: advanced search recordset HELP
                                Jonathan Fortis Community Member

                                If a user enters 'Mananger' for job title, and 'Houston' for location, do you want to return all jobs in Houston and all jobs for Manager?  Or, only Manager jobs in Houston?"

                                 

                                all the jobs for manager and all the jobs in houston

                                 

                                also they can jobs search jobs in houston and leave others blank then it will return all jobs in houston

                                 

                                OR

                                 

                                can they do both so if they leave the others blank it will just show results for the one searched criteria


                                • 13. Re: advanced search recordset HELP
                                  bregent MVP

                                  >all the jobs for manager and all the jobs in houston

                                   

                                  Then the conditions in the WHERE clause need to be separated with an OR, not an AND. Use Osgoods SQL as an example, but change the AND to an OR:

                                   

                                  SELECT tk_job_title, tk_job_location, tk_job_salary

                                  FROM think_jobsearch

                                  WHERE tk_job_title = %s OR job_location = %s

                                   

                                  >can they do both so if they leave the others blank

                                  >it will just show results for the one searched criteria

                                   

                                  Sure. One way is to set the default values in the advanced recordset editor to a value that will never occur in the data.

                                   

                                  Default Value: XXXX

                                  • 14. Re: advanced search recordset HELP
                                    Jonathan Fortis Community Member

                                    ok but what are the variables i should use, ?

                                     

                                    $varJobTitle_conJobResults = "%";

                                    if (isset($_GET["JobTitle"])) {

                                      $varJobTitle_conJobResults = $_GET["JobTitle"];

                                    }

                                    $varLocation_conJobResults = "%";

                                    if (isset($_GET["Location"])) {

                                      $varLocation_conJobResults = $_GET["Location"];

                                    }

                                     

                                    if i am using the advanced recordset builder in dreamweaver or should i not use that?

                                    • 15. Re: advanced search recordset HELP
                                      osgood_ MVP

                                      Open the 'advanced' record set behaviour panel for the results page.

                                       

                                      Type into the sql box:

                                       

                                      SELECT tk_job_title, tk_job_location, tk_job_salary FROM think_jobsearch

                                      WHERE tk_job_title LIKE var_tk_job_title OR tk_job_location LIKE var_tk_job_location OR tk_job_salary LIKE var_tk_job_salary

                                       

                                      Click + on the Variables panel and add:

                                       

                                      Name: var_tk_job_title

                                      Type: choose 'text' from the drop down

                                      Default value: %

                                      Runtime value: $_GET["tk_job_title"]

                                       

                                      click + again and add:

                                       

                                      Name: var_tk_job_location

                                      Type: choose 'text' from the drop down

                                      Default value: %

                                      Runtime value: $_GET["tk_job_location"]

                                       

                                      click + again and add:

                                       

                                      Name: var_tk_job_salary

                                      Type: choose 'text' from the drop down

                                      Default value: %

                                      Runtime value: $_GET["tk_job_salary"]

                                       

                                      Click ok. That will automatically create your variable.

                                       

                                      IMPORTANT: Amend your form field 'names' to match the variable names (as shown below)

                                       

                                       

                                      <form id="Jobtitle" name="Jobtitle" method="get" action="../job-description.php">

                                                <p>

                                                  <input name="tk_job_title" type="text" class="textfeilds" value="Job Title" size="32" />

                                                  <label for="Location"></label>

                                                  <input name="tk_job_location" type="text" value="Location" size="32" />

                                                  <label for="Salary"></label>

                                                  <input name="tk_job_salary" type="text" class="textfeilds" value="Salary" size="32" />

                                                </p>

                                                <p align="right">

                                                  <input type="image" src="../images/jobsearch-button.png" width="120" height="17" border="0" value="Submit" alt="submit" usemap="#Map" />

                                                  <map name="Map" id="Map">

                                                    <area shape="rect" coords="1,0,67,16" href="#" />

                                                  </map>

                                                </p>

                                            </form>

                                      • 16. Re: advanced search recordset HELP
                                        Jonathan Fortis Community Member

                                        ok that brilliant but have tried it but it doesnt return any results where i know there should be, i take it the results should just be

                                         

                                        <?php echo $row_Recordset1['tk_job_title']; ?>

                                         

                                         

                                        placed on the page?

                                        • 17. Re: advanced search recordset HELP
                                          bregent MVP

                                          You need to show us the complete code you are currently having problems with.

                                          • 18. Re: advanced search recordset HELP
                                            Jonathan Fortis Community Member

                                            <?php require_once('Connections/jobs.php'); ?>

                                            <?php

                                            if (!function_exists("GetSQLValueString")) {

                                            function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

                                            {

                                              if (PHP_VERSION < 6) {

                                                $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

                                              }

                                             

                                             

                                              $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

                                             

                                             

                                              switch ($theType) {

                                                case "text":

                                                  $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

                                                  break;   

                                                case "long":

                                                case "int":

                                                  $theValue = ($theValue != "") ? intval($theValue) : "NULL";

                                                  break;

                                                case "double":

                                                  $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

                                                  break;

                                                case "date":

                                                  $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

                                                  break;

                                                case "defined":

                                                  $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

                                                  break;

                                              }

                                              return $theValue;

                                            }

                                            }

                                             

                                             

                                            $var_tk_job_title_Recordset1 = "%";

                                            if (isset($_GET["tk_job_title"])) {

                                              $var_tk_job_title_Recordset1 = $_GET["tk_job_title"];

                                            }

                                            $var_tk_job_location_Recordset1 = "%";

                                            if (isset($_GET["tk_job_location"])) {

                                              $var_tk_job_location_Recordset1 = $_GET["tk_job_location"];

                                            }

                                            $var_tk_job_salary_Recordset1 = "%";

                                            if (isset($_GET["tk_job_salary"])) {

                                              $var_tk_job_salary_Recordset1 = $_GET["tk_job_salary"];

                                            }

                                            mysql_select_db($database_hostprop, $hostprop);

                                            $query_Recordset1 = sprintf("SELECT tk_job_title, tk_job_location, tk_job_salary FROM think_jobsearch WHERE tk_job_title LIKE %s OR tk_job_location LIKE %s OR tk_job_salary LIKE %s", GetSQLValueString($var_tk_job_title_Recordset1, "text"),GetSQLValueString($var_tk_job_location_Recordset1, "text"),GetSQLValueString($var_tk_job_salary_Recordset1, "text"));

                                            $Recordset1 = mysql_query($query_Recordset1, $hostprop) or die(mysql_error());

                                            $row_Recordset1 = mysql_fetch_assoc($Recordset1);

                                            $totalRows_Recordset1 = mysql_num_rows($Recordset1);

                                            ?>

                                             

                                             

                                            then the results should diplay here

                                             

                                                    <table border="1">

                                                      <tr>

                                                        <td>tk_job_title</td>

                                                        <td>tk_job_location</td>

                                                        <td>tk_job_salary</td>

                                                      </tr>

                                                      <?php do { ?>

                                                        <tr>

                                                          <td><?php echo $row_Recordset1['tk_job_title']; ?></td>

                                                          <td><?php echo $row_Recordset1['tk_job_location']; ?></td>

                                                          <td><?php echo $row_Recordset1['tk_job_salary']; ?></td>

                                                        </tr>

                                                        <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>

                                                    </table>

                                            • 19. Re: advanced search recordset HELP
                                              Jonathan Fortis Community Member

                                              ok that does seem to work but when running test when they search for security officer only that job title is returned. even if there is a security guard also as a job. what do i have to to for the search to pick out any word in the search?

                                              • 20. Re: advanced search recordset HELP
                                                bregent MVP

                                                >what do i have to to for the search to pick out any word in the search?

                                                 

                                                Please clarify this by supplying examples of the data, examples of what the user might enter, and what results you would want based on that criteria.

                                                • 21. Re: advanced search recordset HELP
                                                  Jonathan Fortis Community Member

                                                  example: in the tk_job_title the user may type in security or security guard. Currently the results will either show only the exact search result either security or security guard. i want it to display the results for both all job with security in the and guard.

                                                   

                                                  or in the tk_job_location the user may type in Orlando or Orlando Florida, again i want results to display all locations in Orlando and all Results in Florida

                                                   

                                                  or tk_job_salary. again they may put in 30,0000 or Any and it will show results for both

                                                  • 22. Re: advanced search recordset HELP
                                                    bregent MVP

                                                    Standard SQL doesn't work like that. You either need to parse the search expression into separate words and then build a dynamically SQL statement, or alter your database to enable Full-Text Searching.

                                                    • 23. Re: advanced search recordset HELP
                                                      Jonathan Fortis Community Member

                                                      so what you are saying is the way i have it currently set up it wont work like that?

                                                       

                                                      >alter your database to enable Full-Text Searching.

                                                       

                                                      this is something i do within the database itself? or is this done on the MySQL statement?

                                                       


                                                      • 24. Re: advanced search recordset HELP
                                                        bregent MVP

                                                        >this is something i do within the database itself?


                                                        Yes, you would need to alter the database tables.

                                                         

                                                        >or is this done on the MySQL statement?

                                                         

                                                        There are many types of SQL statements. It would not be done in a SELECT statement, if that's what you mean.

                                                        • 25. Re: advanced search recordset HELP
                                                          Jonathan Fortis Community Member

                                                          Well if i do changed the tables do i need to change the SQL aswell??

                                                           

                                                           

                                                          got any pointers?

                                                           

                                                          what would you advise using. between either changing the database or adding SQL?

                                                          • 26. Re: advanced search recordset HELP
                                                            Jonathan Fortis Community Member

                                                            Would it be best to use the database full text searching or add an sqk statement?

                                                            • 27. Re: advanced search recordset HELP
                                                              Jonathan Fortis Community Member

                                                              i have looked at my database options for FULLTEXT in the TYPE but it doesnt have it?

                                                               

                                                              i am using

                                                              phpMyAdmin - 2.11.9.5

                                                              • 28. Re: advanced search recordset HELP
                                                                bregent MVP

                                                                phpMyAdmin - 2.11.9.5

                                                                 

                                                                I'm not sure. I know that you need at least version 3.23.23 of MySQL, but I'm not sure if the version of phpMyAdmin is related to the MySQL version. I would say that the version you have is probably too old.

                                                                • 29. Re: advanced search recordset HELP
                                                                  Jonathan Fortis Community Member

                                                                  i have version

                                                                   

                                                                  MySQL client version: 4.1.25

                                                                   

                                                                  what would you recommend use the database full text searching or add an sqk statement?

                                                                  • 30. Re: advanced search recordset HELP
                                                                    bregent MVP

                                                                    >what would you recommend use the database full text searching

                                                                     

                                                                    I don't use MySQL and am not too familiar with its full text search capabities. Because and index you add can adversely affect performance, you need to do a full analysis of your needs and get a more complete understanding of how it works.

                                                                     

                                                                    >or add an sqk statement?

                                                                     

                                                                    You can't simply add a SQL statement to accomplish what you want. You need to build a search function using a combination of PHP and SQL. To do that, you must first have a very good working knowledge of both PHP scripting and SQL SELECT statements.

                                                                    • 31. Re: advanced search recordset HELP
                                                                      Jonathan Fortis Community Member

                                                                      yes i understand you need to combine both. Ok well i will have a look out there to see if anyone else can point me in the right direction

                                                                       

                                                                      there are a few web pages out there i have found so will try and go through them

                                                                       

                                                                      thanks