problems making a site search engine

[cashton2k]

Hi, this problem has got me totally stuck - i'm trying to make search results appear but my either sql or php doesnt like something

i'm getting the error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Program Files\Apache Group\Apache2\htdocs\homepage\searchresults.php on line 31

my code is

  while ($row = mysql_fetch_array($result)) {
    $rowclass = ($rowclass == "row1"?"row2":"row1");
    echo "<tr class=\"$rowclass\">\n<td>\n";
    $topicid=($row['topic_id']==0?$row['id']:$row['topic_id']);
    echo "<p class=\"searchSubject\">\n<a href=\"viewtopic.php?t=" .
         $topicid . "#post" . $row['id'] . "\">" .
         $row['subject'] . "</a>\n";
    echo "</p>\n";
    echo "<p class=\"searchBody\">\n";
    echo htmlspecialchars(trimBody($row['body']));
    if ($access_lvl > 2) {
         echo "<p>SQL: $sql</p>";
         echo "<br><br>relevance: " . $row['score'];
    } 

line 31 is where the while loop starts

cheers for any help

[chazzy]

ok, so what happens before it?

the issue is that "$result" is not a valid resource, meaning your query failed before it.

[cashton2k]

heres the whole page

<?php
require_once 'conn.obj.php';
require_once 'header.php';

$result = NULL;

if (isset($_GET['keywords'])) {
  $sql = "SELECT *, MATCH (subject,body) " .
         "AGAINST ('" . $_GET['searchwords'] . "') AS score " .
         "FROM objects " .
         "WHERE MATCH (description,keywords) " .
         "AGAINST ('" . $_GET['searchwords'] . "') " .
         "ORDER BY score DESC";

  $result = mysql_query($sql, $conn)
    or die('Could not perform search; ' . mysql_error());
}

echo "<table class=\"forumtable\" width=\"100%\" " .
     "cellspacing=\"0\">\n";
echo "<tr><th class=\"searchHeader\">Search Results</th></tr>\n";

if ($result and !mysql_num_rows($result)) {
  echo "<tr class=\"row1\"><td>No Learning Objects found that match the ";
  echo "search term(s) '<strong>" . $_GET['searchwords'] . "</strong>'";
  if ($access_lvl > 2) echo "<p>SQL: $sql</p>";
  echo "</td></tr>\n";
} else {
  $rowclass = "";
  while ($row = mysql_fetch_array($result)) {
    $rowclass = ($rowclass == "row1"?"row2":"row1");
    echo "<tr class=\"$rowclass\">\n<td>\n";
    $topicid=($row['topic_id']==0?$row['id']:$row['topic_id']);
    echo "<p class=\"searchSubject\">\n<a href=\"viewtopic.php?t=" .
         $topicid . "#post" . $row['id'] . "\">" .
         $row['subject'] . "</a>\n";
    echo "</p>\n";
    echo "<p class=\"searchBody\">\n";
    echo htmlspecialchars(trimBody($row['body']));
    if ($access_lvl > 2) {
         echo "<p>SQL: $sql</p>";
         echo "<br><br>relevance: " . $row['score'];
    }
    echo "\n</p>\n";
    echo "</td>\n</tr>\n\n";
  }
}
echo "</table>";

require_once 'footer.php';
?>

[chazzy]

hmm you have some logic flaws in your code - if keywords is not set, you are still running everything. try adding near the top

if(!isset($_GET['keywords'])){
die("invalid input");
} 

[cashton2k]

cheers looks like its sorted my code out but now i have a problem with my sql database

now i get - Could not perform search; The used table type doesn't support FULLTEXT indexes

[chazzy]

fulltext only works on MyISAM tables. Are you using MyISAM?

[cashton2k]

not sure im using mySQL 5 and the tables are created using the query browser gui

[cashton2k]

i just changed the table type to myISAM

then i just got - Could not perform search; Can't find FULLTEXT index matching the column list

then a mate told me my sql query was wrong so now i've changed it too

$sql = "SELECT * FROM objects WHERE Object_Name OR Description OR Keywords ORDER BY ObjectID DESC" ;

which doesnt give any errors but it always returns no results

any ideas?

[chazzy]

you have to create a fulltext key on the columns in question if you want to use full text search (which is what you were doing this entire post)

no reason to create a bunch of or's that don't compare to anything...

http://dev.mysql.com/doc/refman/5.0/...ate-index.html
you can use that to create the full text index that you want.

[cashton2k]

cheers thanks for your help, got it going!