Skip navigation
Currently Being Moderated

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

Mar 16, 2012 2:17 AM

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

 
Replies
  • Currently Being Moderated
    Mar 16, 2012 10:12 AM   in reply to Jonathan Fortis

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

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 26, 2012 11:09 AM   in reply to Jonathan Fortis

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 26, 2012 2:51 PM   in reply to Jonathan Fortis

    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?

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 26, 2012 3:36 PM   in reply to Jonathan Fortis

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

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 26, 2012 4:00 PM   in reply to Jonathan Fortis

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

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 26, 2012 4:25 PM   in reply to Jonathan Fortis

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

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

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

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

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

     
    |
    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