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

[Jonathan_Fortis]

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

[Jonathan_Fortis]

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

[bregent]

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

[Jonathan_Fortis]

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

[Jonathan_Fortis]

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

[bregent]

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.

[Jonathan_Fortis]

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

[Jonathan_Fortis]

this again diplays an error

[bregent]

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?

[Jonathan_Fortis]

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

[bregent]

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

[Jonathan_Fortis]

sorry job salary is currently

varchar(55)

[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

if i search officer nothing is returned

if i search security guard only security guard cheif is returned

[bregent]

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

[Jonathan_Fortis]

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

[Jonathan_Fortis]

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?

[bregent]

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

[Jonathan_Fortis]

ok where do i put the echo?

[bregent · Accepted solution]

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

[Jonathan_Fortis]

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

[bregent]

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

[Jonathan_Fortis]

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%'