[SOLVED] Full text search

[twsowerby]

Hi I'm really struggling with this full text search stuff.

 

I'm trying to get this search form:

 

echo "<form action='scripts/results.php?query=1' method='post'>";
echo "<input type='text' name='keyword'>";
echo "<input type='submit' value='Search!'>";
echo "</form>";

 

To pass the keyword to a script that will then query the database and return results. So far I've got this:

 

<?php


	$keyword=$_POST['keyword'];

        $sql = "select *, match(title, post) against('$keyword') as score from forum_posts where match(title, post) 
	against('$keyword') order by score desc";
        $rest = MySQL_query($sql);

echo "<table>";
echo "<tr><td>SCORE</td><td>TITLE</td><td>ID#</td></tr>";

        while($row = MySQL_fetch_array($rest)) {
            echo "<tr><td>{$sql2['score']}</td>";
            echo "<td>{$sql2['title']}</td>";
            echo "<td>{$sql2['id']}</td></tr>";
        }
        echo "</table>";

?> 

 

I'm still pretty new to php so I would be grateful if you could fully explain what I am meant to do for this to work.

 

Thanks,

 

Tom

 

 

[twsowerby]

Can nobody help me out with this? I will post more information if it is needed!

 

Thanks,

 

Tom

[The Little Guy]

Do you have your database feilds set to "full text"?

[The Little Guy]

Then, change this line:

$rest = MySQL_query($sql);

 

to this:

$rest = MySQL_query($sql)or die(mysql_error());

 

It will be checking for errors in your SQL statement

[twsowerby]

I had already set my tables to full search and my SQL appears to be fine, not throwing back any errors. It wont return search results though, and I know the terms I'm looking for exist within the table.

 

Any ideas?

 

Thanks,

 

Tom

[The Little Guy]

Try This:

 

<?php
$sql = "SELECT SQL_CALC_FOUND_ROWS * FROM files WHERE MATCH(title, post) AGAINST ('$keyword' IN BOOLEAN MODE) ORDER BY score DESC";

$sql = mysql_query($query)or die(mysql_error()); 
$result_count = mysql_query("SELECT FOUND_ROWS()")or die(mysql_error());
$total = mysql_fetch_array($result_count);
$totalrows = $total[0];
?>

[twsowerby]

Hi,

 

That just returns "query is empty", sorry to sound stupid but could you please tell me exactly how to implement that?

 

Tom

[The Little Guy]

Give this a whirl:

 

<?php
$keyword=$_POST['keyword'];  // The keyword into a variable

// Make a safe query string
$query = sprintf("SELECT SQL_CALC_FOUND_ROWS * FROM files WHERE MATCH(title, post) AGAINST ('%s' IN BOOLEAN MODE) LIMIT 20", mysql_real_escape_string($keyword));

// Query the string
$sql = mysql_query($query)or die(mysql_error()); 
// Query the total number of rows if LIMIT was not set
$result_count = mysql_query("SELECT FOUND_ROWS()")or die(mysql_error());
// Return total number of rows into an array
$total = mysql_fetch_array($result_count);
// set the first value of the array (total number of rows) into a variable
$totalrows = $total[0];

// Now loop through your query
echo 'Found <strong>'.$totalrows.'</strong> Results';
while($row = mysql_fetch_array($sql)){
     echo '<p>'.$row['title'].'</p>';
}
?>

[twsowerby]

You're a genius thank you very much, that works perfectly!

 

Tom

 

*SOLVED*