multiple keyword search

[phpdino]

Hello, I have been working with this searching script and have it working exactly how I need it to but only with one keyword. I have tried a few combinations but can't figure out if I can do this on the mysql end or php. Can anyone put me in the right direction? Thanks

<?
 // Get the search variable from URL

$var = @$_GET['q'];
$trimmed = trim($var); //trim whitespace from the stored variable

 // rows to return
	$limit=20;

 // check for an empty string and display a message.
 if ($trimmed == "")
 	{
  		echo "<p>Please enter a search...</p>";
  		exit;
  	}

 // check for a search parameter
 if (!isset($var))
 	{
  		echo "<p>We dont seem to have a search parameter!</p>";
  		exit;
  	}

 //connect to database
 mysql_connect("localhost","lamer","password"); //(host, username, password)

 //specify database
 mysql_select_db("support") or die("Unable to select database");

 // Build SQL Query  

 $query = "select help_page_id, title from support.help_pages where content like '%$trimmed%' "
 ."and help_page_id in ( "
 ."SELECT help_page_id "
 ."FROM support.help_pages p "
 ."INNER JOIN support.help_topics t "
 ."ON p.help_topic_id=t.help_topic_id "
 ."INNER JOIN support.help_versions v "
 ."ON t.help_version_id=v.help_version_id "
 ."WHERE v.help_interface_id=".$help_interface->getPrimaryKey().")";

 //echo $query;

 $numresults=mysql_query($query);
 $numrows=mysql_num_rows($numresults);

 if ($numrows == 0)
 	{
  		echo "<h4>Results</h4>";
  		echo "<h1>Sorry, your search: &quot;" . $trimmed . "&quot; returned zero results</h1>";
  	}

  else {

 // next determine if s has been passed to script, if not use 0
 if (empty($s)) {
 	$s=0;
  }

 // get results
 	$query .= " limit $s,$limit";
 	$result = mysql_query($query) or die("Couldn't execute query");

 // display what the person searched for
 	echo "<p>You searched for: &quot;" . $var . "&quot;</p>";

 // begin to show results set
 	echo "Results<br />";
 	$count = 1 + $s ;

 // now you can display the results returned
 	while ($row= mysql_fetch_array($result)) 
 	{
 		$title = $row["title"];
 		$help_page_id = $row["help_page_id"];
 		echo " $count.&nbsp;<a href='/help/index.php?help_id=$help_page_id'>".$title."</a><br />";
 		$count++ ;
  	}

$currPage = (($s/$limit) + 1);

 //break before paging
 	echo "<br />";

 // next we need to do the links to other results
 	if ($s>=1) { // bypass PREV link if s is 0
 	$prevs=($s-$limit);
 	print "&nbsp;<a href=\"$PHP_SELF?s=$prevs&q=$var\">&lt;&lt; 
 	Prev 20</a>&nbsp&nbsp;";
 	}

 // calculate number of pages needing links
 	$pages=intval($numrows/$limit);

 // $pages now contains int of pages needed unless there is a remainder from division

if ($numrows%$limit) { // has remainder so add one page
$pages++;
}

 // check to see if last page
 	if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {

 // not last page so give NEXT link
 	$news=$s+$limit;

echo "&nbsp;<a href=\"$PHP_SELF?s=$news&q=$var\">Next 20 &gt;&gt;</a>";
}

$a = $s + ($limit) ;
if ($a > $numrows) { $a = $numrows ; }
  $b = $s + 1 ;
  echo "<p>Showing results $b to $a of $numrows</p>";
}
?>

[ixalmida]

Just add " OR LIKE '%$keyword2%'" to the end of your query.

The only thing to watch is how your user delimits the keywords they submit. If you can make sure they only use spaces, you can do:

// Initial query string...
$query = "select help_page_id, title from support.help_pages where content like ";

// Create an array of keywords...
$keywrds = explode(" ", $_POST['keywords']);

// Add keywords to your query...
foreach($keywds as $keyword)
  $query .= "'$keyword' OR LIKE ";

// Remove the last "OR LIKE "...
$query = substr($query, 0, -8);

You can limit form field input to letters and spaces using Javascript. I'll let you look that up.

[phpdino]

ixalmida;10923899 wrote:

Just add " OR LIKE '%$keyword2%'" to the end of your query.

The only thing to watch is how your user delimits the keywords they submit. If you can make sure they only use spaces, you can do:

// Initial query string...
$query = "select help_page_id, title from support.help_pages where content like ";

// Create an array of keywords...
$keywrds = explode(" ", $_POST['keywords']);

// Add keywords to your query...
foreach($keywds as $keyword)
  $query .= "'$keyword' OR LIKE ";

// Remove the last "OR LIKE "...
$query = substr($query, 0, -8);

You can limit form field input to letters and spaces using Javascript. I'll let you look that up.

Thanks so much! I'll give it a shot.

[ixalmida]

Oops - I just noticed that I left out the wildcards in the query string.

This:

$query .= "'$keyword' OR LIKE ";

Should be:

$query .= "'%$keyword%' OR LIKE ";

Or, for better visability:

$query .= "'%".$keyword."%' OR LIKE ";

[phpdino]

ixalmida;10923907 wrote:

Oops - I just noticed that I left out the wildcards in the query string.

This:

$query .= "'$keyword' OR LIKE ";

Should be:

$query .= "'%$keyword%' OR LIKE ";

Or, for better visability:

$query .= "'%".$keyword."%' OR LIKE ";

Hmmm I must be doing something wrong. The value is always coming up as "Array".

To make sure I know what's going on:

// Initial query string...
$query = "select help_page_id, title from support.help_pages where content like "; 

This part is my query that I currently use to retrieve the records.

// Create an array of keywords...
$keywrds = explode(" ", $_POST['keywords']); 

The array is created but is $keywrds a new variable I am creating? HOw about the $_POST['keywords'] is keywords the value of the field in my form?

// Add keywords to your query...
foreach($keywds as $keyword)
$query .= "'%$keyword%' OR LIKE "; 

Not sure but the query gets cut off in the end when I echo it to see

// Remove the last "OR LIKE "...
$query = substr($query, 0, -8); 

Can you explain this one?

So sorry about all the questions but I'm trying to learn. Thanks so much for your help

[ixalmida]

Let's get into more detail about how this works and what to watch for...

Understand that if only one keyword is submitted, then you can't make an array out of it using the explode() function. What will happen is that you'll get an empty array (which is what you are probably seeing).

To test for and handle this one special case, you could use (note - I've changed some variable names from before):

// First, I assume you are somehow making the user's input query-safe...
$keywds = html_entities($_POST['keywords'], ENT_QUOTES);
// (replace quotes, etc. with HTML entities)

if(strtok($keywds, " ") !== FALSE)
  {
  // No spaces found (only 1 word), so the query should be...
  $query = "SELECT help_page_id, title FROM support.help_pages WHERE content LIKE '%$keywds%'";
  }
else
  {
  // Found a space, so now you can safely use explode()

  // Initial query string... 
  $query = "select help_page_id, title from support.help_pages where content like "; 

  // Create an array of keywords... 
  $words = explode(" ", $keywds); 

  // Add each keyword in the array to your query... 
  foreach($words as $keyword) 
    $query .= "'$keyword' OR LIKE "; 

  // Remove the last "OR LIKE "... 
  $query = substr($query, 0, -8); 
  }
// For testing purposes, you may want to echo the query...
// NOTE:  I use an HTML comment here so it won't interfere with the page.
// I also put 3 blank lines on either side so I can easily locate the comment it in the page source.
echo "\n\n\n<!--  $query  -->\n\n\n";

// Execute the selected query...
$result = mysql_query($query, $conn);

[johanafm]

Actually the special case with no delimiter is not needed (unless you want to use explode with a negative index).
explode(" ", "oneword") will return an array with one element containing the string "oneword".

[phpdino]

I tried to follow and understand the steps and I'm not sure if it's the long sql query that I have that's causing my problem.

if(strtok($trimmed, " ") !== FALSE)
  {

 // Build SQL Query  

 $query = "select help_page_id, title from support_epnet.help_pages where content like '%$trimmed%' "
 ."and help_page_id in ( "
 ."SELECT help_page_id "
 ."FROM support_epnet.help_pages p "
 ."INNER JOIN support_epnet.help_topics t "
 ."ON p.help_topic_id=t.help_topic_id "
 ."INNER JOIN support_epnet.help_versions v "
 ."ON t.help_version_id=v.help_version_id "
 ."WHERE v.help_interface_id=".$help_interface->getPrimaryKey().")";

 } else

 {

  // Initial query string...
  $query = "select help_page_id, title from support_epnet.help_pages where content like ";

  // Create an array of keywords...
  $words = explode(" ", $trimmed);
 echo $words;

  // Add each keyword in the array to your query...
  foreach($words as $keyword)
    $query .= "'$keyword' OR LIKE ";

  // Remove the last "OR LIKE "...
  $query = substr($query, 0, -8);
  } 

// DO I CONTINUE THE REST OF MY QUERY HERE? 
// How does this look so far?

 $numresults=mysql_query($query);
 $numrows=mysql_num_rows($numresults);