Jump to content

Trouble searching mysql table (fulltext)


nezbie

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?
Link to comment
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.
Link to comment
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]
Link to comment
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.
Link to comment
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..
Link to comment
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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.