Searching mysql help plz

Anon

Hi,

I am using the following bit of code to take the keywords input from a form with a field 'keywords'. It works fine, displaying all the matches found (I have omitted the results display code from the snippet) but... the problem I am having is that the loop will produce multiple results of the same record.

For example, if the table contains one record with the title 'Subaru' and the word 'Subaru' is also mentioned in the description my code will return that same record twice ! How do I fix my code so that records will only be displayed once regardless of whether te keywords appear in both the Title and Description fields ?

I hope I have explained what I am trying to do so that it makes sense. Any help would be greatly appreciated. Thanks.

$searchstring = explode(" ",$keywords);
sort($searchstring);
$l = count($searchstring);

for ($i = 0; $i < $l ; $i++) {

$sql6 = "SELECT * FROM auctionitem where title like '%$searchstring[$i]%' or description like '%$searchstring[$i]%' and status = 'current' ORDER BY title ASC ";

$sql_result = mysql_query($sql6,$cid)
or die("Couldn't execute query.");

}

mamil

Hi,
why are you expecting that your code will return the same record twice ? In fact it won't.

Anon

ahh, I missed something out sorry... it returns the same record twice or more if 2 or more keywords are specified for example if the keywords specified were 'Subaru' and 'black'.

The first time thru the loop will return the record with the word 'Subaru' in the 'Title' field, then it goes thru it again and looks for the word 'black' which it finds in the 'description' field for that same record returning the same record again.

I can understand why it does this but how do I avoid returning the same record. Is there a better way to achieve what I am aiming for ? Thanks.

mamil

Well, it seems rather difficult to realize avoiding fetching the same record twice in this case but we can avoid processing this record twice. I assume that you created table auctionitem with at least one field with unique property so we can distinguish given row from all others (let's assume this field's name is 'id'). Here is modified code:

for ($i = 0; $i < $l ; $i++) {

$sql6 = "SELECT FROM auctionitem where ...
$sql_result = ....
$sql_row = mysql_fetch_array($sql_result);
if(!in_array($processed_rows,$sql_row["id"])
{
/ do something with fetched data
.....
*/
$processed_rows[] .= $sql_row["id"];
}
}
In the code above we use array $processed_row to store identifiers of rows which were already fetched and processed; after fetching another row we checked whether this row was already fetched, it not - we process the row and put its id into
array so next time this record will be fetched but not processed.