Help with php MySQL search

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
coily
Forum Newbie
Posts: 2
Joined: Fri Jan 28, 2005 9:23 am

Help with php MySQL search

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

w.word_word IN( <word list here> )

check the mysql docs for details:

http://dev.mysql.com/doc/mysql/en
coily
Forum Newbie
Posts: 2
Joined: Fri Jan 28, 2005 9:23 am

Help with php MySQL

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Post Reply