Page 1 of 1

Help with php MySQL search

Posted: Fri Jan 28, 2005 9:35 am
by coily
Hi

I'm trying to build a a search facility for my site. I have found some good tutorials and developed a simple keyword search.

The problem is it only works for single keywords(not so good!).

Can anyone explain what I need to change to enable searching of several keywords with the following code:

Code: Select all

if( $_POSTї'keyword'] )
{
   /* Connect to the database: */
   mysql_pconnect("localhost","username","password")
       or die("ERROR: Could not connect to database!");
   mysql_select_db("search");

   /* Get timestamp before executing the query: */
   $start_time = getmicrotime();

   /* Set $keyword and $results, and use addslashes() to
    *  minimize the risk of executing unwanted SQL commands: */
   $keyword = addslashes( $_POSTї'keyword'] );
   $results = addslashes( $_POSTї'results'] );

   /* Execute the query that performs the actual search in the DB:*/
  $result = mysql_query(" SELECT p.page_url AS url,
                           COUNT(*) AS occurrences 
                           FROM page p, word w, occurrence o
                           WHERE p.page_id = o.page_id AND
                           w.word_id = o.word_id AND
                           w.word_word = "$keyword"
                           GROUP BY p.page_id
                           ORDER BY occurrences DESC
                           LIMIT $results" );




			  

   /* Get timestamp when the query is finished: */
   $end_time = getmicrotime();

   /* Present the search-results: */
   print "<h2>Search results for '".$_POST&#1111;'keyword&#1111;1]']."':</h2>\n";
   for( $i = 1; $row = mysql_fetch_array($result); $i++ )
   &#123;
      print "$i. <a href='".$row&#1111;'url']."'>".$row&#1111;'url']."</a>\n";
      print "(occurrences: ".$row&#1111;'occurrences'].")<br><br>\n";
   &#125;

   /* Present how long it took the execute the query: */
   print "query executed in ".(substr($end_time-$start_time,0,5))." seconds.";
&#125;
else
&#123;
   /* If no keyword is defined, present the search page instead: */
   print "<form method='post'> Keyword: 
          <input type='text' size='20' name='keyword'>\n";
   print "Results: <select name='results'><option value='5'>5</option>\n";
   print "<option value='10'>10</option><option value='15'>15</option>\n";
   print "<option value='20'>20</option></select>\n";

   print "<input type='submit' value='Search'></form>\n";
&#125;

print "</body></html>\n";
/* Simple function for retrieving the current timestamp in microseconds: */
function getmicrotime()
&#123;
   list($usec, $sec) = explode(" ",microtime());
   return ((float)$usec + (float)$sec);
&#125;

?>
I think it must be simple but i've spent two days reading up on SQL and every time I try to hack the code to enable multiple keywords I just turn it in to a BIG mess of errors.

Any help would be great

Thanks

Posted: Fri Jan 28, 2005 9:48 am
by feyd
w.word_word IN( <word list here> )

check the mysql docs for details:

http://dev.mysql.com/doc/mysql/en

Help with php MySQL

Posted: Fri Jan 28, 2005 11:10 am
by coily
Hi Feyd

Thanks for the reply. Sorry to be so needy but I don't really understand.

I take it that I should change the line:

Code: Select all

w.word_word = "$keyword"
This does make sense to me but change to what is my problem.

I also think I need a way of spliting the words entered in the FORM in order to query each of them.

Once again sorry for my ignorance. I am lost

Thanks

Posted: Fri Jan 28, 2005 11:28 am
by feyd