21 Replies Latest reply: Mar 27, 2012 11:06 AM by Jonathan Fortis RSS

    3 search fields Need to return results for multiple words for each feild NOT all feilds

    Jonathan Fortis Community Member

      I have three search boxes here

       

      <form id="Jobtitle" name="Jobtitle" method="get" action="../current-positions.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>

       

       

      I have built the results page below

       

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

      }

      }

       

       

      $maxRows_Recordset1 = 5;

      $pageNum_Recordset1 = 0;

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

        $pageNum_Recordset1 = $_GET['pageNum_Recordset1'];

      }

      $startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;

       

       

      $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"];

      }

      $var_tk_job_salary_Recordset1 = "%";

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

        $var_tk_job_salary_Recordset1 = $_GET["tk_job_desc"];

      }

      mysql_select_db($database_hostprop, $hostprop);

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

      $query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);

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

      $row_Recordset1 = mysql_fetch_assoc($Recordset1);

       

       

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

        $totalRows_Recordset1 = $_GET['totalRows_Recordset1'];

      } else {

        $all_Recordset1 = mysql_query($query_Recordset1);

        $totalRows_Recordset1 = mysql_num_rows($all_Recordset1);

      }

      $totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;

      ?>

       

       

      and the results here

       

      <table width="655" border="0" cellspacing="5" cellpadding="0">

                  <tr>

                    <td colspan="2" class="Titlegreen"><?php echo $row_Recordset1['tk_job_title']; ?></td>

                  </tr>

                  <tr>

                    <td colspan="2" class="textblack"><?php echo $row_Recordset1['truncated_job_desc']; ?>....read more</td>

                  </tr>

                  <tr>

                    <td width="250"> </td>

                    <td width="405"><div align="right"><span class="pos_salary"><?php echo $row_Recordset1['tk_job_salary']; ?></span><span class="pos_location">/<?php echo $row_Recordset1['tk_job_location']; ?></span></div></td>

                  </tr>

                </table>

       

       

      at the moment if i search in the tk_job_title  for security it will only display the results for security even if i have security guard in the DB.

      or if i search Sydney it will only return these results even if i have Sydney Australia in the DB

       

      can someone help?

       

      thanks in advance

        • 1. Re: 3 search fields Need to return results for multiple words for each feild NOT all feilds
          Jonathan Fortis Community Member

          i have looked on the internet and it shows scripts for single search fields but not if there are three search boxes. Each search box only needs to search a single feild not mulitple accross the DB

          • 2. Re: 3 search fields Need to return results for multiple words for each feild NOT all feilds
            bregent CommunityMVP

            >at the moment if i search in the tk_job_title  for security it will only

            >display the results for security even if i have security guard in the DB.

             

            Jon, you are already using the SQL LIKE predicate in your WHERE clause, but you are not including any wildcards whihch is why it is only finding exact matches. To resolve the problem, take a look at this article:

            http://joshhighland.com/blog/2008/07/06/php-sprintf-sql-like/

             

            It lists various methods for adding wildcards within the sprintf() function.

             

            However, this will not resolve the search condition you mentioned in another post. That is, if the user entered 'security guard', you wanted results if the column contained either or both of those words. To achieve that will require a much more complicated scripted solution, or the use of FULL TEXT searching.

            • 3. Re: 3 search fields Need to return results for multiple words for each feild NOT all feilds
              Jonathan Fortis Community Member

              I will look at the article and i have now upgraded my SQL so can use the FULL TEXT option

              • 4. Re: 3 search fields Need to return results for multiple words for each feild NOT all feilds
                Jonathan Fortis Community Member

                ok i read the article and this is what i need to be doing

                 

                $query =sprintf("SELECT name FROM users WHERE name LIKE '%s'", $searchString . "%");

                 

                but put into the below code-


                 

                $query_Recordset1 = sprintf("SELECT tk_job_title, tk_job_location, tk_job_salary, LEFT(tk_job_desc,200) as truncated_job_desc 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"));

                $query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);

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

                $row_Recordset1 = mysql_fetch_assoc($Recordset1);


                i tried this but it didnt work

                 

                mysql_select_db($database_hostprop, $hostprop);

                $query_Recordset1 = sprintf("SELECT tk_job_title, tk_job_location, tk_job_salary, LEFT(tk_job_desc,200) as truncated_job_desc 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, "%"),GetSQLValueString($var_tk_job_location_Recordset1, "text"),GetSQLValueString($var_tk_job_salary_Recordset1, "text"));

                $query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);

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

                $row_Recordset1 = mysql_fetch_assoc($Recordset1);

                • 5. Re: 3 search fields Need to return results for multiple words for each feild NOT all feilds
                  bregent CommunityMVP

                  1) You are not being consistent with your sprintf directives - the first is wrapped in single quotes and the rest are not. This is not related to your problem, but in programming, it's extremely important to be consistent.

                  2) You are not appending the wildcard character to your swap value. Use the php concatenation character '.'.  Go back to the article I suggested and make sure you understand how the author solved the problem. If you still have problems, post the modified code again.

                  • 6. Re: 3 search fields Need to return results for multiple words for each feild NOT all feilds
                    Jonathan Fortis Community Member

                    mysql_select_db($database_hostprop, $hostprop);

                    $query_Recordset1 = sprintf("SELECT tk_job_title, tk_job_location, tk_job_salary, LEFT(tk_job_desc,200) as truncated_job_desc FROM think_jobsearch WHERE tk_job_title LIKE '%s', $searchString . "%" OR tk_job_location LIKE %s OR tk_job_salary LIKE %s", GetSQLValueString($var_tk_job_title_Recordset1, "%"),GetSQLValueString($var_tk_job_location_Recordset1, "text"),GetSQLValueString($var_tk_job_salary_Recordset1, "text"));

                    $query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);

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

                    $row_Recordset1 = mysql_fetch_assoc($Recordset1);

                     

                     

                    i am really trying to understand this when you talk about concatenation character do you mean . in his  example he is using '%s'", $searchString . "%"

                     

                    is this what you mean. i have added  tk_job_title LIKE '%s', $searchString . "%" 

                     

                    this again gace an error

                    • 8. Re: 3 search fields Need to return results for multiple words for each feild NOT all feilds
                      bregent CommunityMVP

                      What you tried doesn't make sense. You are putting the searchString variable after your directive, which gets replaced with the searchString variable again. Do you understand how the sprintf() function works?

                       

                       

                      OK, first of all I misspoke regarding item #1 above.  Because you are using string variables in the SQL like predicate, they absolutely should be wrapped in single quotes. Try this:

                       

                       

                      mysql_select_db($database_hostprop, $hostprop);

                      $query_Recordset1 = sprintf("SELECT tk_job_title, tk_job_location, tk_job_salary, LEFT(tk_job_desc,200) as truncated_job_desc 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"));

                      $query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);

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

                      $row_Recordset1 = mysql_fetch_assoc($Recordset1);

                       

                      Of course, this will only add the wildcard character to the end of each variable. If you want it at the beginning, you need to add it there too. 

                       

                      What type of data is in the tk_job_salary column?

                      • 9. Re: 3 search fields Need to return results for multiple words for each feild NOT all feilds
                        Jonathan Fortis Community Member

                        ok so the wildcard goes before the "text" i added it to the others aswell

                         

                        i tried this but and error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'security%'' OR tk_job_location LIKE ''Location'' OR tk_job_salary LIKE ''Salary'' at line 1

                         

                        mysql_select_db($database_hostprop, $hostprop);

                        $query_Recordset1 = sprintf("SELECT tk_job_title, tk_job_location, tk_job_salary, LEFT(tk_job_desc,200) as truncated_job_desc 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"));

                        $query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);

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

                        $row_Recordset1 = mysql_fetch_assoc($Recordset1);


                         


                        • 10. Re: 3 search fields Need to return results for multiple words for each feild NOT all feilds
                          bregent CommunityMVP

                          >ok so the wildcard goes before the "text" i added it to the others aswell

                           

                          Show us the code that produced that error message. I'm not really familiar with GetSQLValueString - it's a DW defined function. It's very possible that for text values, it wraps them in single quotes. If that's the case, then lose the single quotes from the actual SQL string:

                           

                          mysql_select_db($database_hostprop, $hostprop);

                          $query_Recordset1 = sprintf("SELECT tk_job_title, tk_job_location, tk_job_salary, LEFT(tk_job_desc,200) as truncated_job_desc 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"));

                          $query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);

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

                          $row_Recordset1 = mysql_fetch_assoc($Recordset1);

                           

                           

                          But you still haven't answered my question about tk_job_salary. What is the datatype of that column?

                          • 11. Re: 3 search fields Need to return results for multiple words for each feild NOT all feilds
                            Jonathan Fortis Community Member

                            sorry job salary is currently

                             

                            varchar(55)

                             

                             


                            • 12. Re: 3 search fields Need to return results for multiple words for each feild NOT all feilds
                              Jonathan Fortis Community Member

                              ok, i ran that and as example if i have 3 jobs e.g security officer, security guard cheif, security

                               

                              if i search security all three are returned

                              if i search officer nothing is returned

                              if i search security guard only security guard cheif is returned

                              • 13. Re: 3 search fields Need to return results for multiple words for each feild NOT all feilds
                                bregent CommunityMVP

                                >ok, i ran that and as example if i have 3 jobs e.g security officer, security guard cheif, security

                                 

                                >if i search security all three are returned

                                 

                                This is the behavior I would expect

                                 

                                >if i search officer nothing is returned

                                 

                                This is also expected. Your query is WHERE job_title LIKE 'officer%'.  So it is only going to find matches where the stored text starts with 'officer'. If you want to find matches where 'officer' is contained anywhere in the text, then you need to add a wildcard before the variable in the GetSQLValueString function.

                                 

                                >if i search security guard only security guard cheif is returned

                                 

                                Again, expected behavior. You only have one row that contains the string 'security guard'. What were you expecting to happen?

                                • 14. Re: 3 search fields Need to return results for multiple words for each feild NOT all feilds
                                  Jonathan Fortis Community Member

                                  oh ok so i need to add a wildcard to the beginning of the variable. will try that

                                  • 15. Re: 3 search fields Need to return results for multiple words for each feild NOT all feilds
                                    Jonathan Fortis Community Member

                                    mysql_select_db($database_hostprop, $hostprop);

                                    $query_Recordset1 = sprintf("SELECT tk_job_title, tk_job_location, tk_job_salary, LEFT(tk_job_desc,200) as truncated_job_desc 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"));

                                    $query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);

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

                                    $row_Recordset1 = mysql_fetch_assoc($Recordset1);

                                     

                                    i added the "%" . to the beginning of the variable

                                     

                                    its working better but if i input security guard it returns nothing even if security guard cheif is present in the DB

                                     

                                    i take it this is because the wild card ar only on either end?

                                    • 16. Re: 3 search fields Need to return results for multiple words for each feild NOT all feilds
                                      bregent CommunityMVP

                                      >its working better but if i input security guard it returns nothing even if security guard cheif is present in the DB

                                       

                                      >i take it this is because the wild card ar only on either end?

                                       

                                      No, it should work. Add an echo statement at the end of the code to output the $query_Recordset1 string and post the results.

                                      • 18. Re: 3 search fields Need to return results for multiple words for each feild NOT all feilds
                                        bregent CommunityMVP

                                        Put it anywhere after the code that you have posted. We need to examine the SQL string sent to the db.

                                        • 19. Re: 3 search fields Need to return results for multiple words for each feild NOT all feilds
                                          Jonathan Fortis Community Member

                                          >Put it anywhere after the code that you have posted.

                                           

                                          ok

                                           

                                          > We need to examine the SQL string sent to the db.

                                           

                                          what do i need to do for this?

                                          • 20. Re: 3 search fields Need to return results for multiple words for each feild NOT all feilds
                                            bregent CommunityMVP

                                            >what do i need to do for this?

                                             

                                            You just need to add an echo statement so that the $query_Recordset1 string is output to the screen. Then paste the results into this thread.

                                            • 21. Re: 3 search fields Need to return results for multiple words for each feild NOT all feilds
                                              Jonathan Fortis Community Member

                                              i put an echo at the end

                                               

                                              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"));

                                              $query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);

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

                                              $row_Recordset1 = mysql_fetch_assoc($Recordset1);

                                               

                                               

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

                                                $totalRows_Recordset1 = $_GET['totalRows_Recordset1'];

                                              } else {

                                                $all_Recordset1 = mysql_query($query_Recordset1);

                                                $totalRows_Recordset1 = mysql_num_rows($all_Recordset1);

                                              }

                                              $totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;

                                              echo  $query_Recordset1

                                               

                                              and got the below results

                                               

                                              SELECT tk_job_title, tk_job_location, tk_job_salary, LEFT(tk_job_desc,200) as truncated_job_desc FROM think_jobsearch WHERE tk_job_title LIKE '%security%' OR tk_job_location LIKE '%Location%' OR tk_job_salary LIKE '%Salary%'