error when not using boolean mode in a MySQL query

[The Little Guy]

I first had it set to "Boolean Full-Text Search Mode" It worked fine, then I decided to ad a my.cnf file, but those files don't work in boolean mode, so I tried this, and it returns this error:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home2/dtoporg/public_html/search/results.php on line 56
Couldn't execute query

$query = "SELECT urltitle,description,keywords,menu1,url,id,votes,v_rate,subcat, MATCH(urltitle,description,keywords) AGAINST ('$var' WITH QUERY EXPANSION) AS score FROM search WHERE MATCH(urltitle,description,keywords) AGAINST ('$var' WITH QUERY EXPANSION) ORDER BY score DESC";

//IN BOOLEAN MODE

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

Any Suggestions

[bokeh]

The query doesn't appear to have any parse errors. Replace this:$numresults = mysql_query($query); with:$numresults = mysql_query($query) or die('Query: '.$query.' Error: '.mysql_error()); It could be something in one of the variables causing the problem.

[The Little Guy]

Can't find FULLTEXT index matching the column list

or

Query: SELECT urltitle,description,keywords,menu1,url,id,votes,v_rate,subcat, MATCH(urltitle,description,keywords) AGAINST ('free' WITH QUERY EXPANSION) AS score FROM search WHERE MATCH(urltitle,description,keywords) AGAINST ('free' WITH QUERY EXPANSION) ORDER BY score DESC Error: Can't find FULLTEXT index matching the column list

[bokeh]

Run this script once and it will create an index.<?php

$user = '';
$password = '';
$database = '';
$tablename = 'search';

mysql_connect("localhost", $user, $password) or die(mysql_error());
mysql_select_db($database) or die(mysql_error());
AddFullTextIndex($tablename);


function AddFullTextIndex($tablename, $fulltext_index_name = 'fulltext_index')
{
$query = <<<QUERY
DESCRIBE $tablename
QUERY;

$result = mysql_query($query) or die(mysql_error());
$fields = array();
if(mysql_num_rows($result) > 0)
{
while ($row = mysql_fetch_assoc($result))
{
if(preg_match('@(text|char)@i', $row['Type'])){
$fields[] = $row['Field'];
}
}
}
else
{
return false;
}
if(count($fields) < 1)
{
return false;
}
$comma = NULL;
$columns = NULL;
foreach($fields as $column)
{
$columns .= $comma.$column;
$comma = ', ';
}
$query = <<<QUERY
CREATE FULLTEXT INDEX $fulltext_index_name
ON $tablename ($columns)
QUERY;
mysql_query($query) or die(mysql_error());
return TRUE;

}

?>

[The Little Guy]

Access denied for user 'dtoporg_dtop'@'localhost' to database '$database'

I have all Privileges to that database

[bokeh]

Access denied for user 'dtoporg_dtop'@'localhost' to database '$database'

I have all Privileges to that databaseHave you filled in the variables at the top. I edited it after I posted it. Copy it again and retry.

[The Little Guy]

OK, It ran, I refreshed the page, and it says "Duplicate key name 'fulltext_index'" So... I am guessing it worked.

On a off note, I am headding to class, so I wont reply back for 3-4 hours.

[bokeh]

Yeah thats right. It produces no output.

[chazzy]

can you just clarify this statement?


then I decided to ad a my.cnf file, but those files don't work in boolean mode,

[The Little Guy]

can you just clarify this statement?
I got this from Here: http://w3.phpfreaks.com/tutorials/129/1.php

Full-Text Noise Words

Noise words are basically common words that are found in the language. For example: and, if, or, the. MySQL Full-Text has an automatic noise word list built in and words such as "and, the, or, if" and a whole lot more are automatically excluded by default as noise words.

The default noiseword list can be changed by creating a file with a list of words, one word per line and change the "ft_stopword_file" setting in the MySQL configuration:
Code Example:

[mysqld]
ft_stopword_file = /path/to/noisewords.txt


The 50% Threshold

To enhance the standard Full-Text search results, any word found in your index more than 50% of the time falls into the "50% threshold" and they are treated as noisewords and excluded. This threshold is designed to enhnace the results. However, when you are searching in BOOLEAN mode, this rule is disregarded.

[chazzy]

ok, but are you doing this on a hosted environment or something where you don't have access to restart the mysql server? i ask this because it's not the my.cnf that gets ignored, it's any 50% threshold word that gets ignored in boolean mode.

[The Little Guy]

Yeah, this is on a hosted server

I think the term is remotly hosted server.