Trouble searching mysql table (fulltext)

[nezbie]

I've tried to find a solution this for some time now, but can't seem to get it working right. I have a php search for my pages to find from news that the page helds within. I use fckEditor to save the news text to the database. Ok, that is irrelevant information, but I think it is causing the problem (in a way).

Well, let's cut the..

My query goes as follows:

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]SELECT n.news_id, n.categories_id, nv.news_header, nv.news_content FROM news AS n LEFT JOIN news_values AS nv ON n.news_id = nv.news_id WHERE MATCH(nv.news_header, nv.news_content) AGAINST("%' . addslashes($_GET['phrase']) . '%") AND nv.languages_id = "' . $_SESSION['languages_id'] . '"'[/quote]

[b]$_GET['phrase'] = my search string.[/b]

OK, this works fine until I have to use special, or scandinavian characters, like [b]ä, ö, ü[/b] etc.. Those characters are stored as using [b]umlauts[/b] like [b]ä[/b] to the database.

At first my script didn't find any matches when just passing the phrase as ä matching to [b]ä[/b] - now the search phrase is changed to umlauts -type - and now it displays every single news as matched result in the database..

I'm losing my nerve with this, anyone have an idea how to work around with this problem?

[fenway]

I'm not sure I understand what you mean -- what results are you getting with which inputs? BTW, you can drop the "%" signs -- it doesn't matter for a FULLTEXT index, AFAIK.

[nezbie]

OK sorry, now reading my question a few hours later I realize it is not too well formed.

I dropped the %:s now.

So, I'll try to explain my issue a better way. Let's say I search for the word "[b]automobil[/b]". The search then works fine, since that searchphrase does not contain any special characters.

Allthough, when I input a phrase like: [b]über[/b], which stands for [b]über[/b] - it searches all right, but returns every single record of data in the database although this word [b]über[/b] isn't in any.

So because every record which contains special characters is stored with some kind of encoding-safe method it - and when searching these words with those special characters in them - messes up my search query! :( I'd really need to get this fixed in one way or another, because my native language contains these, and similar characters in a ton of words.

[fenway]

Sounds like this is a charset/collation issue -- could you post the SHOW CREATE TABLE output for the table in question?

[nezbie]

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]CREATE TABLE `news_values` (
`news_values_id` int(11) NOT NULL auto_increment,
`news_id` int(10) NOT NULL default '0',
`news_header` varchar(255) NOT NULL default '',
`news_content` text NOT NULL,
`news_active` tinyint(1) NOT NULL default '1',
`news_edited` datetime NOT NULL default '0000-00-00 00:00:00',
`news_edited_id` int(11) NOT NULL default '1',
`languages_id` tinyint(3) NOT NULL default '0',
PRIMARY KEY (`news_values_id`),
FULLTEXT KEY `news_fulltext` (`news_header`,`news_content`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=32 ; [/quote]

[fenway]

[!--quoteo(post=351100:date=Mar 2 2006, 03:55 PM:name=nezbie)--][div class=\'quotetop\']QUOTE(nezbie @ Mar 2 2006, 03:55 PM) [snapback]351100[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Allthough, when I input a phrase like: [b]über[/b], which stands for [b]über[/b] - it searches all right, but returns every single record of data in the database although this word [b]über[/b] isn't in any.
[/quote]
This makes no sense to me -- and obviously is the problem. With latin1, there shouldn't be any issues -- I dont' see how it can return _all_ the records. Could you post some examples? I'm stumped.

[nezbie]

[!--quoteo(post=351199:date=Mar 2 2006, 08:33 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Mar 2 2006, 08:33 PM) [snapback]351199[/snapback][/div][div class=\'quotemain\'][!--quotec--]
This makes no sense to me -- and obviously is the problem. With latin1, there shouldn't be any issues -- I dont' see how it can return _all_ the records. Could you post some examples? I'm stumped.
[/quote]

[code]// searchphrase is given?
if($_GET['phrase']) {

  // change special characters to umlauts
  $toreplace = array(special characters); // these work, don't worry
  $replacewith = array(umlauts..); // these work, don't worry

  // change    
  $searchFor = htmlentities(str_replace($toreplace, $replacewith, $_GET['phrase']), ENT_QUOTES);

  // search query
  $searchQuery = 'SELECT n.news_id, n.categories_id, nv.news_header, nv.news_content FROM news AS n LEFT JOIN news_values AS nv ON n.news_id = nv.news_id WHERE MATCH(nv.news_header, nv.news_content) AGAINST("' . addslashes($searchFor) . '") AND nv.languages_id = "' . $_SESSION['languages_id'] . '"';
  $searchResult = executeQuery($searchQuery, $conn);

if(mysql_num_rows($searchResult) > 0) {

  // loop through search results
  $nr = 1;

  echo '
  <table>';

  while($searchRow = mysql_fetch_array($searchResult)) {

    $url = '<a href="index.php?pageID=news&categoriesID=' . $searchRow['categories_id'] . '&newsID=' . $searchRow['news_id'] . '">' . $searchRow['news_header'] . '</a>';
    
    // show result in a row
    echo '
    <tr>
      <td style="width: 18px; padding: 8px 0px 8px 0px;"><img src="' . dir_ws_images . 'arrow.gif"></td>
      <td class="searchSmallHeader">' . $url . '</td>
      <td style="width: 30px; text-align: right;">' . $nr . '.<td>
    </tr>';

  }

echo '
</table>';

} // END if

[/code]

That's my search code. I've tried to keep it as simple as it gets..

[fenway]

I see... well, obviously, the suspicious part of the code is right at the top, where you say "don't worry". I believe you, but my guess is that something is going awry here, and your $searchFor string is not what you think it is.