Searching MySQL help - almost there :)

Anon

Hi there, I'm wondering if anyone could help me.

I'm having no end of trouble trying to make a search engine work on my test php site. I've tried creating the code myself (at the bottom) and I'm having some success, but little things elude me, as I will explain.

These are my search requirements:
I wish to search on one field in one table.
I'd like to be able to enter multiple keywords. eg 'word1' 'word2' at the same time
I wish to stop duplicate results.
Possibly filtering out noise words such as 'the' 'it' 'a' etc.

The article here on PHP Builder about creating search engines has helped me a lot, but it's not quite what I need and some bits are hard to understand as I am a fairly new user. I like the idea of filtering out noisewords but I just can't get it to work with my code.

Here is what I have so far:

include("config.inc.php");
$connection = @mysql_connect($host, $user, $pass)
or die("Couldn't connect.");
$db = @mysql_select_db($db_name, $connection)
or die("Couldn't select database.");
if ($query) { //Query is the value I inputted
$filtered = $query;
$eachword = explode(" ", $filtered); //Split the query up if more than one word
for ($k=0; $k < count($eachword); $k++) { For each split word do a check
$sql = "SELECT ticket_id, prob_desc FROM $table_logs WHERE prob_desc LIKE '%$eachword[$k]%'";
$result = @($sql,$connection) or die("Couldn't execute user query 4.");
while ($row = mysql_fetch_array($result)) {
$ticket_id = $row['ticket_id'];
$prob_desc = $row['prob_desc'];
if ($ticket_id != $check[$e]) {
$html_block .= "<tr><td>$ticket_id <td>$prob_desc";
}
$check[] = $ticket_id;
}
}
$bob = implode('<br> ', $check);
echo $bob;
}
?>

My problem is that if my field has more than one of the Query words in it, the results will be displayed more than once, and i wish to filter this down so that only one of the same result is displayed.
I realise there must be an easy way but i just cant seem to get my head around it, which is why i'm fairly new to this. 🙂

What may be preferable to u lot is if you could provide me with a link to some sample code that does what i require in nice easy steps so that i can study it, rather than providing an answer to that mess of a code above 🙂

I hope u understood what i have been babbling on about.

Thanks in advance!

chaostik

Hi,

I think you were doing sort of ok until the SQL bit. Really, to be nice to the database and to easily eliminate duplicates, you ought to pass just one big query rather than several little ones. I'd do something like this (also, indent your code to make it more readable!), maintaining your basic skeleton code:

<?
include("config.inc.php");
$connection = @mysql_connect($host, $user, $pass) or die("Couldn't connect.");
$db = @mysql_select_db($db_name, $connection) or die("Couldn't select database.");
if ($query) { //Query is the value I inputted
$filtered = $query;
$eachword = explode(" ", $filtered);

//***
$sql = "SELECT ticket_id, prob_desc FROM $table_logs WHERE ";
$sql .= "prob_desc LIKE '%$eachword[$k]%' "

for ($k=1; $k < count($eachword); $k++) { For each split word do a check
$sql .= "OR prob_desc LIKE '%$eachword[$k]%' ";
}
//***

$result = @($sql,$connection) or die("Couldn't execute user query 4.");
while ($row = mysql_fetch_array($result)) {
$ticket_id = $row['ticket_id'];
$prob_desc = $row['prob_desc'];

// FROM HERE ON IS UNCLEAR, WHERE DOES $CHECK[$E] COME FROM?
if ($ticket_id != $check[$e]) {
$html_block .= "<tr><td>$ticket_id <td>$prob_desc";
}
$check[] = $ticket_id;
}

$bob = implode('<br> ', $check);
echo $bob;
}
?>

The main part to note is between the two //*** comments. Hope that is of some help.

Louis

Anon

Thankyou very much, that has certainly helped me out!! ;-)

chaostik

Oops!

Just glanced at my answer and saw a glaring mistake that I'm sure you spotted, but just in case, the bit I mainly changed read like this:

//***
$sql = "SELECT ticket_id, prob_desc FROM $table_logs WHERE ";
$sql .= "prob_desc LIKE '%$eachword[$k]%' "

for ($k=1; $k < count($eachword); $k++) { For each split word do a check
$sql .= "OR prob_desc LIKE '%$eachword[$k]%' ";
}
//***

BUT!! The line:

$sql .= "prob_desc LIKE '%$eachword[$k]%' "

should have read:

$sql .= "prob_desc LIKE '%$eachword[0]%' "

Seeya,

Louis

Anon

Hi, I have exactly the same problem as Paul, I understand how your code fixes the problem but... I am wanting to search 2 fields in my table which I guess slightly complicates things a little ?

How do I search multiple words in 2 fields without duplicating returned records ?

Say for example I want to add the field 'description' to the query below:

if ($query) { //Query is the value I inputted
$keywords = $query;
$searchstring = explode(" +", $keywords);

//**
$sql = "SELECT FROM auctionitem WHERE ";
$sql .= "title LIKE '%$searchstring[0]%' "

for ($k=1; $k < count($searchstring); $k++) { //For each split word do a check
$sql .= "OR title LIKE '%$searchstring[$k]%' ";
}
//***

Thanks in advance. 🙂

Anon

Hiya - if I understand your query correctly (and maybe I don't - I'm a little hungover), it should be quite a simple fix. If you're using MYSQL, you should be able to stick the keyword DISTINCT in after SELECT to stop the db returning duplicate results, like so:

//
$sql = "SELECT DISTINCT FROM auctionitem WHERE ";
//*

Sorry if that wasn't what you were after & I missed something here.