Help with PHP/MySQL Search

[olver]

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:

 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['keyword[1]']."':</h2>\n"; 
   for( $i = 1; $row = mysql_fetch_array($result); $i++ ) 
   { 
      print "$i. <a href='".$row['url']."'>".$row['url']."</a>\n"; 
      print "(occurrences: ".$row['occurrences'].")<br><br>\n"; 
   } 

   /* Present how long it took the execute the query: */ 
   print "query executed in ".(substr($end_time-$start_time,0,5))." seconds."; 
} 
else 
{ 
   /* 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"; 
} 

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

?> 

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

[stereofrog]

So far I understand, you use occurrence(word_id, page_id) as an index table (populated elsewhere), right? I've used something like this in my own projects, although the same may be much simpler with mysql fulltext search.

For the sake of simplicity and speed I prefer to split the whole process into steps. First, let's translate words to their ids:

$words = array(list of keywords to search);
$word_list = implode(" ' , ' ", array_map('addslashes', $words));
$rc = mysql_query("SELECT word_id FROM word WHERE word_word IN ('$word_list')");
while($w = mysql_fetch_row($rc))
   $word_ids[] = $w[0]; 

Now we have, say, (1, 10, 20) instead of ('foo', 'bar', 'baz'). Obviously, if $word_ids is empty we can interrupt the search right now and redirect user to "nothing found" page.

Now let's find pages with keywords. The sql statement will look like

$ids = implode(",", $word_ids);
$rc = mysql_query("
      SELECT o.page_id, COUNT(o.page_id) AS relevance, p.url
           FROM occurrence o, page p
           WHERE word_id IN ($ids) AND o.page_id = p.page_id
           GROUP BY o.page_id
           ORDER BY relevance DESC"); 
while($w = mysql_fetch_array($rc))
   print pages, ordered by relevance 

"Relevance" is quite primitive here: document that contains N keywords has a relevance N. BTW, if you're performing "all words" search, you can simply restrict it by adding "HAVING relevance=$n" to sql above, where $n = count($word_ids).

HTH...

PS All code untested! Handle with care.

[toggg]

Always

mysql_query(...) or die('...'.mysql_error().'....'); 

I can understand what you want, not sure any SQL does:

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

Give more info on tables
that COUNT(*), is it developed over all occurences X occurences X ...
Should that go on SQL forum?

[toggg]

Hi stereofrog,
We just collided but questions shoud be the same.
Just:

$word_list = implode(" ' , ' ", array_map('addslashes', $words)); 

I would say "' and not " ', '" and not ' ",
do we need trailing spaces around words ?

[stereofrog]

Well, normally this shoud look like

$sql = $db->prepare("SELECT word_id FROM word WHERE word_word IN (?)");
$res = $db->execute($sql, $word_list);
// etc 

I never write this "addslahes mysql_query or die" stuff in my projects, that's why I'm not so sure about syntax...

[toggg]

He really,
" IN (?) " meaninq all choices ?
Is it general SQL ?

[stereofrog]

? is an "sql placeholder", commonly used in sql libraries.

[toggg]

Ooops, I missed it was a 'prepare'
Then should $word_list be "'a','b','c'" not "' a ',' b ',' c '" ? Empty ?
That ? in my last sentence is not a "sql placeholder"
à+

[olver]

Hi Guys,

I just popped out for something to eat and seamed to have bad timing!!

The tables for this project were created as follows:

CREATE TABLE page (
   page_id int(10) unsigned NOT NULL auto_increment,
   page_url varchar(200) NOT NULL default '',
   PRIMARY KEY (page_id)
) TYPE=MyISAM;

CREATE TABLE word (
   word_id int(10) unsigned NOT NULL auto_increment,
   word_word varchar(50) NOT NULL default '',
   PRIMARY KEY (word_id)
) TYPE=MyISAM;

CREATE TABLE occurrence (
   occurrence_id int(10) unsigned NOT NULL auto_increment,
   word_id int(10) unsigned NOT NULL default '0',
   page_id int(10) unsigned NOT NULL default '0',
   PRIMARY KEY (occurrence_id)
) TYPE=MyISAM;

I have a populate file used to fill the tables from a given URL.

The search function is as my first post.

As I explained before the scrip works but only for one keyword, i need more really.

I really appreciate the help.

Please be aware that I am not to hot with MySQL so simple terms are cool. Aslo I appologise if I should have posted this in MySQL forum I was unsure.

Thanks

[toggg]

$result = mysql_query(" SELECT p.page_url AS url,
                           COUNT(o.occurrence_id) AS occurrences  // forgive *
                           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 IN (\"$word_list\") // as we disscussed as you got your foof ( ' or " )?
                           GROUP BY p.page_id
                           ORDER BY occurrences DESC
                           LIMIT $results" ); 

[olver]

Okay I made the changes from the previos post and understand that I need to create a word_list array from the keywords entered in the FORM. But I don't understand how to do this.

Now when I run the search from the web page I get;

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in

this is the line 67 in code:

 for( $i = 1; $row = mysql_fetch_array($result); $i++ )

I think I need to keep reading php and MySQL manuals!!

Thanks for working with me on this its driving me mad! So close yet Sooo far.

[toggg]

Take your whole;
$result = mysql_query(" SELECT p.page_url AS url,
COUNT(o.occurrence_id) AS occurrences // forgive *
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 IN (\"$word_list\") // as we disscussed as you got your foof ( ' or " )?
GROUP BY p.page_id
ORDER BY occurrences DESC
LIMIT $results" );
and copy it just before replacing
$result = mysql_query(...
by echo(....
So we can actually see what this SQL should eat !
Thus die on mysql_error()
à+

[olver]

I did as you said and I get error as in my last post, is the problem is with fetch _array? I don't know.

anyway its 2am here and I need some sleep i'll try some more in the morning.

Thanks for all your help.

[toggg]

Bye, you're right better dreaming