fulltext not doing what I want it to...

Anon

I got the tip from Vincent to use fulltext in a query I am trying to make...

BUT fulltext search uses a relevance which I don't want (really don't) and it still gives back and OR result instead of AND

I read that mysql has its own stopwordlist with the fulltext search option...is there a way to shut it off or set ur own?

thx again

[deleted]

Not reallt a stopswordlist, a minimum length of the words that are indexed. Yes there is a way to turn it off, it's in the manual somewhere.

I thought this is exactly what you wanted: get result from the query that match as many of the searchwords as possible.

Anon

uhm...well I thought is was too but it kinda doesn't.

The first 'search' for the key "test1" should give x results. Now the search for "test2" (in search 1) should give all test2's in the resultset of test1 (that makes it an AND search I think)

Now have turned to the search qeury's used in de Zend Search Engine to figure out a way to do this....

[deleted]

Aha, so you want to list all rows where a field contains 'bla'.

Then you want to search those rows to see if any of them also have 'bloo' in that same field, and print those rows.

This is indeed an AND query, but you can't do it all in one query.
You will have to first to a query like
SELECT field
FROM table
WHERE field LIKE '%bla%'

and print the result.
Next you should run a query like
SELECT field
FROM table
WHERE field LIKE '%bla%'
AND field LIKE '%bloo%'

Effectively the second query does a refined search on the results from the first query.

Anon

hmm...but the search could be extended for a third, fourth etc word...how to do that?

[deleted]

Just like I posted,
loop through the words and add a LIKE '%word%' for every word you want to search for.

$sQuery = 'SELECT * FROM table WHERE ';
for ($t;$t<$number_of_words)
{
$sQuery .= " field LIKE '%".$aWords[$t]."%' ";
// execute query, print results.
};

Anon

This looks like it yeah 🙂

but using a simple form to fill out the first word (or in case of filling out to words) how do I set the variable number_of_words AND how do I get the first word seen as aword[1], the second as aword[2] etc...?

BTW Thanks loads for the info so far (I am no wizz...but you got that already 🙂 )

[deleted]

Use arrays. :-)

If you let the user enter the words in a single field, then you'll get a string like
"word anotherword foo bar"

You can turn that into an array using explode(' ',$variable)
Then you can use the loop.

Anon

so if the form returns variable $word (containing "test1 test2")

I can put it in an array like so:

$search_array = explode(' ', $word);

that makes sence...but when I now add the query:

$q = 'SELECT result FROM table WHERE ';
for ($t; $t<$number_of_words)
{
$q = "key LIKE '%".$search_array[$t]."%'";

$r = mysql_query($q);
$row = mysql_fetch_array($r);
echo $row['result'];

how does one set the $number_of_words ???

Anon

is it like this (found it in the php manual)

for ($t; $t<count($search_array)
{
etc....

Anon

I have got this:


$search_array = explode(' ', $word);

mysql_connect("$db_host", "$db_name", "$db_pas") or die ("cant connect");
mysql_select_db("$db_name") or die ("cant change");

$i=0;
$ii=count($search_array);

$q = 'SELECT * FROM keys WHERE ';
for($i=0;$i<$ii;$i++)
{
$q = "Key LIKE '%".$search_array[$i]."%'";

$r = mysql_query($q) or die ("can't perform query");
$row = mysql_fetch_array($r);
echo $row['Key_ID'];
}
?>

and it shows the die statement...I don't know where the error is........

[deleted]

because the OR DIE is a very crude method to use.
Instead, use:

if (!$result = mysql_query($sQuery, ....))
{
echo 'SQL error: '.mysql_error().'<BR>'.$sQuery;
}
else
{
// query ok, process data
};

Anon

thx that makes the following error:

SQL error: You have an error in your SQL syntax near 'Key LIKE '%doei%'' at line 1
SELECT * FROM keyword WHERE Key LIKE '%hallo%'Key LIKE '%doei%'

looking at it, it seems that the loop doesn't add an AND statement like you said in one of your earlier replies...

I have been trying to put it in...but no luck

[deleted]

One problem there was that there was no space between the first and the second LIKE.

You'll have to add the AND yourself.
If you do that in the loop, you'll get one AND too many at the start:

SELECT * FROM table WHERE AND LIKE'%....

to solve that, you can put in one useless statement at the beginning, after which you can put the AND:

SELECT * FROM table WHERE 1=1

After that, and AND is valid.