Skip navigation
Currently Being Moderated

advanced search recordset HELP

Feb 18, 2012 10:38 PM

I have tried following the tutorial here http://help.adobe.com/en_US/dreamweaver/cs/using/WScbb6b82af5544594822 510a94ae8d65-78aaa.html

 

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?

 
Replies
  • Currently Being Moderated
    Feb 19, 2012 3:06 PM   in reply to Jonathan Fortis

    >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.

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 20, 2012 9:33 AM   in reply to Jonathan Fortis

    >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.

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 20, 2012 9:53 AM   in reply to bregent

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 20, 2012 12:27 PM   in reply to osgood_

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 23, 2012 2:43 AM   in reply to Jonathan Fortis

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 23, 2012 9:36 AM   in reply to Jonathan Fortis

    >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?"

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 23, 2012 10:47 PM   in reply to Jonathan Fortis

    >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

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 24, 2012 7:59 AM   in reply to Jonathan Fortis

    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>

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 25, 2012 8:43 PM   in reply to Jonathan Fortis

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

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 9, 2012 8:21 PM   in reply to Jonathan Fortis

    >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.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 10, 2012 9:35 AM   in reply to Jonathan Fortis

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 11, 2012 10:50 PM   in reply to Jonathan Fortis

    >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.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 14, 2012 10:32 AM   in reply to Jonathan Fortis

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 14, 2012 10:50 AM   in reply to Jonathan Fortis

    >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.

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points