[RESOLVED] Multiple Keywords in SQL database search

[ouija]

Hello,

I'm trying to reconfigure a php script I found off another site to allow a search of multiple keywords within a colum/table in an sql database.

The problem with the existing code is that if someone enters multiple keywords, but those keywords dont appear next to each other in the database it doesnt find them.

There is a similar topic about this problem here but I am to retarded to figure out how to implement the same idea within the script I am using to search my database.

I know that it should be simple enough to do, and I am currently trying to get it to work myself, but am having a hell of a time doing so. If anyone has any suggestions it would greatly be appreciated!

Thanks again! 🙂

[Oni]

$var = @$_GET['q'] ; // grabs keywords from form entry on another page

the GET command gets data from the url. If you're trying to get the POST data from the other page you need to use the POST command...

I think. 😉

[ouija]

Oni wrote:

$var = @$_GET['q'] ; // grabs keywords from form entry on another page

the GET command gets data from the url. If you're trying to get the POST data from the other page you need to use the POST command...

I think. 😉

Whatevs... this still doesn't solve my problem. But thanks anyways :bemused:

[thorpe]

but I am to retarded to figure out how to implement the same idea within the script I am using to search my database

So you want us to do it for you? Honestly... there are two good examples in the thread you pointed out, if you cant make use of them then its time to start learning php instead of trying to piece together other peoples code.

What do you want from us?

[ouija]

thorpe wrote:

What do you want from us?

I'm looking for help from you, that's what! 😕 Not asking for much, just someone to point me in the right direction...

$searchterms = preg_replace('/[[:space:]]+/', ' ', $searchstr); // strip extra spaces
$search_array = explode(' ', $searchterms);

$sql_where = '';
foreach($search_array as $word)
    $sql_where .= '`keywords` LIKE \'%' . $word . '%\' OR ';

$query = 'SELECT * from search WHERE ' . rtrim($sql_where, ' OR'); 

I've tried endlessly to implement the same idea as the above, but end up with errors because I'm not quite sure how to make the code work with this other script. I just thought I'd ask for help, but apparently that's not going to happen...

Sorry for not having the php skills you do. I guess i'll just keep spending endless hours trying to make it work until I get it right.

Thanks anyways.

[thorpe]

Yeah... sorry. A bit tired over here.

<?php

// Get the search variable from URL

$var = @$_GET['q'] ; // grabs keywords from form entry on another page
$trimmed = trim($var); //trim whitespace from the stored variable
$searchterms = preg_replace('/[[:space:]]+/', ' ', $trimmed); // strip extra spaces
$search_array = explode(' ', $searchterms);
$sql_where = '';
foreach($search_array as $word)
    $sql_where .= '`1st_field` LIKE \'%' . $word . '%\' OR ';

// rows to return
$limit=10;

// 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 your database ** EDIT REQUIRED HERE **
mysql_connect("localhost","username","password"); //(host, username, password)

//specify database ** EDIT REQUIRED HERE **
mysql_select_db("database") or die("Unable to select database"); //select which database we're using

// Build SQL Query
$query = "SELECT * from the_table WHERE " . rtrim($sql_where, ' OR') ." ORDER BY 1st_field";
//$query = "select * from the_table where 1st_field like \"%$trimmed%\" order by 1st_field"; // EDIT HERE and specify your table and field names for the SQL query

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

// If we have no results, offer a google search as an alternative

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

// google
echo "<p><a href=\"http://www.google.com/search?q="
. $trimmed . "\" target=\"_blank\" title=\"Look up
" . $trimmed . " on Google\">Click here</a> to try the
search on google</p>";
}

// 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";
$count = 1 + $s ;

// now you can display the results returned
while ($row= mysql_fetch_array($result)) {
$title = $row["1st_field"];

echo "$count.)&nbsp;$title" ;
$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 10</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 10 &gt;&gt;</a>";
}

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

?>

Just copy and pasted, NOT tested.

[ouija]

That's okay.. I know how it is.. and I appreciate your help.

And what you did there is exactly how I tried to implement the two together, but when I run the script I get the following error:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in [path] on line 43

And i've tried to get rid of that whole section of the script, but then the script usually errors out or states that I haven't entered any keywords. And this is why I turned to this form, because i've tried everything to make this stupid thing work and I can't seem to figure it out.

But thanks again for your help, man.. 🙂

[thorpe]

Change this..

$numresults=mysql_query($query);

to this and post the results.

$numresults=mysql_query($query) or die(mysql_error()."query = ".$query);

By the way, ive just looked at this code and it really is terrible.

[ouija]

It Works!!!! Thank you sooooo much thorpe, you are the best! I cannot tell you how happy that makes me..

I know the script is a bit wonky, but the point is IT WORKS! 😉

Thanks again!