Jump to content


Photo

Trouble searching mysql table (fulltext)


  • Please log in to reply
7 replies to this topic

#1 nezbie

nezbie
  • Members
  • PipPipPip
  • Advanced Member
  • 31 posts
  • LocationFINLAND

Posted 02 March 2006 - 12:16 PM

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]

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

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

At first my script didn't find any matches when just passing the phrase as ä matching to ä - 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?
:: Be patient with me, I\'m new to PHP and MySQL :: :)

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 02 March 2006 - 03:26 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 nezbie

nezbie
  • Members
  • PipPipPip
  • Advanced Member
  • 31 posts
  • LocationFINLAND

Posted 02 March 2006 - 08:55 PM

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 "automobil". The search then works fine, since that searchphrase does not contain any special characters.

Allthough, when I input a phrase like: über, which stands for über - it searches all right, but returns every single record of data in the database although this word über 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.
:: Be patient with me, I\'m new to PHP and MySQL :: :)

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 02 March 2006 - 10:00 PM

Sounds like this is a charset/collation issue -- could you post the SHOW CREATE TABLE output for the table in question?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 nezbie

nezbie
  • Members
  • PipPipPip
  • Advanced Member
  • 31 posts
  • LocationFINLAND

Posted 02 March 2006 - 10:02 PM

[!--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]
:: Be patient with me, I\'m new to PHP and MySQL :: :)

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 03 March 2006 - 01:33 AM

[!--quoteo(post=351100:date=Mar 2 2006, 03:55 PM:name=nezbie)--][div class=\'quotetop\']QUOTE(nezbie @ Mar 2 2006, 03:55 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Allthough, when I input a phrase like: über, which stands for über - it searches all right, but returns every single record of data in the database although this word über 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.

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 nezbie

nezbie
  • Members
  • PipPipPip
  • Advanced Member
  • 31 posts
  • LocationFINLAND

Posted 03 March 2006 - 10:07 AM

[!--quoteo(post=351199:date=Mar 2 2006, 08:33 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Mar 2 2006, 08:33 PM) View Post[/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]

// 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


That's my search code. I've tried to keep it as simple as it gets..
:: Be patient with me, I\'m new to PHP and MySQL :: :)

#8 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 03 March 2006 - 07:22 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users