Web Hosting Talk : Web Hosting Main Forums : Programming Discussion : Full Text Search Not Working

[TomorrowHosting]

I have this snippet from one of my scripts:

PHP Code:

<?
$sql = mysql_query('SELECT \'response\' FROM cspeak WHERE MATCH (you, body)'
        . ' AGAINST (\'%.$you1.%\' IN BOOLEAN MODE) LIMIT 0, 30 '); 
$result = mysql_result($sql, 0);
       print "$result"
?>

I had variable $you1 set to "Hello", and I had this in my database:
-----------------------
| You | Response |
-----------------------
| Hello | Hello to you |
-----------------------

If this is the case, then shouldn't "Hello to you" appear on the page? Instead I get this error:

<br />
<b>Warning</b>: mysql_result(): supplied argument is not a valid MySQL result resource in <b>/home/www/connectmeonline.com/cardinespeak/indexspeak.php</b> on line <b>24</b><br />

What is wrong with this code?

Thanks!

[Rellizate]

Change to this:
<?
$sql = mysql_query('SELECT 'response' FROM cspeak WHERE MATCH (you, body)'
. ' AGAINST ('%.$you1.%' IN BOOLEAN MODE) LIMIT 0, 30 ');
$result = mysql_result($sql, 0);
print "$result"
?>

[TomorrowHosting]

I now got this error:

PHP Code:

Parse error: parse error, unexpected T_STRING in /home/www/connectmeonline.com/cardinespeak/indexspeak.php on line 2 


[aonic]

Rellizate's code will give an syntax error

do this:

PHP Code:

<?
$sql = mysql_query('SELECT `response` FROM cspeak WHERE MATCH (you, body)'
        . " AGAINST ('$you1' IN BOOLEAN MODE) LIMIT 0, 30 "); 
$result = mysql_result($sql, 0);
       print $result;
?>

[Rellizate]

Wait, is that all your code? Do you not have any code to connect to the database?

[TomorrowHosting]

I don't get a syntax error, but $result equals:

PHP Code:

<br />
<b>Warning</b>:  mysql_result(): supplied argument is not a valid MySQL result resource in <b>/home/www/connectmeonline.com/cardinespeak/indexspeak.php</b> on line <b>24</b><br /> 


just like it did before.

[aonic]

Originally Posted by TomorrowHosting

I don't get a syntax error, but $result equals:

PHP Code:

<br />
<b>Warning</b>:  mysql_result(): supplied argument is not a valid MySQL result resource in <b>/home/www/connectmeonline.com/cardinespeak/indexspeak.php</b> on line <b>24</b><br /> 


just like it did before.

PHP Code:

<?
$sql = mysql_query('SELECT `response` FROM cspeak WHERE MATCH (`you`, `body`)'
        . " AGAINST ('$you1') LIMIT 0, 30 ") or die(mysql_error()); 
$result = mysql_result($sql, 0);
       print $result;
?>

[TomorrowHosting]

It now says

PHP Code:

Query was empty 


, and then it terminates the rest of the script.
Is there something else that could be causing this?

[aonic]

I edited my previous reply, try it now

[TomorrowHosting]

I tried two different things, and I got two different problems. I did your script exactly and got

PHP Code:

Unknown column 'body' in 'where clause' 


I then put in "you" instead of "body" and I got

PHP Code:

Can't find FULLTEXT index matching the column list 


EDIT: Also to answer Rellizate, I have more code, including code that connects me to my database.

[aonic]

you need to create FULLTEXT type indexes on the columns you are trying to search

[TomorrowHosting]

How do I do this? I have never used FULLTEXT before...

[aonic]

check out the mysql manual
http://dev.mysql.com/doc/refman/5.0/...xt-search.html

A FULLTEXT index definition can be given in the CREATE TABLE statement when a table is created, or added later using ALTER TABLE or CREATE INDEX.

[TomorrowHosting]

Ok, thanks a ton for your help!