Jump to content

Archived

This topic is now archived and is closed to further replies.

nezbie

Trouble searching mysql table (fulltext)

Recommended Posts

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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
Sounds like this is a charset/collation issue -- could you post the SHOW CREATE TABLE output for the table in question?

Share this post


Link to post
Share on other sites
[!--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]

Share this post


Link to post
Share on other sites
[!--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.

Share this post


Link to post
Share on other sites
[!--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..

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.